Comparing Tables In Power Query | Power BI Tutorial

Comparing Tables In Power Query | Power BI Tutorial

No comments

Comparing tables is a very common task in Excel and can get tedious if you do it manually all throughout. In this tutorial, I’ll show how you can easily compare two tables in Power Query to isolate the items that do not appear on both tables and report the differences. You can watch the full video of this tutorial at the bottom of this blog.

So, we’re going to compare two tables to isolate, and then report the differences. Looking at the depicted scenario, when comparing table 2 against table 1, the results should be these three highlighted records.

comparing tables

Let’s go over to Power Query and I’ll show you how comparing tables is done using some Power Query functions.

Comparing Tables Using Table.RemoveMatchingRows

There is a function called Table.RemoveMatchingRows that removes all occurrences of the specified rows in the second argument from the table past as its first argument. Be aware that there’s no mapping capability. So, it is important to make sure that your column headers or field names match before you do the comparison.

Let’s create a new blank query and enter that function Table.RemoveMatchingRows. We want to compare table 2 against table 1. Now we need to pass table 1 as a list of rows, so here we can use Table.ToRecords and pass table 1. Then, let’s press OK.

But will it still work if our table includes columns that don’t match? Let’s say that table 2 contains an additional column with the department. Let’s see what happens.

So, I’ll duplicate my query and instead of table 2, we’ll pass table 2B, then press OK. Now, this doesn’t work because it’s also considering that third column. We need to control what columns are considered when comparing the records. And for that, Table.RemoveMatchingRows function has an optional equation criteria argument.

comparing tables

Let’s give that a list with column names that we want to consider. So, in the formula bar, we’ll add a comma, and as a list, pass in the column names. So, we want to consider the end date and the employee.

comparing tables

Conclusion

If you ever need to report differences between two tables, this could be helpful. I hope you’ve enjoyed this tutorial. You can watch the full video tutorial below for more details. For more related content, check out the links below and our courses around Power Query.

All the best!

Melissa

***** Related Links *****
Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables
Referencing And Duplicating Queries In Power BI
Power Query M Language Tutorial And Mastery

***** Related Course Modules *****
Power Query Series
Power Query M Masterclass Part One
Advanced Data Transformations & Modeling

***** Related Support Forum Posts *****
Comparing Two Table and Appending Mismatches
Field by Field Comparison of Two Tables (Auto Compare Two Queries)
Extended Date Table (Power Query M Function)
For more comparing tables in power query support queries to review see here…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.