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.
Let’s go over to Power Query and I’ll show you how comparing tables is done.
.
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.
I’ll duplicate my query and instead of table 2, we’ll paste table 2B, then press OK. 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.
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, paste in the column names. So, we want to consider the end date and the employee.
***** 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
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
[youtube https://www.youtube.com/watch?v=sWEtboeHJl8&w=784&h=441]
