Types of Merges in Power Query (Power BI)

9 March 2023
Eugene Lebedev

Eugene Lebedev is a Power BI consultant by background. Before founding Vidi Corp in 2021 he created Power BI reporting for Autodesk. His Power Bi reports were used by VPs of Finance and C-suite of Autodesk. As part of Vidi Corp, Eugene created dashboards for Google, Teleperformance, Delta Airlines and 200+ clients worldwide.

Intro

In this article let’s speak about merge queries in Power BI. Understanding merge types is very important when merging two tables in Power BI. Depending on the merge type that you select, the output will be different. There are six types of joins available in Power BI:

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join
  4. Inner Join
  5. Left Anti Join
  6. Right Anti Join

merge queries power bi

The image above is often used to visualise what happens with tables during joins in Power BI. Before we discuss each join in detail, let’s discuss the terminology that is used in the join names.

To see how you can optimise your reporting with Power Bi, make an enquiry here. 

Left and Right

left right joins Power BI

As you see, there are 2 circles in every type of merge representing 2 tables. Because of this diagram, we speak of merge queries in Power BI as an operation between a Left and a Right table. This is how we are going to refer to our tables later on in this blog.

Left Join means that the Left table is more dominant during the merge e.g. after the join we will get all the records from the Left table and only some from the Right table.

Right join is the opposite. After the join we will get all the records from the Right table and only some from the Left table.

You might wonder what this means in terms of the Power Query interface. The merge menu in Power Query always shows you the Left table as the top table and the right table as the bottom table. In the screenshot below, the employee table is the Left table and the Sales table is the Right table

See also  From Data to Insights: The Role of a Power BI Consultant in UK SMB

employee and sales tables merge power bi

Inner and Outer

inner outter joins Power BI

Inner join refers to the inner section between the 2 circles in the Venn diagram that you see above. This join looks for rows that have matching values in the 2 tables and only returns the results when there is a match in values. If there are rows for which the values don’t match, those rows won’t be included.

Outer join refers to the outer section of the circles. Unlike inner join, it does not filter out the rows that have no matching values. It keeps all the rows and simply returns null values for the rows that have no match.

Anti

anti join Power BI

Anti join is the opposite of the regular join. Instead of returning the rows that have matching values between the 2 tables, it returns only the rows that have no match. Now that we are familiar with the terminology let’s dive deeper into each join type.

Left Outer Join – Power BI

The comment Power BI gives for Left Outer Join is “all from first and matching from second”.

This means it will return all the available rows from the Left table and matching rows from the Right table. This is the default type of join in Power query. If you are an Excel user, this functionality would be familiar to you since this is exactly what happens during a VLOOKUP.

Let’s use an example to understand this better. Assume we have two tables in Power BI:

We can join the 2 using an EmpID since this is a common column in both tables. When we select the 2 columns, Power BI will evaluate the number of matching rows. In this case 78 rows in the Sales table have an EmpID which can also be found in the Employee table.

left outer join Power BI step 1

After we click ok, we can get a new column from which we can expand other columns.

left outer join Power BI step 2

left outer join Power BI step 3

Select Columns from the employee table. Here we will select EmpName, EmpDesignation and EmpCountry. Those will now appear in the Sales dataset.

left outer join done

Now we have EmpName, EmpDesignation and EmpCountry in the Sales table. Since 78 out of 100 rows have a matching value, we get some values for them in the columns that we expanded.  The 22 rows that have no matching values are still kept in the resulting table but they have null values in the columns that we expanded.

See also  How to Consolidate QuickBooks Online from Multiple Accounts

Right Outer Join – Power BI

Power BI explains Right outer Join as “all from second, matching from first”.

This is exactly the opposite of Left Outer Join where it returns all the rows from the Right table and only matching rows from the Left table.

Let’s try to use this merge type on the same 2 tables. Merge Sales table (Left) to the Employee table (Right) on based on EmpId using Right outer Join:

Right Outer JoinNotice how Power BI is now saying 78 rows out of 98 rather than 78 out of 100 as before. This is because it is now taking the Employee table (Right) as our main table. When we expand the rows, we get the following results:

Right Outer Join expanded row

First of all, the number of rows has increased to 98 since there are 98 rows in the Right table. Secondly, there are no blanks for the Right table but there are some blanks for the unmatched values in the Left table.

Full Outer Join – Power BI

You can think of a full outer as a combination of Right Outer and Left Outer joins. Power BI explains Full Outer Join as “all rows from both”.

This join will return all the rows from both tables. It includes matching and non-matching rows. This means that the number of rows we are going to get after the join will increase.

As for the null values, we will get all the null values that were the output of a Right Outer join and the Left Outer join.

Merge Sales table (Left) to the Employee table (Right) on based on EmpId using full outer join as follows:

Full Outer Join Power BIAs per the above screenshot it will display 78 of 100 rows from the Left table plus 78 of 98 rows from the Right table.

Full Outer Join left and right tables

There are null values which are non-matching rows as the full outer join displays everything.

Inner Join – Power BI

Unlike Outer join that returns all the rows, inner join automatically filters out the nulls. Imagine if you did a Full Outer Join and then filtered out all the resulting null values from all the columns. This is exactly the output you are going to get from the inner join.

Power BI explains Full Inner Join as “only matching rows ”.

This join is the opposite of outer joins and will return only matching rows between both tables. There will not be any null value record here because it returns matching rows only.

See also  How to Design a Professional-Looking Power BI Dashboard

Merge Sales table (Left) to the Employee table (Right) on based on EmpId using Inner join as follows:

Inner Join Power BI merge

Inner Join Power BI merge 2

As you see, no record with null values can be found in the final table.

Left Anti Join – Power BI

Power BI explains Left Anti Join as “Rows only in first”.

This join will return all the rows from the Left table which are not matching in the right table.

Merge Sales table (Left) to the Employee table (Right) on based on EmpId using left anti join as follows:

Left Anti Join Power BI

As per the above screenshot 78 of 100 rows match from the sales table so the remaining 22 rows will be returned by this join.

Left Anti Join remaining 22 rows

Right Anti Join – Power BI

Power BI explains Right Anti Join as “Rows only in second”.

This join will return all the rows from the Right table which are not match in the Left table.

Merge Sales table (Left) to the Employee table (Right) based on EmpId using the full outer join as follows:

Right Anti Join Power BI

As per the above screenshot 78 of 98 rows match from the sales table so the remaining 20 rows will be returned by this join.

Right Anti Join remaining 20 rows

Bonus: Fuzzy Merge – Power BI

In addition to trying to match the values exactly using regular merge, there is an option to match those values if they are 50%, 70% or 90% similar to each other. Select the check box under the merge table dialog box to use fuzzy merge options.

Fuzzy Merge how to step 1

Fuzzy Merge how to step 2

We can configure other operations while using fuzzy matching like Similarity Threshold, Ignore Case, Match by combining text part and transformation table:

Fuzzy Merge how to step 3

Fuzzy Merge how to step 4

Let’s understand with an example. We have 2 tables as follows:

City Table

Fuzzy Merge city table

Population Table:

Fuzzy Merge population table

Let’s merge both the table using normal merge and using fuzzy matching options merge:

Normal merge Output:

Normal Output Power BI merge

Fuzzy Matching Output:

Fuzzy Matching Output Power BI merge

In the normal merge Power BI matched 2 records only and ignored Paris and New York as they are spelled as Pari and New york in the right table whereas Fuzzy matching did not ignore these records and returned 4 matching records.

Conclusion

I hope that you found this blog interesting and informative! Have fun merging the datasets!

Microsoft Power Platform

Everything you Need to Know

Of the endless possible ways to try and maximise the value of your data, only one is the very best. We’ll show you exactly what it looks like.

To discuss your project and the many ways we can help bring your data to life please contact:

Call

+44 7846 623693

eugene.lebedev@vidi-corp.com

Or complete the form below