Table of Contents
Concatenation in Power BI allows you to combine two or more columns into a single column. This process is useful for creating a new column with combined data from multiple columns, such as combining first and last names into a full name column. To concatenate columns in Power BI, you can use the ‘&’ operator or the ‘CONCATENATE’ function. This allows you to easily manipulate and organize your data within your Power BI reports, making it more efficient and user-friendly. By following the proper steps, you can easily concatenate columns and create a new, consolidated column in your Power BI report.
Concatenate Two Columns in Power BI (With Examples)
You can use the following formulas in DAX to concatenate two columns together:
Formula 1: Concatenate Two Columns with No Separator
New Column = CONCATENATE('my_data'[Column 1], 'my_data'[Column 2])
This particular formula concatenates the strings in Column 1 and Column 2 together with no separator between them.
Formula 2: Concatenate Two Columns with Separator
New Column = CONCATENATE('my_data'[Column 1] & " ", 'my_data'[Column 2])
This particular formula uses the & symbol to concatenate the strings in Column 1 and Column 2 together with a space between them.
The following examples show how to use each formula in practice with the following table named my_data in Power BI:
Example 1: Concatenate Two Columns with No Separator
To concatenate the strings in the First and Last columns together with no separator, click the Table tools tab, then click the New column icon, then type the following formula into the formula bar:
First and Last = CONCATENATE('my_data'[First], 'my_data'[Last])
This will create a new column named First and Last that concatenates the strings in the First and Last columns together with no separator:
Example 2: Concatenate Two Columns with Separator
To concatenate the strings in the First and Last columns together with a specific separator, click the Table tools tab, then click the New column icon, then type the following formula into the formula bar:
First and Last = CONCATENATE('my_data'[First] & " ", 'my_data'[Last])
Note that we chose to use a space as a separator, but you could use any separator you’d like by specifying it after the & symbol.
For example, you could use the following syntax to use a dash as a separator:
First and Last = CONCATENATE('my_data'[First] & "-", 'my_data'[Last])
This will concatenate the values from the First and Last columns together with a dash in between them:
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: