How to Combine Two Columns in Power BI

Combining columns in Power BI is a fundamental task for anyone looking to clean up data and create more meaningful reports. Whether you need to create a full name from separate first and last name columns or build a unique ID for your records, merging data is an essential skill. This guide will walk you through a few different ways to combine columns in Power BI, from the user-friendly interface to more flexible formulas.

Why Combine Columns?

Before jumping into the "how," let's quickly touch on the "why." You'll find yourself needing to merge columns in many common scenarios, such as:

• Creating a Full Name: The most common example is combining a "First Name" and a "Last Name" column into a single "Full Name" column for easier reading in charts and tables.
• Building a Location Field: You might want to combine "City," "State," and "Country" columns to create a single, unified "Location" field.
• Constructing a Unique Identifier (ID): Sometimes, you need to create a unique key by merging multiple fields, like an order number and a product SKU, to analyze individual line items.
• Concatenating Text and Numbers: You might want to combine a product name with its model number (e.g., "iPhone" + "15 Pro") for more descriptive labels in your reports.

Once you get the hang of it, you'll see opportunities to tidy up your data everywhere. Let’s get to it.

Method 1: Using the Merge Columns Feature in Power Query

The easiest and most direct way to combine columns is by using the built-in "Merge Columns" feature within the Power Query Editor. This is the perfect approach for straightforward merging without needing to write any code.

Power Query is Power BI’s data transformation engine, where you clean and prepare your data before loading it into your report. Performing transformations here is usually the best practice for permanent changes to your data structure.

Step-by-Step Instructions:

For this example, let's say we have two columns, FirstName and LastName, that we want to combine into a new FullName column.

1. Open the Power Query Editor: From the main Power BI Desktop window, go to the Home tab and click on Transform data. This will launch the Power Query Editor where the magic happens.

2. Select the Columns you Want to Merge: In the Power Query Editor, find the columns you want to combine. Select the first column (e.g., FirstName). Then, hold down the Ctrl key (or Cmd on a Mac) and click the next column(s) (e.g., LastName). The order in which you select them matters - this will be the order they appear in the merged column.

3. Choose the "Merge Columns" Option: You have two choices here, depending on whether you want to keep the original columns:

• To create a new column and keep the originals: Go to the Add Column tab in the ribbon and click Merge Columns.
• To merge and replace the original columns: Go to the Transform tab in the ribbon and click Merge Columns. This option is less common as you usually want to preserve your original data.

4. Configure the Merge Settings: A "Merge Columns" dialog box will appear. Here, you'll configure how the columns are combined:

• Separator: This is what goes between the text from each column. You can pick from common options like a space, comma, or colon. If what you need isn't there, select "Custom" and enter your own separator (like " - "). For our Full Name example, a Space is perfect.
• New column name: Give your new column a descriptive name, like "FullName". If you skip this, it will be named "Merged," which isn't very helpful.

Click OK. And just like that, you’ll see your shiny new FullName column at the end of your table! Don’t forget to click Close & Apply in the top-left corner of the Power Query Editor to save your changes and load them into your Power BI report.

Method 2: Creating a DAX Calculated Column

Another way to combine columns is by using Data Analysis Expressions (DAX) to create a calculated column. This method is done directly in the Power BI Desktop’s Data view, after your data has been loaded from Power Query.

When to Use DAX Instead of Power Query:

• You need the new column's values to be dynamically calculated based on other measures or context in your report (though this is less common for simple concatenations).
• The source data cannot be changed, or you prefer to keep transformations within the modeling layer.
• The logic is simple and you just need a quick combination without going back into the Power Query Editor.

Keep in mind that calculated columns created with DAX are stored in your model and consume memory and resources, so for permanent transformations, Power Query is generally the more efficient choice.

The simplest way to combine text in DAX is with the ampersand (&) operator.

Step-by-Step Instructions:

1. Navigate to the Data View: In the main Power BI Desktop window, click on the Data icon in the left-hand navigation pane.

2. Create a New Column: Make sure you have the correct table selected in the Fields pane on the right. Then, go to the Column tools tab and click New column.

3. Write the DAX Formula: The formula bar will appear. Here, you'll write the DAX expression to combine your columns. The format is: New Column Name = [Column1] & "Separator" & [Column2]

For our Full Name example, the formula would be:

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

Let’s break that down:

• FullName = defines the name of our new column.
• [FirstName] & [LastName] references the columns we want to merge.
• " " is the separator - a single space inside quotation marks.
• The & operator joins it all together.

Press Enter to create the column. You will see it appear in your data table instantly.

Alternative: The CONCATENATE Function

DAX also has a CONCATENATE() function, but it only accepts two arguments at a time. This makes the ampersand operator (&) much cleaner and easier to read when you're combining more than two items. Using CONCATENATE() for our example would look a bit clunkier:

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

For this reason, most people stick with the & for simple text combinations in DAX.

Method 3: Creating a Custom Column in Power Query

If you need more control and flexibility than the standard "Merge Columns" feature offers, you can create a Custom Column in Power Query and use the M language formula to combine columns. This approach is powerful when you need to embed conditional logic.

For example, what if some of your last names are missing? With a custom column, you can check if a column is null before merging it.

Step-by-Step Instructions:

1. Open the Power Query Editor and Select "Custom Column": Go to the Add Column tab and click on Custom Column.

2. Write the M Language Formula: The "Custom Column" dialog box will open. Here you'll enter a new column name and the M formula. The syntax for combining text in M language is very similar to DAX. You use the & operator.

For a basic merge of FirstName and LastName, the formula is:

[FirstName] & " " & [LastName]

Power Query will tell you if your formula has any syntax errors before you even click OK.

Advanced Example: Handling Null or Blank Values

What happens if someone has no entry for their last name? With the simple formula, you'd get "John " with a trailing space. Let’s clean that up with some simple logic:

if [LastName] = null or [LastName] = "" then [FirstName] else [FirstName] & " " & [LastName]

This formula checks if the LastName column is empty. If it is, the new column will only contain the FirstName. If not, it will combine both with a space in between. This kind of flexibility is where custom columns truly shine.

Another M Formula: Text.Combine()

Power Query also has a dedicated function, Text.Combine(), which is great for combining a list of text items with a separator. This is a very clean and explicit way to write your formula.

Text.Combine({[FirstName], [LastName]}, " ")

• The first part, {[FirstName], [LastName]}, creates a list of columns.
• The second part, " ", specifies the separator to put between each item in the list.

Choosing the Right Method for You

So which method should you choose? Here’s a quick summary:

• For quick, simple merging, use the Power Query Merge Columns feature. It's visual, fast, and requires no code.
• For new columns based on data already in your model, or for simpler transformations without leaving the main window, DAX Calculated Columns work well. Just be mindful of performance implications on large datasets.
• For complex scenarios with conditional logic or handling potential errors like nulls, create a Custom Column in Power Query. It gives you the most power and flexibility.

As a best practice, try to do as many transformations as you can in the Power Query Editor. This keeps your data preparation steps organized in one place and typically results in a more efficient and responsive report.

Final Thoughts

Combining columns is a core skill in Power BI that helps transform raw data into a clean, report-ready format. By mastering the point-and-click Merge Columns feature, DAX calculated columns, and flexible Custom Columns in Power Query, you can handle virtually any concatenation scenario that comes your way, making your data infinitely more useful and your reports much clearer.

We know that even "simple" tasks like this in Power BI can be tedious, especially when you’re pulling information from many different places like Salesforce, Google Analytics, Shopify, and various ad platforms. We built Graphed to remove this friction by connecting all your data sources automatically and letting you build reports using simple, natural language. Instead of spending time clicking through menus, you can just ask questions like "Show me sales by region" and get an interactive dashboard built in seconds, freeing you up to focus on strategy instead of report-building.