How to Merge Column in Power BI

Do you know that we can merge two text columns in Power BI, it is possible to merge multiple columns in Power BI. If you don’t have any idea about Merging columns in Power BI. Then this Power BI Tutorial will help to merge columns in Power BI.

Additionally, we will cover the below-connected topics:

  1. Power BI Concatenate Function or Concatenate Function in Power BI
  2. How to Merge two columns in Power BI desktop
  3. Merge columns in power bi table visual
  4. Merge columns in power bi DAX
  5. Merge columns in power bi matrix visual
  6. Merge Multiple columns in power bi
  7. Merge columns in the power query editor
  8. Merge text columns in Power BI
  9. Power bi concatenate 3 columns
  10. How to Merge Calculated columns in Power BI

Power BI Concatenate Function

  • The Power BI CONCATENATE function joins two text strings into one text string, which is used to merge columns, The Columns can include text or numbers as data type.

Syntax:

CONCATENATE(<text1>, <text2>) 

Where,

  1. Text1: The first text string is to be joined into a single text string. The string can include text or numbers. You can also use column references.
  2. Text2: The second text string is to be joined into a single text string. The string can include text or numbers. You can also use column references.

Also Read: How to merge columns in Power Query

How to Merge two columns in Power BI desktop

Let us see how we can Merge two columns in the Power BI desktop.

In this complete post, I am going to use the below-mentioned sample Sales_Table data, You can download the sample data from here.

How to Merge two columns in Power BI desktop
How to Merge two columns in Power BI desktop
  • Open the Power BI desktop, and load the data into it using the get data option.
  • To Merge two columns in the Power BI desktop, From the ribbon select the New Column option, and apply the below-mentioned formula.
Merged Columns= CONCATENATE(Sales_Table[Product Name]&"  ",Sales_Table[Sales])

Where,

  1. Merged Columns = New calculated column,
  2. Concatenate = Function to merge columns, The Columns can include text or numbers as data type.
  3. Sales_Table = Table Name
  4. Product Name and Sales = Column names.
  • If we want additional space, commas, or semi-colons then we can use them within the double-quoted string.
  • The Merged column will always be in the text data type. In the below screenshot you can see that I have merged the product name and sales column.
Merge two columns in Power BI desktop example
concatenate two columns with space in Power bi

This is how to Merge or concatenate two columns in the Power BI desktop.

Also read: Power BI Slicer Multiple Columns

Merge columns in power bi table visual

Let us see how we can Merge columns in Power bi and display the merged data in the table visual in Power Bi.

  • Load the data into it using the get data option. To Merge two columns in the Power BI desktop, From the ribbon select the New Column option, and apply the below-mentioned DAX formula.
Merged Columns= CONCATENATE(Sales_Table[Product Name]&"  ",Sales_Table[Sales])

Where,

  1. Merged Columns = New calculated column,
  2. Concatenate = Function to merge columns, The Columns can include text or numbers as data type.
  3. Sales_Table = Table Name
  4. Product Name and Sales = Column names.
  • The Merged column will always be in the text data type. In the below screenshot you can see that I have merged the product name and sales column.
Merge two columns in Power BI desktop example
Merge columns in power bi table visual
  • Now select the Table visual under the visualization, drag and drop the fields or columns from the fields pane in Power BI as shown below:
Merge columns in power bi table visual
Merge columns in power bi table visual
  • In the below screenshot, we can see that column 1 data and column 2 data have been merged and displayed in the table visually.
Merge columns in power bi table
Merge columns in power bi table

This is how to Merge columns in Power bi and display the merged data in the table visual in Power BI.

Check out: Power BI Create Table From Another Table

Merge columns in power bi DAX

Let us see how we can Merge columns in Power Bi using the Measure or DAX in Power bi.

  • Load the data into it using the get data option. To Merge two columns in the Power BI desktop, From the ribbon select the New Column option, and apply the below-mentioned DAX formula.
Combine Columns = COMBINEVALUES(", ",Sales_Table[Product Name],Sales_Table[Units Delivered])

Where,

  1. Combine Columns= New calculated column,
  2. COMBINEVALUES = Function to merge columns, The Columns can include text or numbers as data type.
  3. Sales_Table = Table Name
  4. Product Name and Units Delivered = Column names.
  • The Merged column will always be in the text data type. In the below screenshot you can see that I have merged the product name and units delivered column.
Merge columns in power bi DAX
Merge columns in power bi DAX

This is how to Merge or combine columns in Power Bi using the Measure or DAX in Power bi.

Merge columns in power bi matrix visual

Let us see how to Merge columns in Power Bi and display the merged data in the matrix visual in Power Bi,

  • Load the data into it using the get data option. To Merge two columns in the Power BI desktop, From the ribbon select the New Column option, and apply the below-mentioned DAX formula.
Combine Columns = COMBINEVALUES(", ",Sales_Table[Product Name],Sales_Table[Units Delivered])

Where,

  1. Combine Columns= New calculated column,
  2. COMBINEVALUES = Function to merge columns, The Columns can include text or numbers as data type.
  3. Sales_Table = Table Name
  4. Product Name and Units Delivered = Column names.
  • The Merged column will always be in the text data type. In the below screenshot you can see that I have merged the product name and units delivered column.
Merge columns in power bi DAX
Merge columns in power bi matrix visual
  • Now select the Matrix visual under the visualization, drag and drop the fields or columns from the fields pane in Power BI as shown below:
  • In the rows section drag and drop the Product Name and Combine columns and in the values section drag and drop the Units Delivered field. Use the drill down feature so that it groups and displays the data separately as below:
Merge columns in power bi matrix visual
Merge columns in power bi matrix visual
  • To see the Merged values in the Matrix visual, drag and drop the Combine columns in the values section, and you can see it takes the first values and displayed them in the matrix visual as highlighted below:
Merge columns in the power bi matrix example
Merge columns in the power bi matrix example

This is how to Merge columns in Power Bi and display the merged data in the matrix visual in Power Bi.

Read: Power BI Compares Two Columns in Different Tables

How to Concatenate columns in power query

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

  • 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 in the power query editor
  • In the example, I am going to merge the Customer Name and the Customer Location column. In the Power Query editor select the columns that you want to merge as shown below: To Select the columns click on the ctrl and select the columns.
Merge columns in power query editor
Merge columns in the power query editor
  • Go to the transform tab, text column section in the ribbon select the  Merge column option.
Merge columns in power query editor example
Merge columns in power query editor 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 change the name of the column. Click on the Ok button.
Merge columns using power query editor
Merge columns using the power query editor
  • 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.
Merge columns using power query editor example
Merge columns using power query editor example

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

Check: Power BI Sum Group by

Merge text columns in Power BI

Let us see how we can merge text columns in the Power Bi desktop,

  • Load the data into it using the get data option. To Merge two columns in the Power BI desktop, From the ribbon select the New Column option, and apply the below-mentioned DAX formula.
  • In the example, I am going to merge the Customer Name and the Customer Location column.
Merged Columns = CONCATENATE(Sales_Table[Customer Name] &", ",Sales_Table[Customer Location])

Where,

  1. Merged Columns = New calculated column,
  2. Concatenate = Function to merge columns, The Columns can include text or numbers as data type.
  3. Sales_Table = Table Name
  4. Customer Name and Customer Location = Column names.
  • The Merged column will always be in the text data type. In the below screenshot you can see that I have merged the Customer Location and Customer Location.
Merge text columns in Power BI
Merge text columns in Power BI

This is how to merge text columns in the Power Bi desktop.

Concatenate Multiple columns in power bi

Let us see how we can merge multiple columns in the Power Bi desktop,

  • Load the data into it using the get data option. To Merge two or more columns in the Power BI desktop, select the New Column option from the ribbon, and apply the below-mentioned DAX formula.
  • In the example, I am going to merge the Product Name, Customer Name, and the Customer Location column.
Merged Columns = COMBINEVALUES(",",Sales_Table[Product Name],Sales_Table[Customer Name],Sales_Table[Customer Location])

Where,

  1. Merged Columns = New calculated column,
  2. COMBINEVALUES = Function to merge columns, The Columns can include text or numbers as data type.
  3. Sales_Table = Table Name
  4. Product Name, Customer Name, and Customer Location = Column names.
  • The Merged column will always be in the text data type. In the below screenshot you can see that I have merged the Product Name, Customer Location, and Customer Location.
Merge text columns in Power BI example
Merge text columns in Power BI example

This is how to merge multiple columns in the Power Bi desktop.

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

Power bi concatenate 3 columns

Let us see how we can concatenate 3 columns in Power BI,

  • Load the data into it using the get data option. To Concatenate 3 columns in the Power BI desktop, select the New Column option from the ribbon, and apply the below-mentioned DAX formula.
  • In the example, I am going to merge the Customer Name, Product Name, and Customer Location columns.
Concatenate 3 columns = Sales_Table[Product Name] & " - " &Sales_Table[Customer Name] & " - " & Sales_Table[Customer Location]

Where,

  1. Concatenate 3 columns = New calculated column,
  2. COMBINEVALUES = Function to merge columns, The Columns can include text or numbers as data type.
  3. Sales_Table = Table Name
  4. Product Name, Customer Name, and Customer Location = Column names.
  • The Merged column will always be in the text data type. In the below screenshot you can see that I have merged the Product Name, Customer Location, and Customer Location.
Power bi concatenate 3 columns
Power bi concatenate 3 columns

This is how to concatenate 3 columns in Power BI.

How to Merge Calculated columns in Power BI

Let us see how we can merge the calculated columns in Power BI,

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

How to Merge Calculated columns in Power BI
How to Merge Calculated columns in Power BI
  • Load the data into it using the get data option. To Concatenate two calculated columns in the Power BI desktop, select the New Column option from the ribbon, and apply the below-mentioned DAX formula.
  • In the example, I am going to calculate the Remaining QTY and the Needed QTY by subtracting the Total QTY.
Remaining QTY = Table1[Total QTY]-Table1[QTY Completed]

Where,

  1. Remaining QTY = New calculated column,
  2. Table1 = Table Name
  3. Total QTY, and QTY Completed = Column names.
Needed QTY = Table1[Total QTY]-Table1[QTY Needed]

Where,

  1. Needed QTY = New calculated column,
  2. Table1 = Table Name
  3. Total QTY, and QTY Needed = Column names.
Concatenate Calculated Columns = CONCATENATE(Table1[Remaining QTY]& ",",Table1[Needed QTY])

Where,

  1. Concatenate Calculated Columns = New calculated column,
  2. CONCATENATE = Function to merge columns, The Columns can include text or numbers as data type.
  3. Table1 = Table Name
  4. Remaining QTY, and Needed QTY = Calculated Column names.

In the below screenshot you can see that I have merged the two calculated columns in Power BI.

How to Merge Calculated columns in Power BI example
How to Merge Calculated columns in Power BI example

This is how to merge the calculated columns in Power BI.

Also, you may like some below Power BI Tutorials:

So In the Power BI tutorial, we learned how we can merge two text columns in Power BI  And also covered the below-mentioned points:

  • How to Merge two columns in Power BI desktop
  • Merge columns in power bi table visual
  • Merge columns in power bi DAX
  • Merge columns in power bi matrix visual
  • Merge Multiple columns in power bi
  • Merge columns in the power query editor
  • Merge text columns in Power BI
  • Power bi concatenate 3 columns
  • How to Merge Calculated columns in Power BI
>