How to Merge Two Columns in Power BI Using DAX

Combining columns in Power BI is a common task, especially when you need to clean up data or create more readable labels for your reports. If you have "First Name" and "Last Name" in separate columns, you'll almost certainly want to merge them into a single "Full Name" column for your charts and tables. This article will walk you through several ways to merge columns in Power BI using DAX, from basic functions to more robust and flexible methods.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Merge Columns in Power BI?

Before jumping into the DAX formulas, let's look at a few common scenarios where merging columns is necessary. This isn't just a technical exercise, it directly impacts the usability and clarity of your dashboards.

• Creating Full Names: The most frequent use case is combining a 'First Name' and 'Last Name' column into a single 'Full Name' field. It's much cleaner to display "John Smith" in a table than to have two separate columns.
• Building Complete Addresses: If your data has columns for 'Street Address', 'City', 'State', and 'Zip Code', you can combine them into a single, well-formatted 'Full Address' field. This is perfect for tooltips or detailed report views.
• Generating Unique IDs: Sometimes you need to create a unique identifier from multiple fields. For example, you might merge an 'OrderID' and a 'ProductID' to create a unique 'OrderLineItemID' for analysis.
• Improving Chart Labels: Merging columns can make your chart axes and legends much more descriptive. Instead of just showing a year, you could combine a 'Quarter' and 'Year' column to create labels like "Q1 2023", "Q2 2023", etc.
• Adding Context to Data: You might combine a product name with its category or a campaign name with its channel (e.g., "Summer Sale - Facebook Ad") to provide instant context in your visualizations.

In all these cases, merging columns transforms raw data into more useful and presentable information. Doing this in Power BI is best accomplished by creating a calculated column with a DAX formula.

Method 1: The Basic Approach with CONCATENATE

DAX's most straightforward function for joining text is CONCATENATE(). This function takes exactly two text arguments and merges them together.

Let's use the classic 'First Name' and 'Last Name' example. Imagine you have a 'Customers' table that looks like this:

Our goal is to create a new column called 'FullName'. Here's how to do it step-by-step:

1. Navigate to the Data View in Power BI (the grid icon in the left-hand pane).
2. Select the 'Customers' table from the Fields pane on the right.
3. Go to the Table tools tab at the top and click on New column.
4. This will open up the formula bar. Enter the following DAX formula:

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

1. Press Enter to create the column.

You'll see a new 'FullName' column, but it will look like this: "AnnaKendrick", "BlakeLively", "RyanReynolds". We've combined the columns, but there’s no space!

This reveals the main limitation of CONCATENATE(): it only accepts two arguments. To add a space, you would need to use a second, nested CONCATENATE() function, which can get clunky very quickly:

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

While this works, it’s not very readable. Thankfully, there's a much more elegant and flexible way.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 2: Using the Ampersand (&) Operator (The Better Way)

The ampersand (&) is DAX's concatenation operator, and it's your best friend for merging columns. It allows you to chain together as many columns and text strings as you need, making your formulas much cleaner and easier to read.

Let's fix our 'FullName' column. Instead of using CONCATENATE(), create a new calculated column with this formula:

FullName = 'Customers'[FirstName] & " " & 'Customers'[LastName]

This simple formula does three things:

1. It takes the value from the [FirstName] column.
2. It appends a literal space character, which we define with " ".
3. It appends the value from the [LastName] column.

The result is a perfectly formatted "Anna Kendrick", "Blake Lively", and "Ryan Reynolds".

The & operator is extremely powerful because you can add any text or delimiter you want. For example, if you wanted the format "LastName, FirstName", you would write:

FullName = 'Customers'[LastName] & ", " & 'Customers'[FirstName]

This would produce "Kendrick, Anna", "Lively, Blake", etc.

Example: Merging City and State

Let's say you have city and state data and want to create a standard 'Location' label. You could use this:

Location = 'Customers'[City] & ", " & 'Customers'[State]

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 3: A More Modern Solution with COMBINEVALUES

More recently, DAX introduced the COMBINEVALUES() function. It’s designed specifically for this purpose and offers a slightly cleaner syntax when you're using a consistent delimiter.

The syntax is: COMBINEVALUES(<delimiter>, <expression1>, <expression2>, ...).

To create our 'Full Name' column using this function, the formula would be:

FullName = COMBINEVALUES(" ", 'Customers'[FirstName], 'Customers'[LastName])

Here, the first argument is the delimiter (a space), followed by all the columns you want to join. This is especially handy when you need to merge three or more columns. For a full address, the formula is much neater than chaining multiple & operators:

FullAddress = COMBINEVALUES(", ", 'Customers'[Street], 'Customers'[City], 'Customers'[State], 'Customers'[ZIP])

One key benefit of COMBINEVALUES() is how it handles blank values. If one of the columns in the list is blank, it intelligently omits the delimiter, preventing awkward results like leading or trailing commas. This makes it a more robust option for dirty or incomplete data.

Practical Tips: Handling Blanks and Formatting

When you merge columns, you often encounter little formatting issues. Here’s how to handle two of the most common ones.

Handling Blank Values Gracefully

What if some rows have a first name but no last name, or vice versa? Let's say your data has a null or blank value for FirstName. Using the ampersand (&) method ([FirstName] & " " & [LastName]), your result would be " Smith", with an unwanted leading space.

You can solve this by wrapping your concatenation formula in the TRIM() function. TRIM() removes all spaces from a text string except for single spaces between words.

FullName = TRIM('Customers'[FirstName] & " " & 'Customers'[LastName])

Now, if [FirstName] is blank, the TRIM() function will remove the leading space, leaving you with just "Smith". As mentioned, COMBINEVALUES() handles this logic automatically, making it a stronger choice in many cases.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Combining Text with Numbers or Dates

Another common scenario is combining a text field with a number or date field, such as "Sales for 2023" or "Product Cost: $1,499.00". When you concatenate a number, Power BI will convert it to text, but it will lose the formatting (like currency symbols, commas, or decimal places).

To preserve the format, use the FORMAT() function within your concatenation formula. The FORMAT() function converts a value to text using a specified format string.

For example, imagine you have a 'Products' table with 'ProductName' and 'Price' columns. To create a label like "Laptop - $1,499.00", you would use:

ProductAndPrice = 'Products'[ProductName] & " - " & FORMAT('Products'[Price], "$#,##0.00")

Similarly, for dates, you could use a formula like this to create a report period label:

ReportPeriod = "Week Ending: " & FORMAT('Calendar'[Date], "mmmm d, yyyy")

Calculated Column or Measure?

A quick but critical technical note: when merging row-level values like in all the examples above, you should always use a Calculated Column.

Here's a simple breakdown:

• Calculated Column: Is calculated once for each row in your table when you refresh the data. It's stored in your data model, takes up memory, and can be used like any other column - on an axis, in a legend, or as a slicer. This is exactly what we need for creating labels like 'Full Name' or 'Location'.
• Measure: Is calculated on-the-fly based on the context of your report (e.g., filters applied). Measures are used for aggregations like sums, averages, or counts. They aren't stored row-by-row and can't be used to label an axis. A measure wouldn't be able to "see" the individual first and last names to combine them.

So, when you're merging column data, always remember to go to Table tools > New column.

Final Thoughts

Merging columns in Power BI using DAX is a fundamental skill that dramatically improves the clarity and professionalism of your reports. Using the ampersand operator (&) or the COMBINEVALUES() function will cover nearly every use case you encounter, giving you clean, readable labels for your tables and visuals.

Working through tasks like this is core to building great dashboards, but sometimes you just need to get straight to the insights. Manually creating every calculated column or custom formula takes time away from actual analysis. At Graphed , we’ve created an AI data analyst that handles this for you. You can connect your data sources and simply ask questions like, "Show me revenue by full name from my Salesforce contacts table." It handles the data joining, formatting, and visualization instantly, letting you stay focused on what the data means instead of how to combine it.