Merge Columns in Power BI

In Power BI, merging columns means combining information from different columns into one. We use the Power BI Concatenate Function to merge columns in Power BI.

This tutorial will teach us about Power BI Concatenate Function and how to merge columns in Power BI using DAX.

Additionally, we will discuss the topics below:

• Combine two columns using Power Query Editor
• How to concatenate multiple columns in Power BI
• Combine two columns using Power Query
• Merge columns but keep the original using Power Query
• Power Query merge columns ignore null
• Power Query merge columns using the table.combinecolumns

Power BI Concatenate Function

The CONCATENATE function in Power BI lets you combine text from different columns or strings into a single text string.

Syntax:

CONCATENATE(<text1>, <text2>)

Where:

• CONCATENATE: It’s a function in Power BI that combines text from multiple columns into one.
• <text1>: This is the first piece of text that you want to combine.
• <text2>: This is the second piece of text that you want to combine.

For example:

If you have a column for first names and another for last names, you can use CONCATENATE to combine them into one column for full names.

Combine Two Columns using Power Query Editor

Now we see how to merge columns in Power BI.

Imagine you’re analyzing customer data for an online store. You have a SharePoint list for first and last names, but you’d like to combine them into a single “Full Name” column for easier visualization and analysis.

Here, we have a SharePoint list (Customer Data) that contains below columns with various data types:

Columns	Data Types
Customer ID	Single line of text
First Name	Single line of text
Last Name	Single line of text
Email Address	Single line of text
City	Single line of text
State	Single line of text
Country	Single line of text
Purchase Amount	Currency
Purchase Date	Date and time

Now follow the below steps:

1. Open the Power BI desktop. Then, from the top navigation bar, click on Get data -> More…

2. The Get Data dialog box will open. Click on Online Services, then choose SharePoint Online List and click Connect.

3. The SharePoint Online Lists will open a dialog box where you can enter the SharePoint site URL. Click ‘OK’ to proceed. If you’re creating a report for the first time, it will ask for your username and password to access the SharePoint online list or site.

4. After that, it will show all the lists and libraries on the SharePoint site. Choose the list you want to use in this Power BI report. If your list has clean data, click Load. Otherwise, click Transform Data.

5. In my case, I click Transform Data. Then, it will open the Power Query Editor, which you can see. Here, we need to remove the columns. To do this, click Home and click the Choose Columns option in the ribbon.

6. The Choose Columns dialog box opens. By default, Power BI loads all the list columns. You can choose the columns you need to create a report. Then click ‘OK.’

7. In the Power Query Editor, go to Transform, select First Name and Last Name by “ctrl + click,” then click Merge Columns.

8. Now, the merge column window will appear. Choose a separator to insert between the columns. Under the new column name, you can change the column’s name. Click OK.

9. Then, you can see the Full Name column created in the Power Query Editor.

Then click Close & Apply.

This way, the power query concatenates columns.

How to Merge Two Columns in Power BI using DAX?

Here, we merge the City, State, and Country three columns into a single column called “Full Address,” so you have a complete address for each customer in a single field.

I hope you loaded the above SharePoint list in Power BI.

Now follow the below steps:

Go to the Modeling tab in Power BI and select “New column.”

Then, in the formula bar, put below the DAX expression.

Full Address = 'Customer Data'[City] & ", " & 'Customer Data'[State] & ", " & 'Customer Data'[Country]

Where:

• Full Address = This names the calculated column we’re creating, which will contain the combined full address of each customer.
• ‘Customer Data'[City], ‘Customer Data'[State], ‘Customer Data'[Country] = These are columns from the ‘Customer Data’ table. They contain the city, state, and country information for each customer.
• & = This operator concatenates (join together) text strings.

When you go to the Table view, you can see a new column added Full Address.

This way, you can merge two columns in Power BI using DAX.

How to Concatenate Multiple Columns in Power BI?

Imagine you’re analyzing customer data. You have columns for the customer’s first, last, and city names.

By concatenating these columns, you can create a new column that displays the customer’s full name in a single, user-friendly format.

To do this, follow the below steps:

1. Go to the Table view, then under the Table tools, click New column.

2. Then, in the formula bar, put the below DAX expression.

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

Where:

• Full Name = This is the calculated column we create, containing each customer’s combined full name.
• CONCATENATE() = This function joins two text strings into one.
• ‘Customers'[First Name] = This column from the ‘Customers’ table contains customers’ first names.
• &” “ = This concatenates a space between the first and last names.
• ‘Customers'[Last Name] = This column from the ‘Customers’ table contains customers’ last names.

3. Then, you can see a new column added Full Name.

But here, we can’t add more than two values to concatenate multiple columns.

Power Query Combine Two Columns

This example shows how to combine two columns in Power Query Editor.

I hope you loaded the above data set into Power BI.

Now, click ‘Add Column’ in the Power Query editor and then ‘Custom Column’.

In the Custom Column dialog box, you enter a name for the new column, such as Full Name. In the formula box, you use the & operator to concatenate the columns. The ” ” adds a space between the first and last names. After that, click OK.

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

Then, you can see a new column added in the Power Query Editor.

Following these steps, you can combine two columns in Power Query Editor.

Power Query Merge Columns but Keep the Original

Now we see how we merge columns but keep the original column in Power BI using the Power Query Editor.

We again loaded the above SharePoint List into Power Query Editor to do this.

In the Power Query Editor, go to Add Column, select First Name and Last Name by “ctrl + click,” then click Merge Columns.

Now, the merge column window will appear. Choose a separator to insert between the columns. Under the new column name, you can change the column’s name. Click OK.

Now, the newly merged column and the original one are visible in the Power Query Editor.

You can merge columns using the Power Query Editor but keep the original column in Power BI.

Power Query Merge Columns Ignore Null

Let’s consider a scenario in which you manage a customer database for a retail company. Your database has columns for customers’ first, middle, and last names. However, not all customers have a middle name.

You need to merge these three columns to create a single “Full Name” column, but you want to ignore any null values (missing names) so that you don’t end up with extra spaces or “null” text in your “Full Name” column.

To do this, follow the below steps:

I hope you load the data set into Power Query Editor.

In the Power Query Editor, go to Add Column, select First Name, Middle Name, and Last Name by “ctrl + click,” then click Merge Columns.

Now, the merge column window will appear. Choose a separator to insert between the columns. Under the new column name, you can change the column’s name. Click OK.

Then, you can see a new column added in the Power Query Editor.

This way, you can Power Query merge columns and ignore null.

Power Query Merge Columns using the table.combinecolumns

Scenario:

You have a sales report with separate columns for the product’s name, category, and color. You must create a single column combining these details into a single descriptive column for a presentation.

Table Before:

ProductName	Category	Color
Shirt	Clothing	Red
Pants	Clothing	Blue
Mug	Home	White

Table After:

ProductDetails
Shirt – Clothing – Red
Pants – Clothing – Blue
Mug – Home – White

To do this, follow the below steps:

1. loaded data set into the Power Query Editor, which will look like the “Table Before”

2. Under the home tab, click Advanced Editor, then Put below m query code.

,
    CombinedColumns = Table.CombineColumns(
        #"Changed Type",
        {"ProductName", "Category", "Color"}, 
        Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None), 
        "ProductDetails"
    )
in
    CombinedColumns

Where:

• CombinedColumns = Table.CombineColumns() = This line starts the process of combining multiple columns into one.
• #”Changed Type” = This specifies the table to work on, which has had its column types changed.
• {“ProductName”, “Category”, “Color”}, = These are the columns being combined into one.
• Combiner.CombineTextByDelimiter(” – “, QuoteStyle.None), = This function combines the text from the specified columns, using ” – ” as a separator, without adding any quotes.
• “ProductDetails”= This is the name of the new column that will contain the combined text.
• in = This keyword indicates the end of the let statement and specifies the output of the query.
• CombinedColumns = This is the final result of the query, which includes the newly combined column.

3. After clicking the Done button, you can see the data set, which will look like the “Table After”.

This tutorial teaches how to join columns in Power BI using the Concatenate function and DAX and how to merge columns with the Power Query Editor to help you combine multiple columns effectively in Power BI.

We also learned how to merge columns in Power Query while keeping the original, how to merge columns ignoring null values, and how to merge columns using the table.combinecolumns() function in Power BI.

You may also like the following tutorials:

• Concatenate with Space in Power BI
• Concatenate Text and Number in Power BI
• Calculated Column Based on Another Column in Power BI
• Power BI Group by
• Compare Two Columns in Different Tables in Power BI