Table of Contents
In Power BI, two columns can be concatenated by using the “Append Queries” function. This allows the user to combine two tables or queries, creating a new table that contains all of the columns from both original tables. The user can then select which columns they want to keep and arrange them in the desired order. This process effectively joins the two columns together, allowing for easier analysis and visualization in Power BI.
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: