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.

Power BI Concatenate Function

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:

ColumnsData Types
Customer IDSingle line of text
First NameSingle line of text
Last NameSingle line of text
Email AddressSingle line of text
CitySingle line of text
StateSingle line of text
CountrySingle line of text
Purchase AmountCurrency
Purchase DateDate and time
power bi merge columns

Now follow the below steps:

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

merge column power bi

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

how to merge column in power bi

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.

combine columns power query

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.

merge two columns power bi

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.

how to combine two columns in power bi

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.’

merging columns in power bi

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

combine two columns in power query

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.

merge columns power query

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

power query combine columns

Then click Close & Apply.

See also  Power BI Union Two Tables [With Examples]

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.”

dax merge columns

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.
how to merge two columns in power bi

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

How to Merge Two Columns in Power BI using DAX

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.

how to merge cells in power bi

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.

merge 2 columns in power bi

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.
how to concatenate multiple columns in power bi

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

how to merge columns in power bi

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

See also  How to Add Data Labels in Power BI? [With Examples]

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.

how to merge cells in power bi

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

combine columns in power bi

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]
power query combine two columns

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

combine columns in power query

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.

Power Query Merge Column but keep original

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

Power Query Merge Column but keep original column

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.

Power Query Merge Column but keep the original example

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

Power Query Merge Columns but Keep the Original

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.

Power Query Editor Merge Columns ignore null

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.

See also  Power BI Slicer Sort Descending [With Examples]

To do this, follow the below steps:

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

Examples of Power Query Merge Columns ignore null

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

power bi merge columns but keep original

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.

Power Query Merge Column but keep the original example

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

Power Query Merge Column Ignore Null

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:

ProductNameCategoryColor
ShirtClothingRed
PantsClothingBlue
MugHomeWhite

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”

table.combine columns power query

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.
power query combiner.combinetextbydelimiter

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

power query text.combine

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:

>