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

Power BI Merge Two Tables
Power BI Merge Two Tables

Cars Table

Power BI Merge Two Tables example
Power BI Merge Two Tables example
  • 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.
Merge Two Tables in Power BI
Merge Two Tables in Power BI
  • 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:
Power Query Merge two tables
Power Query Merge two tables

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.

Power Query Merge two tables example
Power Query Merge two tables example

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.

Example of Power Query Merge two tables
Example of Power Query Merge two tables

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"})
Power BI Merge Two Tables together
Power BI Merge Two Tables together

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

See also  Power bi DAX functions with examples

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

Power BI Merge Two Tables without Duplicates example
Power BI Merge Two Tables without Duplicates example

Cars Table

Power BI Merge Two Tables without Duplicates
Power BI Merge Two Tables without Duplicates
  • 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.
Power Query Merge two tables example
Power Query Merge two tables example

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.
Example of Power BI Merge Two Tables without Duplicates
Example of Power BI Merge Two Tables without Duplicates

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.

Merge Two Tables without Duplicates in Power BI
Merge Two Tables without Duplicates in Power BI

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"})
Merge Two Tables without Duplicates in the Power BI
Merge Two Tables without Duplicates in the Power BI

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

See also  How to Convert Date to Text 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.
Power BI union two tables power query example
Power BI Merge Two Tables based on one column
  • 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:
Example of Power BI Merge Two Tables together
Example of Power BI Merge Two Tables together
  • 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})
Power BI Merge multiple tables at once
Power BI Merge multiple tables at once

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.

See also  Power BI DAX ISBLANK vs ISEMPTY

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:

>