How to Join Columns in Power BI

Combining columns in Power BI is a fundamental step for cleaning up your data and preparing it for analysis. Whether you need to create a full name from separate first and last name columns, build a unique ID key, or format an address for a report, merging columns makes your data model more readable and your reports more intuitive. This guide walks you through the two main ways to join columns in Power BI, complete with practical steps and tips on which method to choose.

We'll cover the simple point-and-click method using the Power Query Editor and the more flexible approach using DAX for calculated columns. By the end, you'll be able to merge data cleanly and efficiently for any reporting need.

Why Merge Columns in Power BI?

Before diving into the "how," let's quickly discuss the "why." Merging columns is more than just a formatting trick, it's a critical data transformation technique that serves several important purposes in your reports and data models.

• Creating Readable Labels: The most common use case is combining two or more text fields to create a single, easy-to-read label. For instance, joining a 'FirstName' column (John) and a 'LastName' column (Smith) into a 'FullName' column (John Smith) makes charts, tables, and slicers far more user-friendly. No one wants to see separate first and last name columns taking up space in a legend.
• Building Unique Identifiers (Composite Keys): Sometimes, a single column isn't enough to uniquely identify a row in your data. You might need to merge multiple columns to create a composite key. For example, if you have 'OrderID' and 'ProductID' columns, merging them could create a 'UniqueOrderItemID' (1001-P45) that establishes a truly unique identifier for creating relationships between tables.
• Geographic Data Formatting: Visuals like maps often require location data to be in a specific format. Merging 'City', 'State', and 'Country' columns into a single 'FullLocation' column (Austin, Texas, USA) can help Power BI's mapping engine more accurately plot your data points.
• Simplifying Your Data Model: While it might seem counterintuitive to add a column to simplify things, having a single, well-defined column can be easier to manage than multiple component columns. It reduces clutter in the Fields pane and makes writing DAX measures simpler down the line, as you're referencing one clean column instead of several smaller ones.

Method 1: Using the 'Merge Columns' Feature in Power Query

The easiest and most common way to join columns is within the Power Query Editor. This is the recommended approach for most data preparation scenarios because it happens during the data transformation stage, before your data is even loaded into the Power BI data model. This makes the process efficient and keeps your final model lean.

Let’s walk through the exact steps to merge a 'FirstName' and 'LastName' column into a 'FullName' column.

Step-by-Step Guide to Merging Columns in Power Query

1. Open the Power Query Editor

First, you need to open the data shaping layer of Power BI. From your main Power BI Desktop report view, go to the Home tab ribbon and click on Transform data. This will launch the Power Query Editor window.

2. Select the Columns You Want to Merge

In the Power Query Editor, find the table containing the columns you want to join. Then, select the columns. The order in which you select them is important! Power Query will combine the values in the order you click them.

To create a 'FullName' column, you'll want to click the 'FirstName' column header first. Then, hold down the Ctrl key on your keyboard and click the 'LastName' column header. Both columns should now be highlighted.

3. Use the 'Merge Columns' Command

With your columns selected, you have two ways to access the 'Merge Columns' feature:

• From the Ribbon: Go to the Add Column tab on the Power Query ribbon. In the 'Text' section, click on Merge Columns.
• By Right-Clicking: A faster way is to simply right-click on either of the selected column headers. A context menu will appear, select Merge Columns from this menu.

4. Configure the Merge Options

A 'Merge Columns' dialog box will pop up, asking for two pieces of information:

• Separator: This is what will go between the values from each column. You can choose from a dropdown list of common separators (like Space, Comma, or Equal Sign) or select a 'Custom' one and type in anything you want (e.g., a hyphen - or a pipe |). For our 'FullName' example, we'll choose Space.
• New column name: This is where you name your new, shiny, merged column. Let's call it FullName. Giving it a descriptive name is crucial for keeping your data model organized.

Once you've configured these options, click OK.

5. Close & Apply

Instantly, you’ll see your new 'FullName' column appear in the Power Query Editor, and the original 'FirstName' and 'LastName' columns will be gone since the merge operation replaces them by default. If you wanted to keep the original columns, you could use the 'Merge Columns' feature from the Add Column tab instead of the Transform tab. The 'Add Column' version creates a new column while preserving the originals.

Finally, click Close & Apply on the Home tab to load your changes into the Power BI model. You can now use your perfectly formatted 'FullName' column in all of your visualizations!

Method 2: Using DAX to Create a Calculated Column

Sometimes, you need more flexibility than Power Query offers, or you may prefer to create a merged column directly in your data model after the data has been loaded. This is where DAX (Data Analysis Expressions) comes in handy. Using DAX, you can create a calculated column that combines other columns.

This method is useful when your merge logic depends on other calculations or when you need to use conditional logic (like an IF statement) within your concatenation.

Method 2a: The Ampersand (&) Operator

The simplest way to join text strings in DAX is with the ampersand (&) operator. It's concise, readable, and perfect for straightforward concatenation.

How to Use It:

1. From the Report View or Data View in Power BI desktop, select the table you want to add the column to.
2. In the table tools ribbon that appears, click New column.
3. A formula bar will appear. Type your formula using an ampersand to link columns and text strings. To create a 'FullName' and include a space, your formula would look like this:

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

Here’s a breakdown of the formula:

• FullName = Names your new calculated column.
• [FirstName] References the FirstName column.
• & " " & Concatenates the first name with a literal space character (anything inside double quotes is treated as a text string).
• [LastName] References the LastName column.

Press Enter, and your new column will be generated row by row based on this logic.

Method 2b: The CONCATENATE and COMBINEVALUES Functions

DAX also includes functions specifically for combining text. While the ampersand is often preferred for readability, these functions have their own uses.

CONCATENATE Function

The standard CONCATENATE function is limited. It can only join two text items at a time. It's an older function that exists mostly for Excel compatibility. To replicate our 'FullName' example, you’d have to nest the function, which gets messy fast:

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

As you can see, the ampersand & is much cleaner and is generally the better choice for this task.

COMBINEVALUES Function

A far more modern and efficient DAX function is COMBINEVALUES. This function is specifically optimized for concatenation and can join multiple values with a single delimiter. It's especially good for creating composite keys because it’s performance-tuned for that purpose.

The syntax for COMBINEVALUES is:

State City Key = COMBINEVALUES(", ", [State], [City])

Which one should you use? For simple readability, the & operator is excellent. For performance, especially when building keys from multiple columns that will be used in relationships, COMBINEVALUES is a better choice.

Power Query vs. DAX: Which Should You Use?

So, you know two ways to get the same result. How do you decide which one to use? Here's a simple breakdown:

Go with Power Query (Merge Columns) When:

• You're in the data cleaning phase. Merging columns is fundamentally a data transformation task, and Power Query is the engine built for exactly that. It's best practice to shape your data as much as possible before it gets loaded.
• Performance matters. Transformations done in Power Query are compressed more efficiently in the data model. DAX calculated columns are not compressed as well and are calculated when the data is refreshed, which can add to your refresh time and file size.
• The logic is simple. If you just need to join columns with a static separator, the Power Query user interface is faster and requires no code.

Lean on DAX (Calculated Columns) When:

• You need a dynamic or conditional result. If your logic relies on certain conditions (e.g., only combine columns if another column has a certain value), DAX provides the flexibility of an IF statement. For example: IF(ISBLANK([MiddleName]), [FirstName] & " " & [LastName], [FirstName] & " " & [MiddleName] & " " & [LastName]). Power Query can also do this, but it's much easier to write this logic in DAX.
• Your new column depends on another DAX measure or calculated column. DAX columns exist after the data load, so they can reference other measures or columns, while a Power Query step cannot.
• You can’t modify the source. If you don’t have access to go back into Power Query (perhaps because of permissions), DAX offers an in-model solution.

General Rule of Thumb: When in doubt, start with Power Query. Perform your data join transformations there as your first preference. Resort to DAX calculated columns only when the flexibility of DAX is absolutely necessary.

Final Thoughts

Combining columns in Power BI is a crucial skill for building clean, user-friendly reports. The most straightforward path is using the 'Merge Columns' feature in the Power Query Editor for performance and simplicity, while DAX calculated columns offer a flexible alternative for more complex, conditional logic directly within your data model.

Manually preparing data, even with powerful tools like Power BI, often involves dozens of these small but time-consuming steps. Sometimes you don't want to get into the weeds of Power Query or DAX just to see how your website users from the UK who arrived via a specific campaign are performing. For that, we built Graphed. By integrating all your sources in just a few clicks, we let you create real-time dashboards and reports by simply describing what you want in plain English, helping you skip the manual data wrangling and get straight to the insights.