Power BI Merge Two Tables without Duplicates

In this Power BI tutorial, we will learn how to merge two tables in Power Bi and also will see how to merge two tables together in Power Bi.

In addition, we will also cover how to merge two tables without duplicate values and the below headings with examples.

• Power BI Merge multiple tables
• Power BI Merge Two Tables together

Power BI Merge Two Tables

Let us see how we can merge the two tables using the Power Query editor merge queries in Power BI.

In this example, we are going to use the vehicles table data Excel sheet consisting of two sheets called Bikes and Cars as displayed below:

Bikes Table

Cars Table

• Open Power bi desktop and load the source data using the get data option.
• Then click on Home ->transform data from the ribbon on the power bi desktop, it will automatically redirect to the power query editor, where we will merge two tables.

• To merge two tables, we will use the Merge Queries feature option presented in the Power Query editor.
• Then, click Home > Merge Queries (drop down) > Merge Queries As New in the Power Query editor. as shown below:

In the merge query, pop-up window, select the Table names and matching columns to create a merge table and click on the Ok button.

Once you click on the OK button, you can see that the two queries have been merged and combined as a new table.

= Table.NestedJoin(Cars, {"Color"}, Bikes, {"Color"}, "Bikes", JoinKind.LeftOuter)

Then Expand the column and select the column you want to display in the merged table, here I am going to expand the bike names column.

In the below screenshot, you can see that the value has been expanded and displayed as below: Now click on the Home -> close and apply the option to save the changes.

= Table.ExpandTableColumn(Source, "Bikes", {"Bike Model", "Price", "Released Date", "Color"}, {"Bikes.Bike Model", "Bikes.Price", "Bikes.Released Date", "Bikes.Color"})

This is how to merge the two tables using the Power Query editor merge queries in Power BI.

Power BI Merge Two Tables without Duplicates

Here we will discuss how to merge two tables without duplicates in Power BI.

In this example, I have selected the vehicle table data, in the Bike Table and in the Car Table we can see duplicate values or repeated values as highlighted below:

Bikes Table

Cars Table

• Load the source data into the Power Bi desktop using the get data option. And Click on the Home tab then transform data which redirects to the power query editor.
• In the Power Query editor click Home > Merge Queries (drop down) > Merge Queries As New option.
• In the merge query, pop-up window, select the Table names and matching columns to create a merge table and click on the Ok button.

Once you click on the OK button, you can see that the two queries have been merged and combined as a new table.

= Table.NestedJoin(Cars, {"Color"}, Bikes, {"Color"}, "Bikes", JoinKind.LeftOuter)

• Then Expand the column and select the column you want to display in the merged table, here I am going to expand the bike names column.
• The Screenshot below displays the value that has been expanded and displayed as below: You can also see that the duplicate row values are displayed in the merged tables.

To remove the duplicate row values from the table, simply select the column and right click on it and choose the Remove Duplicates option. which automatically removes the duplicate values.

In the below screenshot, you can see that it removes the duplicate values and displays the unique values. Now click on the home -> close and apply the option to save the changes.

= Table.Distinct(#"Expanded Bikes", {"Bikes.Bike Model", "Bikes.Price", "Bikes.Released Date", "Bikes.Color"})

This is how to merge two tables without duplicates in Power BI.

Power BI Merge multiple tables

To Merge Multiple tables, we will use the append queries because, in the Merge queries, we can only merge two tables in Power BI.

In the following heading, you can see how we can append multiple tables using append queries in the Power BI Power Query editor.

Power BI Merge Two Tables together

Here we will see how we can Merge two tables together using the Power Query editor in Power BI.

In this example, we will append the two tables together with all the row data values in Power BI.

• Open the Power Bi desktop and load data using the get data option. And Click on the Home tab then transform data which redirects to the power query editor.
• In the Power Query editor click Home > Append Queries (drop down) > Append Queries As New option.

• In the Append Query Popup window, select the Three or More tables option and choose the select the tables and click add button, we can also view the list of tables to append under the tables to append section and then select the Ok button as below:

• In the below screenshot, you can see that the bikes and cars table is row values displayed as a new table.
• It displays the null values for the non-matching column names. Click on the Close and Apply option to save the changes.

= Table.Combine({Cars, Bikes})

This is how to Merge two tables together using the Power Query editor in Power BI.

In this Power BI tutorial, we have learned how to merge two tables in Power Bi and also will see how to merge two tables together in Power Bi.

In addition, we also cover how to merge two tables without duplicate values in Power BI.

• Power BI Merge multiple tables
• Power BI Merge Two Tables together

You may also like:

• Power BI Union Two Tables
• Power BI Group by Measure
• Convert Text to Number in Power BI
• Power BI Measure If Multiple Conditions