Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Power Query: compare two tables, return all differences in each of multiple columns

I have two tables. Both tables have the same columns:

Primary Key | Item | Description | Date A | Date B | Dollar Amount A | Dollar Amount B | Comments | etc. 
 ||||||||

There are a total 38 columns most of which are just text data types.

 

I want to compare these tables and generate a report where the values are different, something like this:

Primary Key | Column | Table 1 value | Table 2 Value 
123| Date Aa datea different date
456| Description| a description| a different description
456| Dollar Amount B| an amount | a different amount
789| Item| name of an item| a different item name

 

In the result, I want one record for each difference.  If the data associated with a Primary Key has differences in ten columns then in the result there will be ten records, one for each difference. If all the data associated a Primary Key is the same in both tables then that Primary Key would not appear in the result.

I know how to do this by building a separate query for each column and ultimately appending the results but I am hoping there is a more direct method of producing this kind of result.  

1 ACCEPTED SOLUTION
artemus
Employee
Employee

For both tables:

1. Select the key column, then choose under transform Unpivot -> other columns which will produce the following script:

= Table.UnpivotOtherColumns(previousStep, {"Primary Key"}, "Attribute", "Value")

2. Rename the Attribute to "Column" and Value to "Table 1 value" and "Table 2 value" for query 1 and 2 respectivly. 

3. Merge (Join) the 2 queries using an inner join (assuming both have the same primary keys, use a full outer join otherwise).

4. Expand the new table column and only choose the Table 2 values.

5. Add a conditional column with Table Value 1 = Table Value 2

6. Filter the new column for false.

7. Remove the newly added column.

View solution in original post

4 REPLIES 4
MasterSonic
Helper IV
Helper IV

This is what I was looking for. 

 

However I have a issue in the end.
I can filter on TRUE/OK to see matching results.

 

But I can not see missmatches? After point 3 it looks like that:

MasterSonic_0-1663801663639.png

 

4. Expand the new table column and only choose the Table 2 values.
it looks like that Key is duplicated

MasterSonic_1-1663802079996.png

 

Is this is how it should be?

I got it:

Needed to merge them in both Key and Column columns

🙂

Anonymous
Not applicable

Thank you!

artemus
Employee
Employee

For both tables:

1. Select the key column, then choose under transform Unpivot -> other columns which will produce the following script:

= Table.UnpivotOtherColumns(previousStep, {"Primary Key"}, "Attribute", "Value")

2. Rename the Attribute to "Column" and Value to "Table 1 value" and "Table 2 value" for query 1 and 2 respectivly. 

3. Merge (Join) the 2 queries using an inner join (assuming both have the same primary keys, use a full outer join otherwise).

4. Expand the new table column and only choose the Table 2 values.

5. Add a conditional column with Table Value 1 = Table Value 2

6. Filter the new column for false.

7. Remove the newly added column.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors