How to Concatenate Columns in Power BI

Combining text from different columns into one is a fundamental task when preparing data for analysis, and in Power BI, it’s a skill you’ll use all the time. Whether you're creating a "Full Name" column from separate "First Name" and "Last Name" fields or building a unique ID for your data model, knowing how to concatenate columns is essential. This guide will walk you through the primary ways to achieve this in both Power Query and with DAX, so you can pick the best method for your specific situation.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Bother Concatenating Columns?

Before diving into the "how," it’s helpful to understand the "why." Concatenation isn't just a technical exercise, it serves practical purposes that make your reports more intuitive and powerful. Here are a few common scenarios:

• Creating Full Names: The most classic example. Your data source probably stores "First Name" and "Last Name" separately, but for reports and visualizations, a single "Full Name" column is much cleaner.
• Building Unique IDs: Sometimes a combination of two or more columns creates a unique identifier. For example, concatenating an Order ID with a Product ID (e.g., "1001-P45S") can create a unique key for an order line item.
• Crafting Descriptive Labels: For charts and tables, you might want more descriptive labels than your raw data provides. Combining a product name with its category (e.g., "Espresso Machine - Kitchen Appliances") makes visualizations easier to interpret at a glance.
• Generating Address Blocks: Instead of showing street, city, state, and zip code in four different columns, you can concatenate them into a single, properly formatted address block.

Choosing Your Method: Power Query vs. DAX

In Power BI, you have two primary environments where you can manipulate data: the Power Query Editor and the DAX formula engine in the main Report/Data view. Your first decision is deciding where to perform the concatenation.

Here’s the simplest rule of thumb:

• Use Power Query Editor for data preparation. If you're cleaning, shaping, or transforming your raw data before it gets loaded into your data model, Power Query is the place to be. Concatenating here is a transformation step that permanently alters the table structure for your report. This is generally the best-practice approach for creating new columns.
• Use DAX for calculations on your loaded data. If you need a concatenated column that can react dynamically to filters or uses values from different (but related) tables, DAX is your tool. DAX creates a calculated column that exists within the data model after it has been loaded.

For most simple concatenation needs, like creating a full name, doing it in Power Query is the cleaner, more efficient method.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Using the Power Query Editor

Let's start with the most common and recommended approach. Open the Power Query Editor by clicking "Transform data" on the Home tab of Power BI Desktop.

Technique A: The "Merge Columns" Feature (The Easy Way)

Power Query has a built-in, user-friendly feature for this exact task. It’s perfect for beginners and requires no writing of formulas.

Let’s say you have these two columns:

Here’s how to merge them:

1. Select the columns you want to combine. The order you select them in matters! To select multiple columns, hold down the Ctrl key. Click "First Name," then hold Ctrl and click "Last Name."
2. Navigate to the "Add Column" tab on the Ribbon. (You can also use the "Transform" tab if you want to replace the original columns, but "Add Column" is usually safer).
3. In the "From Text" group, click on "Merge Columns."
4. A dialog box will appear. Here, you define how the columns are merged.
5. Choose a Separator. If you want to put a space between the first and last name, select "Space" from the dropdown. You can also choose from other common separators or use a "Custom" one like a hyphen (-) or a comma and space (, ).
6. Give your new column a name, such as "Full Name."
7. Click OK.

Power Query will instantly create a new "Full Name" column with your neatly combined text.

Technique B: Creating a Custom Column with the Ampersand (&)

If you want a little more flexibility or prefer writing a quick formula, you can use the ampersand (&) operator in a custom column. This is the universal symbol for concatenating text.

1. In the Power Query Editor, go to the "Add Column" tab.
2. Click "Custom Column."
3. The Custom Column editor will open.
4. Name your new column (e.g., "FullName").
5. In the formula box, you'll write an expression to combine your columns. The column names appear in a list on the right, you can double-click them to add them to your formula. To combine "First Name" and "Last Name" with a space, the formula would be:

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

• Notice that the text you want to add — in this case, a space — must be enclosed in double quotes. The ampersand joins each piece together.

Click OK, and you'll get the same result as the Merge Columns feature, simply achieved through a manual formula.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Technique C: Using Text.Combine for More Control

There's one more powerful text function in Power Query: Text.Combine. This function is excellent because it takes a list of text items and a separator, making it very clean to read if you are combining many columns.

To use it:

1. Go to Add Column > Custom Column.
2. Name your new column.
3. The formula takes two arguments: a list of columns and a separator. You put the column names inside curly braces {} to create the list.

=Text.Combine({[First Name], [Last Name]}, " ")

This formula tells Power Query to combine the items in the list ({First Name, Last Name}) and place a space between each of them. It’s a very elegant solution, especially if you were combining three or four columns, like city, state, and zip code.

Method 2: Using DAX for Calculated Columns

Now, let's switch gears and move to DAX. You would use this method in the Report or Data view of Power BI Desktop, not in the Power Query Editor. Remember, DAX calculations happen after the data is loaded.

To create a calculated column:

1. Make sure you’re in the Data view (the little spreadsheet icon on the left).
2. Select the table you want to add the column to from the Fields pane on the right.
3. From the Table tools tab at the top, click "New column." A formula bar will appear.

Technique A: The Simple Ampersand (&) in DAX

Just like in Power Query, the ampersand works beautifully in DAX. The syntax is nearly identical.

In the formula bar, type:

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

• The main difference in DAX is that you have to specify the table name followed by the column name in brackets (e.g., 'YourTable'[ColumnName]).
• Press Enter, and the new calculated column will appear in your table.

Technique B: The CONCATENATE Function

DAX has a function specifically for this, called CONCATENATE. It’s straightforward but has one big limitation: it only accepts two arguments at a time.

Here's how you'd write the formula for our "Full Name" example:

Full Name = CONCATENATE('YourTable'[First Name], " ")

Wait, that only gives you "John ". To add the last name, you'd have to nest another CONCATENATE function, which can get messy:

Full Name = CONCATENATE('YourTable'[First Name], CONCATENATE(" ", 'YourTable'[Last Name]))

As you can see, the ampersand (&) is much cleaner and more readable than nesting multiple CONCATENATE functions. For this reason, most Power BI professionals prefer using the ampersand for DAX concatenation.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Tips and Best Practices for Concatenation

• Handling Nulls: When a column contains a blank or null value, concatenation can produce odd results, like an extra space ("John "). Power Query's "Merge Columns" feature handily ignores nulls. If you're building a formula yourself, you may need a bit of if logic to check for blanks if they're causing issues in your output.
• Data Types: If you try to concatenate a text column with a number or date column, Power BI will generally convert the non-text column to text for you automatically. However, the formatting might not be what you expect (e.g., dates might turn into a long serial number). It's best practice to explicitly convert columns to Text type first in Power Query for consistent results.
• Performance: Performing transformations in Power Query is almost always better for performance than with a DAX calculated column. Power Query performs the step once during data refresh, while a DAX calculated column is re-evaluated and uses up memory and processing power in your model file.

Final Thoughts

Mastering concatenation in Power BI is a small skill that pays big dividends, making your reports clearer and your data models more robust. Whether you prefer the simple UI of Merge Columns in Power Query, the flexibility of the ampersand operator, or need the dynamic power of a DAX calculated column, you now have the tools to combine text like a pro.

For us, wrestling with formulas like Text.Combine or remembering the precise DAX syntax is exactly the kind of friction we wanted to eliminate. Learning a complex BI tool is a significant time investment just to get basic insights from your data. We built Graphed to be the opposite of that experience. Instead of clicking through menus or looking up formulas, you just connect your data sources — like Google Analytics, Shopify, or Salesforce — and ask for what you want in plain English. Want to see your sales by campaign? Just ask, and Graphed builds the charts for you in seconds, no M code or DAX required.