cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Finding differences between tables

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Finding differences between tables

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.

 

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Finding differences between tables

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.

 

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

Highlighted
Frequent Visitor

Re: Finding differences between tables

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.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors