Finding rows that are in one table, but not the other is one of the most common scenarios happening in any data related applications. You may have customer records coming from two sources, and want to find data rows that exist in one, but not the other. In Power Query, you can use Merge to combine data tables together. Merge can be also used for finding mismatch records. You will learn through this blog post, how in Power Query you can find out which records are missing with Merge, and then report it in Power BI. To learn more about Power BI, read Power BI book from Rookie to Rock Star.

Sample Data Tables

In this sample scenario, I have two customer tables; one customer table coming from the website, and another customer table coming from an application. Here is the customer table from the website:

And the other table that comes from the application:

The customer table from the website has the email address field, plus name and CustomerKey. They table coming from the application has fields such as birthdate, gender, and marital status, plus name and customer key. the link field in this scenario is CustomerKey which exists in both tables.

Merge in Power Query

Previously I have explained about What the Merge is, and what is the difference of that to Append. I also explained in another blog post, about different types of Merge Kind. In this example, you would find a couple of those join kinds useful; Left Anti Join, and Right Anti Join. In Below Power Query example, I read the data from both tables, and I merge them together;

Left Anti Join: Records Only in the First Table

When I merge these two queries, I select Left Anti Join, that gives me rows that exist only in the first table (customer table from application):

This will give you only rows that exist in the first table “Customer table from application”

Then you can remove the last column in the output because the table value would not have any rows (this are mismatch rows)

Right Anti Join: Records Only in the Second Table

The same Approach can be used for rows that exist only in the second table, using the Right Anti Join

But right Anti Join will give you a result which looks a bit weird if you do not expand the table;

You DO NEED to expand the table for the second query to get mismatch rows when you use RIGHT ANIT Join. which is an extra step, but still works fine. You can remove all columns from the first table, and expand the last column;

Left Anti with Changing Order of Tables; Works similar to Right Anti

Or alternatively, you can switch the order of tables at the time of Merge, and use Left Anti join to get the same output;

The output of this is similar to the step we have done before (it gives you the records only in the second table “customers from the website”):

Summary

In summary, this post focused on two of the least common join types in Power Query; Left Anti join, and Right Anti join. These two join types are very useful when you want to find records that exist in one of the tables, but not the other one. All you do need is to select the right order of tables and merge type in the Merge command graphical interface. If you like to know more about other types of joins, read this post.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.