In this article we will show you how you can concatenate columns in your Power BI tables.
We also go over how to concatenate multiple columns using DAX in power BI and how to include spaces.
If you’re looking to combine text values across different tables or customize the order of your concatenated strings, you can use CONCATENATEX function in Power BI. We cover this in a separate article.
Concatenate two columns using CONCATENATE DAX function
You can concatenate two columns easily in Power BI using the CONCATENATE DAX function.
The only fields you need are the two columns you want to concatenate.
Here is an example table with some product data:
If we want to concatenate the Product column and the Category column, we can create a new column in Power BI and use the following formula:
ProductCategory = CONCATENATE('Product Costs'[Product],'Product Costs'[Category])This produces the following result:
The CONCATENATE function will only allow you to use two columns, so if you need to concatenate three or more, you can either nest the formula or use the next method.
Concatenate multiple columns using &”
This method does not use a DAX function, but instead uses the & and ” symbols to concatenate words and columns in Power BI.
You can use these to concatenate multiple columns easily and have three or more strings.
If we use the same example table of data from the above section, the following formula can be used to achieve the same result:
ProductCategory = 'Product Costs'[Product] & 'Product Costs'[Category]Rather than using the CONCATENATE function, we use an & symbol to stitch the two columns together.
You can keep adding these to your formula to concatenate more columns.
It also allows you to include spaces in your concatenation and your own text.
Text must be wrapped in “double quotations” and stiched with the & as before.
Here is an example of a formula we could write in Power BI that includes multiple items and a space:
ProductCategory = "Category: " & 'Product Costs'[Product] & ", " & 'Product Costs'[Category]This produces the following result in our example table:
