Matching between two tables for field deviations

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.