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

[Anonymous]

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 A	| a date	| a 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.  

[jsangerman]

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]

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:

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

Is this is how it should be?

[MasterSonic]

I got it:

Needed to merge them in both Key and Column columns

🙂

[Anonymous]

Thank you!

[artemus · Accepted solution]

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.