Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

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. | | | | |...

artemus
4 years ago
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.

Microsoft Employee

4 years ago

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.

Related content