Forum Discussion
Matching between two tables for field deviations
Hi. I have the following challenges.
I use two tables (Table A and Table B)
In both tables there is a unique key. I connect the two tables. Now I want to match different fields of table A with B per key.
Example:
Table A - key 1 - field 1 = Car
Table B - Key 1 - Field 1 = Car
Table A - Key 2 - Field 1 = Bear
Table B - Key 2 - Field 1 = Cat
I want to set a filter that will only give me the variations of field 1.
In my example I would identify key 2 with the deviation "Bear" not equal to "Cat".
Combine the Tables: You need to merge the two tables based on the unique key and compare the fields. You can do this in Power Query Editor.
- Go to "Home" tab -> "Edit Queries" -> "Merge Queries" -> "Merge Queries as New".
- Select "Table A" and "Table B", then select the unique key field from both tables and use an "Inner" join.
- Expand the new table column and select the fields you want to compare.
Add a Custom Column: Still in Power Query Editor, you can add a custom column that will identify mismatches between fields.
Go to "Add Column" tab -> "Custom Column".
Use a formula similar to this:
if [Table A - Field 1] <> [Table B - Field 1] then "Mismatch" else "Match"
Filter for Mismatches: Now, you can filter this combined table to only include the mismatches.
- In the newly created custom column, you can filter it to only show "Mismatch".
Apply & Close: Click on "Close & Apply" to apply these changes to your Power BI model.
Visualize the Data: You can now create a table visualization or any other visualization to show the mismatches based on the new table.
2 Replies
Combine the Tables: You need to merge the two tables based on the unique key and compare the fields. You can do this in Power Query Editor.
- Go to "Home" tab -> "Edit Queries" -> "Merge Queries" -> "Merge Queries as New".
- Select "Table A" and "Table B", then select the unique key field from both tables and use an "Inner" join.
- Expand the new table column and select the fields you want to compare.
Add a Custom Column: Still in Power Query Editor, you can add a custom column that will identify mismatches between fields.
Go to "Add Column" tab -> "Custom Column".
Use a formula similar to this:
if [Table A - Field 1] <> [Table B - Field 1] then "Mismatch" else "Match"
Filter for Mismatches: Now, you can filter this combined table to only include the mismatches.
- In the newly created custom column, you can filter it to only show "Mismatch".
Apply & Close: Click on "Close & Apply" to apply these changes to your Power BI model.
Visualize the Data: You can now create a table visualization or any other visualization to show the mismatches based on the new table.
Genius. Thank you.
