Power BI combine columns from two tables

In this power bi tutorial, we will see about the power bi combine columns from two tables. And also we will discuss the below points:

Different ways to combine columns from two tables or queries
Power bi combine columns from two tables
Power bi combine multiple columns into one
Power bi custom column merge two columns
Power bi add a column from multiple tables
Power bi combine different column from DAX
Power bi union two columns

Table of Contents

Power bi combine column from two tables

In Power bi combining columns means connecting two or more tables or data sources, shaping (means transforming the data) them as needed, then consolidating them into a userful query.

Different ways to combine columns from two tables or queries

There are two primary ways power bi combine columns from two table or queries, these are

Merging: When you have one or more columns in power bi that you would like to add to an another query or table, then we merge the table.
Appending: When you have additional rows of data in power bi, that you would like to add to an existing query, then we append the query.

Power bi combine columns from two tables

Here we will see how to implement power bi combines columns from two tables. I have loaded two tables i.e. Orders table and the Return table in the power bi desktop.

Open Power bi desktop
Load the data using get data.
Then click on transform data from the ribbon in power bi desktop, it will open power query editor, where we will combine columns from two tables.

How power bi combine columns from two tables

Now you can shape your data( transforming the data) if you want.
To combine the columns of the two tables, we will use the Merge Queries feature.
Click on the Merge Queries, in the Home tab, to add the return status column(Return table) to the Order table.

power bi combine columns from two tables

Then the merge dialog box will appear, prompts you to select which table you want to merge to the selected table and the matching columns use for the merge in power bi desktop.
Select a table which you want to merge, then select the Column from both the table.
When you select the correct matching columns, the OK is enabled, then click on Ok.

Once you click on OK, you can see Power bi combine columns from two tables.
Then Expand the column and select the column you want to add, here I will add only return status.

Microsoft power bi combine columns from two tables — microsoft power bi combine columns from two tables

Now you can see the Return status column from return table get added to order table in power bi desktop.

Read: Microsoft Power bi report vs dashboard

Power bi combine multiple columns into one

Here we will see power bi combine multiple columns into one using power query editor.

By using power query editor we can merge to or more columns in your query or table.
In power bi, we can merge columns to replace them with one merged column or power bi create a new merged column alongside the column that are merged.
We can only merge columns of a text data type.

Let’s combine two columns i.e. customer id and customer name from the order table into one by using the merge column feature in Power Query.

In Power bi desktop, open power query editor to combine multiple columns into one.
Here I have taken customer id and customer name you can see in the below image.

Power bi combine multiple columns into one

Make sure the column’s data type must be text.
If you want to change the data type of any column, then in power query editor go to Transform tab -> Any column section -> change the data type.

Once your data type is changed to text you can merge the two columns.
First, select the column you want to merge. To Select the column press ctrl and select the columns.
Go to transform tab, text column section in ribbon select Merge column.

Microsoft Power bi combine multiple columns into one — Power bi combine multiple columns into one

Now merge column window will open, you can choose a separator to insert between each merged column
Next under the new column name, you can change the name of the column. Click on Ok

Now you can see about Power bi combine multiple columns into one.

Read: How to Embed Power BI Report in SharePoint Online

Power bi custom column merge two columns

Here we will see power bi custom columns merge two columns.

We can insert a custom column into the Query table, and then we will use the custom column to effectively merge two or more columns.
We will merge the customer id and customer name column in the Power Query editor.

Lets see power bi custom column merge two column using power Query editor.

Open power query editor in power bi desktop, by clicking on Transform data present in the ribbon.
Now make sure that the custom columns you want to merge have the text data type.
If custom columns don’t have data type text, to change the data type, select transform data -> Change data type -> text.

Power bi custom column merge two columns

Once you change the data type, go to Add column tab in the power query editor.
Then select the custom column from the ribbon.

Microsoft Power bi custom column merge two columns — Power bi custom column merge two columns

A custom column dialog box will open, change the name of the column to Merge.
Select the column you want to merge from the Available column list, then click on Insert.
After the First column enter the ampersand ‘&’( it is used to combine the values in the power query).
Then enter the double quote to add the Space.
Enter another Ampersand ‘&’ after the space character. Insert the one more column you want to merge. Then click on Ok

Now You can see the Power bi custom column merge two columns using power query

Read: How to create Power BI report from SharePoint list + Excel

Power bi add a column from multiple tables

Here we will see how to adds a column from multiple tables using power query in Power BI. And I will use the two tables order table and return the table to add a column.

In power bi desktop load the data by using Get data
Then click on Transform in the Navigator dialog box or you can click on it from the ribbon of power bi home tab to open power query editor.

Power bi add a column from multiple tables

In the power query editor, go to the home tab.
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 OK.
Ensure that both the column you match from two different tables has the same number of rows and has some common columns.

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

Now you can see the Merged tables in the below screenshot.

Note
If you want to add any other table to the newly create merge table, then use the feature of merge, And make sure that the new Merge table and added table, have a common column you need to match and the same number of rows.

Read: Power BI Pie Chart

Power bi combine different column from DAX

Here we will see how to combines different columns using DAX using Power BI. I will combine two-column that is Customer ID and Customer Name using DAX from the order table.

Open your power bi desktop. Load the Data from the Get data.
Go to the Data view tab. Click on New table from the ribbon.

Power bi combine different column from DAX

Then we will write the DAX function to combine the two different columns by using Concatenate() in power bi desktop.
The Dax function is Combine = CONCATENATE(Orders[Customer ID],Orders[Customer Name]).

Now you can see the power bi combine different column using DAX.

Microsoft Power bi combine different column from DAX — Power bi combine different column from DAX

Read: Power bi gauge chart

Power bi union two columns

Here we will see how to union two columns using DAX in Power BI. I am using two tables i.e. Product 1 and Product 2.

In the below screen shot you can see the Product 1 table

Product 2 table

Let’s see power bi union two columns.

Open power bi desktop
Load the data using Get data.
Go to Data view, click on New table

Then we will write the Dax formula using UNION(), which combine both the column of two tables.
The Dax formula is: Union table = UNION(Product1,Product2).

Power bi union two columns using Dax

Now you can see the power bi union two columns using DAX.

Rules to be followed while using UNION():

Both the tables having the same number of columns in power bi.
By position, columns are combined in their respective tables.
In the return table, the column name should match the column name table1_expression.
Duplicate rows are retained in the table in power bi
When columns data types differ, the resulting column data type is determined based on the rules of data type coercion.
From the related tables, the return table will not contain columns in power bi.
The union function is not supported for direct query mode and it is supported in calculated columns and row-level security in power bi.

You may like the following Power BI tutorials:

In this power bi tutorial, we learned power bi combine columns from two tables. And also we discuss the below points:

Different ways to combine columns from two tables or queries
Power bi combine columns from two tables
Power bi combine multiple columns into one
Power bi custom column merge two columns
Power bi add a column from multiple tables
Power bi combine different column from DAX
Power bi union two columns

Bijay Kumar

After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.

Great content! Keep up the good work!

Open power bi desktopLoad the data using Get data.Go to Data view, click on New tableThe information above gives you much easy access for data. Power BI helps to get easy visualization for you.

Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights.