In this post, we will learn how to perform Power BI concatenate two columns with space using DAX and M Query to combine multiple values in one column in Power BI.
Power BI concatenate two columns
In some cases, you may need to concatenate two columns and combine the result in one column. so, you can easily use DAX CONCATENATE to join two text strings into one text string.
Full Name = CONCATENATE(Text1,Text2)
Unfortunately, the CONCATENATE DAX function doesn’t allow me to concatenate two columns with space!
In this case, you can use ampersand & in a calculated column or a custom column to combine two or more columns with space in Power BI and get the desired value as below.
In this post, we will provide two methods to perform Power BI concatenate columns using DAX and M.
You might also need to read Power BI: DAX Vs M Language
Power BI concatenate two columns using DAX
To concatenate two columns using DAX in Power BI, you have to do the following:
- From the left side, Click on Data View icon.
- In the Data view, Make sure the current table is the table that you need to combine multiple columns.
- At Table Tools tab, click on “New Columns” to create a new caclulated column in your table.
- Write the below DAX formula to concatenate two columns with space in Power BI
Full Name = Employee[First Name] & " " & Employee[Last Name]
- Great, the new calculated column is now created and show the combined value in a new column as shown below.
Power BI concatenate two columns using Power Query
To concatenate two columns using Power Query in Power BI, you have to usw M language as the following:
- In “Home” tab, click on “Transform Data” to open Power Query Editor.
- At the left side, and specifically at Queries section, make sure that you have select the required table.
- At the “Add column” tab, click on “Custom Column” to create a new column using M Language.
- The custom column editor should be shown now to write your formula.
- At “New column name“, provide your column name.
- At “Custom column formula” textbox, write the below simple formula to combine two columns with space in Power BI.
[First Name] & " " & [Last Name]
- Great, the new custom column has been created with the desired result.
- Close and apply your changes to can use the new column in your visuals and model.
In conclusion, we have learned how to perform Power BI concatenate two columns with space, and you can also use the same way to concatenate multiple columns and combine the value in one column.