Forum Discussion
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. | | | | |...
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.
Thank you!