Concatenate 2 Columns in Power BI with DAX

There are two methods to CONCATENATE columns in Power BI with DAX. You can use the CONCATENATE function, or you can use the & operator. CONCATENATE works well when combining text 2 columns, but becomes cumbersome when combining more than two columns, or when you want to concatenate with a space, comma or other special character.

The CONCATENATE DAX Function

To quickly concatenate two text fields in Power BI, you can use the CONCATENATE function. Create a New Column, and use the DAX formula: CONCATENATE([Column1],[Column2]. Where the names of the columns are surrounded by square brackets and separated by a comma.

Here is an example of combining a First Name and Last Name Column.

The Power BI Formula: Full Name = CONCATENATE([First Name],[Last Name])
Full Name = CONCATENATE([First Name],[Last Name])

The results of this formula would be: JohnSmith

The problem with this example is that there is no delimiter or space in-between the first and last name which would create a single string that would be difficult for report viewers to read.

To address this problem using only the CONCATENATE function, you would need to nest two CONCATENATE formulas together, as the CONCATENATE function only accepts 2 field names as arguments.

Full Name = CONCATENATE([First Name],CONCATENATE(" ",[LAST NAME])

The results of this formula would be: John Smith

This setup gets the job done but is difficult to read and impractical when combining text from many different fields.

Because of this limitation we recommend Power BI report developers use the & operator.

The & DAX Operator

The fastest way to concatenate 2 columns or more in Power BI is the use of the & operator when creating a New Column. The formula is: [Column1]&[Column2] to combine two columns. Power BI will detect that the fields are text/string data types and automatically concatenate them when an & operator is applied.

The following example concatenates 2 columns.

Full Name = [First Name]&[Last Name]

Use double quotes around a space to concatenate two columns with a space in-between.

Full Name = [First Name]&" "&[Last Name]

You can use the same technique to concatenate any text or delimiter. For example, two columns with a comma.

Full Name = [Last Name]&","&[First Name]

There are no limitations on the number of columns you can concatenate with this method. It’s also possible to concatenate multiple characters together, such as a hyphen and spaces.

Full Name = [First Name]&" - "&[Last Name]

The double quotes act as an escape that tells Power BI to interpret the characters in the DAX formula as fixed text.

Creating a New Column vs a New Measure in Power BI

When you concatenate values in Power BI DAX, use the “New Column” button under Table Tools instead of creating a New Measure. Measures are typically used for aggregations, such as SUM or AVERAGE. New Columns are evaluated on a row-by row basis which is applicable for concatenation, since you cannot aggregate text like you can numeric values.

Creating a New Column from the Table Tools menu in Power BI

Only String/Text values can be concatenated in Power BI DAX. Meaning that if you want to concatenate text with numbers you will need to convert the number to a String/Text data type.

Converting Numeric Fields to Text with DAX

Use the DAX CONVERT function to temporarily change the data type of a numeric field when writing a DAX formula. Conversions in DAX are preferred when you want to set a data type for a single formula. Otherwise you can more permanently change the data type using Power Query or adjusting the data type from the Column Tools section of the Power BI ribbon.

The Syntax to Convert a data type is” CONVERT([Column1],DataType).

Here is an example of using CONVERT to assess a numeric field as a String/Text field.

The Power BI Formula: Full Name = [First Name]&" "&[Middle Name]&" "&CONVERT([Sales],STRING) with an example of the concatenation text presented in Power BI
Full Name = [First Name]&" "&[Middle Name]&" "&CONVERT([Sales],STRING)

The data type conversion occurs for only this specific formula, leaving it as a numeric field for other calculations and measures. The CONVERT DAX function supports the following data type conversions:

  • BOOLEAN
  • CURRENCY
  • DATETIME
  • DOUBLE
  • INTERGER
  • STRING

We recommend doing data type conversions in Power Query whenever the data type will be used the same way across the entire report. For example, CSV files will import into Power Query entirely as text. You can adjust columns of numbers to numeric data types to allow measures to aggregate and calculate appropriately. Then convert them to strings if needed on a formula-by-formula basis.

Scroll to Top