Power Query Problems

How to Compare Row Combinations in Two Tables Using Power Query

Maira Mehtab Sep 21, 2026 870 views

Question details

The user needs to compare row-by-row data between two identical tables originating from different databases to identify non-matching combinations.

Product: Power Query
Device & OS: not provided
Scenario: Comparing exported data tables to find discrepancies across different databases.
Observed behavior: Requires a reliable method to flag specific row combinations that do not match as either 'Missing' or 'Mismatch'.

Before you start

Ensure both tables have identical data types for the corresponding columns and that there are no hidden trailing spaces, which can cause false mismatches during comparison.

Solution 1Recommended

Create a Concatenated Comparison Key

Merge the values of each row into a single string (a comparison key) to easily evaluate entire row combinations between the two tables.

By concatenating the specific columns you want to compare into a single 'Key' column, you can turn a complex multi-column comparison into a simple one-to-one match.

Load Tables into Power Query

Open your workbook, navigate to the Data tab, and load both database tables into the Power Query Editor using 'From Table/Range'.

Add a Custom Concatenation Column

In both queries, go to 'Add Column' > 'Custom Column'. Create a combination key by concatenating the fields (e.g., using the formula =[Name] & "|" & Text.From([Number]) & "|" & [Class]).

Merge the Two Queries

Select your primary query, go to 'Home' > 'Merge Queries'. Select the second table, highlight the newly created Custom Key columns in both tables, and choose 'Full Outer Join' to ensure you see all records from both sides.

Identify Mismatches with a Conditional Column

Expand the merged table. Go to 'Add Column' > 'Conditional Column'. Set rules: If Table1 Key is null, output 'Missing'. If Table1 Key does not equal Table2 Key, output 'Mismatch'.

Handling Null Values in Concatenation: If any of your columns contain null values, standard concatenation will result in a null key. Ensure you replace nulls with an empty string or use a function to handle nulls before merging.

Solution 2

Use Power Query Set Operations (Table.RemoveMatchingRows)

Use advanced M code functions to directly subtract rows of one table from another to find exactly what is missing.

Compare Data Across Tables Easily in WPS Spreadsheet

You can achieve the same row-by-row comparison without complex Power Query scripts by using WPS Spreadsheet's built-in formulas and conditional formatting.

1. Open Your Tables: Launch WPS Spreadsheet and open the workbook containing your two database tables.
2. Create a Helper Column: Insert a new column next to both tables. Use the CONCATENATE function or the & operator (e.g., =A2&B2&C2) to merge the row values into a single string.
3. Use a Lookup Formula: In your primary table, enter the formula =IF(ISNA(MATCH(D2, Sheet2!D:D, 0)), "Missing", "Match") to compare the merged strings against the second table.
4. Filter the Results: Apply a standard Data Filter to the column to instantly view and isolate the rows labeled 'Missing' or 'Mismatch'.

Free, lightweight, and incredibly fast for processing large datasetsFully compatible with Microsoft Excel (.xlsx) formulas and formattingBuilt-in Highlight Duplicates and Data Validation tools simplify comparisons

microsoft office alternative - wps office

Frequently Asked Questions

Why do perfectly matching rows show up as a mismatch in Power Query?

This is almost always caused by invisible characters, trailing spaces, or mismatched data types (e.g., one table stores a number as Text, the other as a Whole Number). Use the Text.Trim and Text.Clean functions on your columns before comparing.

Can I compare tables if the column names are different?

Yes. When using the Merge Queries feature, you manually click and select which columns to join on. The column headers do not need to be identical as long as the underlying data is structurally the same.

How do I handle case sensitivity during the comparison?

Power Query is case-sensitive by default ('Apple' and 'apple' will be flagged as a mismatch). To fix this, you can wrap your text columns in Text.Upper() or Text.Lower() to standardize the casing before comparing.

Excel & Data Problems