Finding differences between tables

[DaneM123]

I've recently inherited a Power BI job and am trying to document the various tables etc. within it. I have two tables, each containing roughly 3.5 million rows. As far as I can see they are the same but there must obviously be some differences as the row counts are different. Is there a quick way to compare two tables and pull out the rows that appear in one but not the other? The only way I can think of at the moment is to concatenate a few columns into a unique key and do an outer join, but I'd prefer not to be adding columns if I don't need to as I don't want to mess around with the original design too much.

Thanks.

[daniel_baciu]

Hi @v-ljerr-msft,

What if the content of the tables changed, meaning Table 1 suffered some value changes for an item that appears in both tables (i.e. assuming 1st line CMC ABT 1/1/2016 is not 50 anymore but 57), can you also please update the pbix addressing this query and showing these changes in the Difference table? it should look like below (having the change in the first line highlighted like the +7 difference):

Many many thanks!

[v-ljerr-msft · Accepted solution]

Hi @DaneM123,

Based on my test, you should be able to follow steps below to compare two tables and pull out the rows that appear in one but not the other.

1. Use the formula below to create a measure(you will need to replace the bold with your real table names and column names).

Is Contained in Table 2 =
IF (
    CONTAINS (
        Table2,
        Table2[Code], MAX ( Table1[Code] ),
        Table2[Code2], MAX ( Table1[Code2] ),
        Table2[Date], MAX ( Table1[Date] )
    ),
    1,
    0
)

2. Show the related columns from Table1 with which can be concatenated into a unique key on the Table visual.

3. Use the the measure to apply a visual level filter([Is Contained in Table 2] is 0) on the Table visual to show only the rows that appear in Table1 but not the Table2.

Here is the sample pbix file for your reference. 

Regards

[DaneM123]

Many thanks for the reply, I've tried a test version this morning and that seems to work so I'll try it on the big tables today.