Comparing Tables In Power Query | Power BI Tutorial

Comparing Tables In Power Query | Power BI Tutorial

3 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.

.

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.

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, paste in the column names. So, we want to consider the end date and the employee.

comparing tables

***** 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

Enterprise DNA Power BI On-Demand

3 comments on “Comparing Tables In Power Query | Power BI Tutorial”

  1. Hi,
    This is really amazing – Thank you for sharing

    I have used this to compare to tables with many columns. Is there an easy way that Power Query can highlight the cells where the changes are ? Perhaps a colour?

    Best regards,
    Steffen

    1. Hi Steffen,

      Unfortunately the requested functionality is not available inside Power Query.

      What can be done is to use Merge Query to get the comparison data from 2nd table and then create a new Column using If condition to compare with Table 1. The new column can be given values like Match/UnMatch and can be highlighted in report.

Leave a Reply

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