How to compare data in power bi

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.

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…

Photo by Austin Distel on Unsplash

For conducting proper reporting, it’s often required to dynamically compare one or more categories against each other based on the current filter context. The easiest way to…

Hi;

  I'm fairly new to Power BI, so in addition to solving my exact question I wouldn't mind being pointed to examples of similar problems.

What I would like to do is be able to compare select attributes from two different selections next to each other, and then further show the difference.  For example, if I have a data set that was similar to so many of the examples that I have seem (they all seem to use sales data over time) I would like to be able to select from two different sets of filters to compare Units/Revenue/Profit/etc. from one Time Period/Sales Person/Category to another. See rough example of the type of output I'd like below. Each column would be controlled by a set of three slicers.  Thanks in advance for your help!

                      January/Joe/Jeans                September/Sam/Shirts            Difference 

Units                          20                                          30                                  +10

Revenue                     40                                          30                                  -10

Profit                          12                                          12                                  +0

After I get there, I'd like to be able to put together a waterfall chart that would use calculated metrics to walk from Profit to Profit in buckets such as Volume, Revenue/unit, Profit/Revenue, etc.  

I have been increasingly recording videos as my method of sharing tips and techniques for getting more out of Power BI. Some concepts are best documented (traditional blogging) and some are just easier for everyone if it is a video. That is the case again for today.

The whole concept here is to allow a user to pick two things from a list and compare them with each other. For example, compare

• Any year with any other year
• Any product against any other product (maybe even a group of products, using multi select)
• Snapshots (eg different versions of a financial forecast) with any other version
• Etc

What is possible is endless, and the good news is that it is pretty simple to do this in Power BI. Check out my video below to see how I did it.

Final Result

You can see in the image below, the user can select a year from the top slicer and any other year from the bottom slicer, and the visual updates along with the title of the visual clearly communicating what is being displayed.

If you would like to learn more about Power BI from experts like me, Ken Puls, Miguel Escobar, and Reid Havens (all Microsoft MVPs), head over to http://skillwave.training and check out our training course catalogue. Yes, you can learn yourself using YouTube – there is a lot of great free content out there (like my video below), but you will learn better/faster if you take a structured course built by industry professionals.

Video