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.

Power Query Merge Columns with the same name
Power Query Merge Columns with the same name
  • 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.
Merge columns in the power query editor
Merge columns with the same name in the power query editor
  • 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.
Power Query Merge Columns with the same name example
Power Query Merge Columns with the same name example
  • Go to the transform tab, text column section in the ribbon select the  Merge column option as shown below.
Example of Power Query Merge Columns with the same name
Example of Power Query Merge Columns with the same name
  • 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.
Example of Power Query Merge Columns with same name
Example of Power Query Merge Columns with the same name
  • 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")
Example of the Power Query Merge Columns with same name
Example of the Power Query Merge Columns with the same name

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:
Power Query Merge Column but keep original
Power Query Merge Column but keep original
  • 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.
Power Query Merge Column but keep original example
Power Query Merge Column but keep the original example
  • 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)
Example of the Power Query Merge Column but keep original
Example of the Power Query Merge Column but keep original

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.

See also  Power BI date slicer between a default to today

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.
Power Query Merge Columns based on Condition
Power Query Merge Columns based on Condition
  • 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.
Power Query Merge Columns based on Condition example
Power Query Merge Columns based on Condition example
  • 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)
Example of Power Query Merge Columns based on Condition
Example of Power Query Merge Columns based on Condition

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:
Power query merge columns greyed out example
Power query merge columns greyed out example
  • 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:
Power query merge columns greyed out
Power query merge columns greyed out

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:

Power Query Merge Columns ignore null
Power Query Merge Columns ignore null
  • 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.
Power Query Merge Columns ignore null example
Power Query Merge Columns ignore the null example
  • 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.
Example of Power Query Merge Columns ignore null
Example of Power Query Merge Columns ignore null
  • 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")
Power Query Merge Columns with ignore null
Power Query Merge Columns with ignoring null

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.
Power Query Combine Columns with Delimeter
Power Query Combine Columns with Delimeter
  • 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.
Power Query Combine Columns with Delimeter example
Power Query Combine Columns with Delimeter example
  • 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")
Example of Power Query Combine Columns with Delimeter
Example of Power Query Combine Columns with Delimeter

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

See also  How to Add Data to Existing Table in Power BI?

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.
Power Query merge columns percentage
Power Query merge columns percentage
  • 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.
Power Query merge columns percentage example
Power Query merge columns percentage example
  • 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.
Example of Power Query merge columns percentage
Example of Power Query merge columns percentage

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.

Power Query list sum multiple columns
Power Query list sum multiple columns
  • 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:
Power Query sum multiple columns
Power Query Merge Column sum
  • 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:
Power Query sum multiple columns in Power BI
Power Query sum multiple columns in Power BI
  • Under the values, select the columns that you want to add, and click on the Ok button.
Power Query sum multiple columns in the Power BI
Power Query sum multiple columns in the Power BI
  • 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.
Power Query sum multiple columns in the Power BI example
Power Query sum multiple columns in the Power BI example

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:

Power Query Merge Columns from different tables example
Power Query Merge Columns from different tables example

Departments Table:

Power Query Merge Columns from different tables
Power Query Merge Columns from different tables
  • 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.
Power bi add a column from multiple tables
Power bi add a column 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.
Example of Power Query Merge Columns from different tables
Example of Power Query Merge Columns from different tables
  • Now you can expand the table (Employees table) and select the column you want to merge. Click on Ok.
Power Query Merge Columns from two different tables example
Power Query Merge Columns from two different tables example
  • Now you can see the column has been merged from two different tables in the below screenshot.
Example of Power Query Merge Columns from two different tables
Example of Power Query Merge Columns from two different tables

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

See also  Power BI Switch Multiple Conditions

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.

Power Query merge columns with line break
Power Query merge columns with a line break
  • 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.
Power Query merge columns with line break example
Power Query merge columns with line break example

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.
Power Query merge columns remove duplicate
Power Query merge columns remove duplicate
  • 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.
Power Query merge columns remove duplicate example
Power Query merge columns remove duplicate example
  • 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")
Example of Power Query merge columns remove duplicate
Example of Power Query merge columns remove duplicate
  • 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.
Example of the Power Query merge columns remove duplicate
Example of the Power Query merge columns remove duplicate
  • 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")
Power query merge columns if
Power query merges columns if
  • 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.
Power query merge columns if example
Power query merge columns if example
  • 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")
Example of Power query merge columns if
Example of Power query merge columns if

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

Also, you may like the below Power BI tutorials:

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
>