How to merge columns in Power Query

In this Power BI Tutorial, we will examine how to merge columns or combine columns based on conditions using the Power Query editor. We also covered the below-related headings:

1. Power Query Merge Columns with the same name
2. Power Query Merge Column but keep original
3. Power Query Merge Columns based on Condition
4. Power query merge columns greyed out
5. Power Query Merge Columns ignore null
6. Power Query Combine Columns with Delimeter
7. Power Query Merge Column sum
8. Power Query concatenates multiple columns from different tables
9. Power Query merge columns with a line break
10. Power Query merge columns remove duplicate
11. Power query merge columns if

Power Query Merge Columns with the same name

Let us see how we can merge columns with the same customer names using the Power Query editor in Power BI.

In this example, I will use the Customers Table data as mentioned below. You can see that the table has the same customer’s first name and last name. You can download the sample data from here.

• Open Power bi desktop and Load the data using the get data option.
• Click on the transform data from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.

• In the example, I will merge the Customer First Name, Customer Last Name, and the Customer Location column. In the Power Query editor select the columns you want to join as shown below: To Select the columns click on the ctrl key and select the columns.

• Go to the transform tab, text column section in the ribbon select the  Merge column option as shown below.

• Now merge column window will appear, and we can choose a separator to insert between the columns.
• Next under the new column name, you can also change the name of the column. Click on the Ok button.

• In the below screenshot you can see that the columns have been Merged and displayed in the table, click on the close and apply option so that the changes will appear on the Power Bi desktop.

= Table.CombineColumns(#"Removed Columns",{"First Name", "Last Name", "Customer Location"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged Values")

This is how we can merge columns with the same customer names using the Power Query editor in Power BI.

Also Read: How to Merge Column in Power BI

Power Query Merge Column but keep original

Let us see how we can display the merged column values in new columns and keep the original column in Power BI using the Power Query editor.

In this example, I will use the Customers Table data, display the Merged column values in new columns, and keeps the original columns as it is.

• Open Power bi desktop and Load the data using the get data option.
• Click on the transform data from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
• I will merge the Customer First Name, Customer Code, and Sales columns in the example. In the Power Query editor select the columns you want to join as shown below: To Select the columns click on the ctrl key and select the columns.
• Under the Add Column, select the Merge Columns option as highlighted below:

• Now merge column window will appear, and we can choose a separator to insert between the columns.
• Next under the new column name, you can also change the name of the column. Click on the Ok button.

• In the below screenshot you can see that the columns have been Merged and displayed in the new column, click on the close and apply option so that the changes will appear on the Power Bi desktop.

= Table.AddColumn(#"Removed Columns", "Merged", each Text.Combine({[First Name], Text.From([Customer Code], "en-US"), Text.From([Sales Amount], "en-US")}, ","), type text)

This is how we can display the Merged column values in new columns and keep the original column using the Power Query editor in Power BI.

Check out: Power BI Slicer Multiple Columns

Power Query Merge Columns based on Condition

Let us see how we can merge the columns based on conditions using the Power Query editor in Power BI.

In this example, I will use the Customer data, where we can filter the customer data based on the sales data value. If the sales value is greater than 100000 and less than 200000 then it should filter the data table value accordingly.

• Open Power bi desktop and Load the data using the get data option.
• Click on the transform data from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
• Expand the Sales Amount column and choose the Number Filters option and click on the Between option.

• In the filter rows pop up, Select the Basic option, and apply the condition based on the requirement as shown below: Click on the Ok button.

• In the below screenshot you can see that based on the sales amount condition it filters the row and displays the value.

= Table.SelectRows(#"Removed Columns1", each [Sales Amount] >= 100000 and [Sales Amount] <= 200000)

This is how to merge the columns based on conditions using the Power Query editor in Power BI.

Check: Power BI Create Table From Another Table

Power query merge columns greyed out

Let us know what the merge columns OK button is grayed out in the Power BI Power Query editor,

• Select the columns that you want to merge, then click on the Merge Queries option from the ribbon.
• In the Merge dialog box, you need to choose the 2nd table so that the OK button will become yellow to actually run the merge query as shown below:

• In the Merge dialog box, if you didn’t choose the 2nd table the Merge column Ok button will be Greyed out as shown below:

So, when you want to ok button to be enabled you have to choose the second table, if not the Ok button will be greyed out.

Also check: Power BI Compares Two Columns in Different Tables

Power Query Merge Columns ignore null

Let us see how we can merge columns by ignoring null using the Power Query editor in Power BI.

In this example, I’m going to ignore null values and Merge the columns using the Power Query editor. you can see in the below screenshot there are null values or blank values presented:

• Open Power bi desktop and Load the data using the get data option.
• Click on the transform data from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
• I will merge the Customer First Name, Customer Code, and the Sales Amount column in the example. In the Power Query editor select the columns you want to join as shown below: To Select the columns click on the ctrl key and select the columns.

• Go to the transform tab, text column section in the ribbon select the  Merge column option.
• Now merge column window will appear, and we can choose a separator to insert between the columns.
• Next under the new column name, you can also change the name of the column. Click on the Ok button.

• In the below screenshot you can see that the column has been merged by ignoring the null values in it.

= Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Customer Code", type text}, {"Sales Amount", type text}}, "en-US"),{"First Name", "Customer Code", "Sales Amount"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")

This is how to merge columns by ignoring null using the Power Query editor in Power BI.

Read: Power BI Sum Group by

Power Query Combine Columns with Delimeter

Let us see how we can combine the columns with Delimeter using the Power Query editor.

In this example, I’m going to Combine the columns called First Name and Last Name with Delimeter using the Power Query editor.

• Initially, load the data into the Power Bi desktop using the get data option. Select the transform data option from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
• In the Power Query editor select the columns you want to join as shown below: To Select the columns click on the ctrl key and select the columns.

• Now merge column window will appear, and we can choose a separator to insert between the columns.
• Next under the new column name, you can also change the name of the column. Click on the Ok button.

• In the below screenshot you can see that the column has been merged with the selected Delimeter using the Power Query editor.
• Click on the close and apply option so that the changes will appear on the Power Bi desktop.

= Table.CombineColumns(#"Changed Type",{"First Name", "Last Name"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")

This is how to combine the columns with Delimeter using the Power Query editor.

Example:2

Power Query merge columns percentage

Let us see how to merge columns with percentage values in Power BI using the Power Query editor.

• Load the data into the Power Bi desktop using the get data option. Select the transform data option from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
• In the Power Query editor select the columns you want to join as shown below: To Select the columns click on the ctrl key and select the columns.

• Now merge column window will appear, and we can choose a separator to insert between the columns.
• Next under the new column name, you can also change the name of the column. Click on the Ok button.

• In the below screenshot you can see that the column has been merged with the selected Delimeter using the Power Query editor.
• Click on the close and apply option so that the changes will appear on the Power Bi desktop.

This is how to merge columns with Percentage values in Power BI using the Power Query editor.

Read: Power BI Report Export to PDF [With 20+ Examples]

Power Query Merge Column sum

Let us see how we can Merge the columns and sum the values and display them in the new column using the Power Query editor in Power BI.

In this example, I have used the below sample table to sum the two columns using the Power Query editor in Power BI.

• Here, we will calculate the sum of sales and profit value using the power query editor in Power BI.
• Initially Load the data using the get data option and click on the Home and Select the Transform data option from the ribbon as shown below:

• In the Power Query editor, Select the Table and click on the add column option.
• After that, select the column that you want to add with other columns Expand the standard option and choose the operation that you want to perform.
• Here I am going to select Add operation as mentioned below:

• Under the values, select the columns that you want to add, and click on the Ok button.

• Now, you can see the Addition column displays the result of sales + profit columns using Power Query in Power BI.
• Click on the Close and Apply option from the ribbon, so that the changes will be saved.

This is how to merge the columns and sum the values and display them in the new column using the Power Query editor in Power BI.

Check out: Power BI Add Calculated Column [With Various Examples]

Power Query Merge Columns from different tables

Let us see how we can merge columns from different tables using the Power Query editor in Power BI.

In this example, I have used the Departments table and Employees table as highlighted below:

Employees Table:

Departments Table:

• Initially Load the data using the get data option and click on the Home and Select the Transform data option from the ribbon.
• Then click on Merge Queries and select the merge queries as new, it will create a new table where you can add columns from multiple tables.

• Then merge window will open and select the table and then select a column you want to merge. Then click on the OK button.
• Ensure that both the column you match from two different tables has the same number of rows and has some standard columns. In this example, the Department ID column is similar in both tables.

• Now you can expand the table (Employees table) and select the column you want to merge. Click on Ok.

• Now you can see the column has been merged from two different tables in the below screenshot.

This is how to merge columns from different tables using the Power Query editor in Power BI.

Read: Power BI Matrix Multiple Column

Power Query merge columns with a line break

Let us see how we can merge the columns with a line break using the Power Query editor in Power BI.

In this example, I have used the below-mentioned sample data table.

• Initially Load the data using the get data option and click on the Home and Select the Transform data option from the ribbon.
• In the Power Query editor, under the Add column choose the Custom column and write the below-mentioned formula:

= Table.AddColumn(#"Changed Type", "Custom", each 
let
teama = 
if [TeamA]="" then "" else "-"&[TeamA],
teamb = 
if [TeamB]="" then "" else "-"&[TeamB],
teamc = 
if [TeamC]="" then "" else "-"&[TeamC],
list = List.Select({teama,teamb,teamc},each _<>"")
in
Text.Combine(list,"#(lf)")
)

• In the below screenshot, you can see that the new custom column you can column has been merged with the line break.

This is how to merge the columns with the line break using the Power Query editor in Power BI.

Check: How to remove rows in power query editor [With various examples]

Power Query merge columns remove duplicate

Let us see how we can remove the duplicate values in the merged columns using the Power Query editor in Power BI.

• Load the data using the get data option and click on the Home and Select the Transform data option from the ribbon.
• In this example, you can see that highlighted data values are duplicate values.

• In the Power Query editor select the columns you want to merge as shown below: To Select the columns click on the ctrl key and select the columns.

• Now merge column window will appear, and we can choose a separator to insert between the columns.
• Next under the new column name, you can also change the name of the column. Click on the Ok button.

= Table.CombineColumns(#"Changed Type",{"Employee Id", "Employee Name"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")

• Once Merged from the ribbon select the Home –> Remove Rows –> Remove Duplicates as shown below:
• In the below screenshot, you can see that the column has been merged, and duplicate values are removed.

• This is how to remove the duplicate values in the merged columns using the Power Query editor in Power BI.

Also check: Power BI filter between two dates [With 15+ Examples]

Power query merge columns if

Let us see how we can merge columns if using the Power Query editor in Power BI,

In this example, I will check the Sum of the sales amount if greater than 1000 then it will display true else false in the custom column, after that I’ll merge the Custom column and Sum column.

• Load the data using the get data option and click on the Home and Select the Transform data option from the ribbon.
• In the Power Query editor, under the Add column choose the Custom column option and write the below mentioned below:

= Table.AddColumn(#"Changed Type", "Custom", each if [Sum] >= 1000 then "True" else "False")

• select the columns you want to merge as shown below: To Select the columns click on the ctrl key and select the columns.
• Now merge column window will appear, and we can choose a separator to insert between the columns.
• Next under the new column name, you can also change the name of the column. Click on the Ok button.

• In the below screenshot, you can see that the column has been merged based on the condition applied.

= Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Sum", type text}}, "en-US"),{"Sum", "Custom"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")

This is how to merge columns if in Power BI using the Power Query editor.

Also, you may like the below Power BI tutorials:

• How to Filter Power BI Dax Based On Condition
• How to Append Columns in Power Query
• How to Filter Date using Power BI DAX
• Power BI Sum Multiple columns [With 21 Useful Examples]
• Power BI divides two columns [With 14 real examples]
• Stacked Bar Chart in Power BI [With 27 Real Examples]
• Power BI Bookmarks [With 21 Examples]

This Power BI tutorial explains how we can merge the columns using the Power Query editor in Power BI. Also, covered the below-mentioned headings:

• Power Query Merge Columns with the same name
• Power Query Merge Column but keep original
• Power Query Merge Columns based on Condition
• Power query merge columns greyed out
• Power Query Merge Columns ignore null
• Power Query Combine Columns with Delimeter
• Power Query Merge Column sum
• Power Query concatenates multiple columns from different tables
• Power Query merge columns with a line break
• Power Query merge columns remove duplicate
• power query merge columns if