How to Concatenate in Power BI

Combining text from different columns is a fundamental task in data analysis, and Power BI offers several straightforward ways to do it. Whether you need to create a full name from separate first and last name columns, build a unique ID, or craft custom labels for your charts, this guide will walk you through the process step-by-step. We’ll cover the most common methods using both DAX and Power Query so you can choose the best approach for your specific report.

What is Concatenation and Why Is It Useful?

At its core, concatenation is simply the process of joining text strings together, end-to-end. Think of it like linking chains together to form a longer one. In Power BI, this is incredibly useful for several practical reasons:

• Creating Full Names: The most common example is combining a "FirstName" column and a "LastName" column to create a "FullName" column for easier readability in tables and slicers.
• Generating Unique IDs: You can combine columns like an Order ID and a Product ID (e.g., "ORD1001-PROD54") to create a unique key that helps identify specific records.
• Building Descriptive Labels: For charts and tables, you might concatenate a geographic location with a time frame to create dynamic labels like "USA - Q2 2024."
• Improving Data Sorting: Sometimes, concatenating a year column and a month column (e.g., "2024-01") creates a text field that sorts chronologically and looks cleaner than default date hierarchies.

You can achieve this in Power BI in two main environments: with DAX for dynamic calculations in your data model, or in the Power Query Editor for pre-processing and cleaning your data. Let's break down each method.

Method 1: Concatenating with DAX

Using Data Analysis Expressions (DAX) is the go-to method for creating concatenations as 'calculated columns' or 'measures' directly within your data model. This approach is dynamic, meaning the result will reflect any changes as your model updates or users interact with slicers (in the case of measures).

Using the Ampersand (&) Operator

The simplest and most flexible way to concatenate in DAX is by using the ampersand (&) operator. It's intuitive, easy to read, and allows you to join as many columns or text strings as you need.

Let’s say we have a Customers table with FirstName and LastName columns and want to create a FullName column.

Step-by-Step Instructions:

1. Navigate to the Data view in Power BI Desktop (the second icon on the left-hand pane that looks like a grid).
2. Select the table you want to add the column to from the Fields pane on the right.
3. From the Table tools ribbon at the top, click on New column. A formula bar will appear.
4. Enter the following DAX formula:
5. Press Enter. A new "FullName" column will appear in your table.

In this formula, Customers[FirstName] and Customers[LastName] refer to the columns we want to join. The " " in the middle is a literal text string containing a single space. Without this, the names would be mashed together (e.g., "JohnSmith"). You can put any characters or text inside the double quotes to act as a separator.

Using the CONCATENATE Function

DAX also includes a formal function for this task: CONCATENATE. While it does the same job as the ampersand, it has a significant limitation: it can only accept two arguments at a time.

Syntax:

CONCATENATE(<text1>, <text2>)

To create the same "FullName" column, the formula would look like this:

FullName = CONCATENATE(Customers[FirstName], " ")

To then add the last name, you would have to nest the function, which can get messy:

FullName = CONCATENATE(CONCATENATE(Customers[FirstName], " "), Customers[LastName])

Because of this, most Power BI developers prefer the readability and flexibility of the ampersand (&) operator for most simple concatenations.

Using the CONCATENATEX Function

For more advanced scenarios, especially in measures, CONCATENATEX is an extremely powerful function. It iterates over a table and concatenates the results of an expression for each row, optionally adding a delimiter.

Imagine you want to create a card visual that lists all the product colors sold in a particular category, separated by commas.

Syntax:

CONCATENATEX(<table>, <expression>, [<delimiter>])

You could create a measure like this:

Product Colors = CONCATENATEX(Products, Products[Color], ", ")

When you use this measure in a report filtered by a specific category, it will dynamically list just the colors for that category (e.g., "Red, Blue, Black"). This isn't possible with a standard calculated column.

Method 2: Concatenating in Power Query

Performing concatenation in the Power Query Editor is ideal when you're cleaning and transforming your data before loading it into your model. Any column you create here becomes a permanent part of your table's structure.

Using the "Merge Columns" Feature (No Code Required)

The easiest way to concatenate in Power Query is by using its built-in user interface, which requires no formula writing at all.

Step-by-Step Instructions:

1. From the Home ribbon, click Transform data to open the Power Query Editor.
2. In the Power Query Editor, select the columns you want to join. Hold down the Ctrl key while clicking to select multiple columns. The order in which you select them is the order they will be merged.
3. To keep your original columns, go to the Add Column tab in the ribbon. To replace your original columns with the new merged column, use the Transform tab. It's generally safer to use "Add Column."
4. Click on Merge Columns.
5. A dialog box will pop up.
6. Click OK. Power Query will generate the M code for you and add the new column to your table.

Using a Custom Column (with Formulas)

For more control, you can write a simple formula in Power Query to create a custom column. The syntax is very similar to DAX.

Step-by-Step Instructions:

1. In the Power Query Editor, go to the Add Column tab and click on Custom Column.
2. In the dialog box, give your new column a name.
3. Type the formula into the Custom column formula box. Use the ampersand (&) just like in DAX:
4. Check for any syntax errors, and then click OK.

Power Query also has a specific function for this called Text.Combine, which works like CONCATENATEX. The equivalent formula would be Text.Combine({[City], [State]}, ", ").

DAX vs. Power Query: Which Should You Use?

Deciding where to perform your concatenation depends on your goal:

Use Power Query for concatenation when:

• It’s a one-time data preparation step.
• The combined value is a permanent, static attribute of the data (like a Full Name or Unique ID).
• You want to improve model performance by performing transformations before the data is loaded.

Use a DAX Calculated Column when:

• The concatenation needs ingredients from two completely different tables (that were created as part of the data model and aren’t in the Power Query).
• You prefer to see the column within the Power BI data model view without going back into the editor.

Use a DAX Measure when:

• You need the result to be calculated dynamically in a visualization, and the result should reflect the user’s selections on different slicers or filters.

Final Thoughts

Concatenating data in Power BI is a common and powerful technique for making your reports more readable and functional. Whether you prefer the simplicity of the DAX ampersand operator, the raw power of CONCATENATEX, or the no-code 'Merge Columns' feature in Power Query, there's a method that fits your needs and skill level.

Manually preparing your data still leaves much to be done when it gets into the BI solution. Often, this requires a deeper understanding of the BI product you decide to use. We built Graphed to simplify this entire process so marketing and sales teams can get answers without needing a data science degree. After connecting your data sources in seconds, you can just ask questions in plain English - like "create a dashboard showing my sales pipeline by rep" - and the platform builds a live, professional dashboard for you automatically, saving you hours of manual reporting work.