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. |
| | | | | | | | | | | | | | | | |
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.
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.
5 Replies
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.
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 duplicatedIs this is how it should be?
I got it:
Needed to merge them in both Key and Column columns
🙂
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.
