How to Insert Merged Column in Power BI

Combining text from different columns into one is a common and surprisingly powerful task in data analysis. Maybe you have separate "First Name" and "Last Name" columns you need to join, or perhaps you want to create a full address from street, city, and state columns. This article will walk you through exactly how to merge columns in Power BI using the Power Query Editor, turning separate pieces of data into a single, more useful column.

Why Merge Columns in Power BI?

Before jumping into the "how," let's quickly cover the "why." Merging columns isn't just about making your tables look tidy, it solves real-world reporting challenges. It allows you to shape your data so it's easier to work with when you start building visualizations.

Here are a few common scenarios where merging columns is essential:

• Creating Full Names: The most classic example. Combining a "First Name" and "Last Name" column into a single "Full Name" column is great for labeling charts, tables, and slicers.
• Building Unique IDs: Sometimes your data doesn't have a single unique ID for each row. You might need to create one by merging multiple columns, like combining an "OrderID" with a "ProductID" to get a unique identifier for each line item.
• Geographical Data: Merging "City," "State," and "Country" columns can create a single location field that is easier to use in maps and reports.
• Concatenating Product Information: You might want to display product details by combining "Product Category," "Brand," and "Product Name" into one descriptive label.

Simply put, merging columns cleans up your data model, makes your reports more readable, and can help you create necessary keys for connecting different tables.

Getting Started: An Introduction to the Power Query Editor

The magic of merging columns happens not in the main Power BI report view, but in the Power Query Editor. If you're new to Power BI, think of the Power Query Editor as your data workshop. It's where you clean, transform, and prepare your data before loading it into your data model for visualization. Any changes you make here - like merging columns, filtering rows, or changing data types - are applied every time you refresh your data.

To open the Power Query Editor, just click the Transform data button on the Home ribbon in Power BI Desktop.

Once you click it, a new window will open, showing you all your data in tables. This is where we'll do our work.

Step-by-Step Guide to Merging Columns

Let's walk through the most common example: combining a "First Name" and "Last Name" column. Let’s assume your table looks something like this:

Step 1: Select the Columns to Merge

First, identify the columns you want to combine. The order you select them in is important because it determines the order they will appear in the final merged column. Since we want our result to be "First Name [space] Last Name," we'll select the FirstName column first.

Click the header of the FirstName column to select it. Then, hold down the Ctrl key and click the header of the LastName column. Both columns should now be highlighted.

Step 2: Choose Between 'Transform' and 'Add Column'

Look at the ribbon at the top of the Power Query Editor. You'll see two tabs that look relevant: Transform and Add Column. Both have a "Merge Columns" button.

• Transform > Merge Columns: This will merge your selected columns and replace them with the new single column. Use this if you are sure you won't need the original columns anymore.
• Add Column > Merge Columns: This will merge your selected columns and create a new column, leaving the original columns untouched. This approach is generally safer and recommended, as it preserves your original data.

We'll use the safer method. Navigate to the Add Column tab.

Step 3: Open the Merge Columns Tool

In the "From Text" section of the Add Column ribbon, click Merge Columns.

This will open a dialog box where you can configure the details of your merge.

Step 4: Configure the Merge

The Merge Columns window gives you two simple options:

1. Separator: Select the character or text to put between the values from your columns. Power BI offers common options like Space, Comma, Colon, Semicolon, etc. For our "Full Name" example, choose Space.
2. New column name: Name your new column. Use a clear name like FullName. This helps your data model be understandable.

Once you've selected a separator and entered your new column name, click OK.

Step 5: Review the Result

Power Query will execute the merge step immediately and add a new FullName column at the right side of your table. Each row now contains the merged first and last names, separated by a space.

That's it! When you're done with data transformations, remember to click Close & Apply on the Home tab to load the changes into your Power BI data model.

Advanced Merging: Creating a Custom Separator

What if you need something more specific? Suppose merging City, State, and ZipCode into an address like "Seattle, WA 98101". You need a comma and a space between City and State, but just a space between State and ZipCode.

You can't do this in a single merge operation with the standard tool, but you can do it in two steps, or by using a custom separator for a simpler case.

For example, for "City, State", in the separator dropdown, choose Custom. A textbox will appear. Type , (a comma followed by a space).

Follow the same process:

• Select City, then Ctrl+click State.
• Go to Add Column > Merge Columns.
• Choose Custom as separator and type , .
• Name the new column CityState and click OK.

Then, you can merge CityState with ZipCode using a space as separator if desired.

Pro Tips for Merging Columns

The basic merging function is straightforward, but here are additional tips:

Handling Null or Blank Values

If one cell is null or empty (e.g., no last name), the merge handles it gracefully. It ignores null values for separators, so you won't get extra spaces or separators. For instance, if John has no last name, the merged column will just show "John".

Verifying Data Types

The new merged column always has a "Text" data type. If you're merging number columns (like Year, Month, Day), you may need to convert the new column to Date: right-click the column header, select Change Type > Date.

Moving the New Column

By default, the new column appears on the far right. To move it next to the original columns, click and drag the column header to your preferred position.

Beyond Merging: Using Custom Columns for Conditional Logic

Sometimes, merging isn't enough. You might want to merge AddressLine1 and AddressLine2, but only add a comma if AddressLine2 contains data.

For this, use the Custom Column feature:

1. Go to Add Column > Custom Column.
2. Name it FullAddress.
3. Enter a formula, for example:

if [AddressLine2] = null or [AddressLine2] = "" then [AddressLine1] else [AddressLine1] & ", " & [AddressLine2]

This formula checks if AddressLine2 is empty. If so, use only AddressLine1, otherwise, combine them with a comma.

This approach gives you full control over how columns are combined based on specific conditions.

Final Thoughts

Merging columns is a key skill in Power BI that helps clean and structure your data for better insights. Mastering the Power Query Editor's merge tools enables you to easily create concatenated name, location, or ID fields, improving your reports' clarity.

Power BI also allows advanced data transformations via the Power Query M language, which can automate complex logic. At Graphed, we've developed solutions that interpret plain English instructions like "create a full name by combining first and last names with a space," streamlining your workflow and reducing manual effort. This allows you to focus on analysis and strategy instead of data preparation.