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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
Microsoft Employee
Microsoft 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

5 REPLIES 5
jsangerman
Helper II
Helper II

I've been using Power Query for a long time and was very confused looking at this answer. "How can I ensure both the primary key and the Column column are used for the merge?" In the past, I'd have merged those two columns in each table to create a new column used for the merge.

 

Instead, I just learned that Power Query supports merging using multiple columns. All you have to do is Ctrl-click to select the additional columns in the Merge dialog.

 

This is mind-blowing! Thank you.

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
Microsoft Employee
Microsoft 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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.