Forum Discussion
Power Query: compare two tables, return all differences in each of multiple columns
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.
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.