Excel Power Query: Compare Worksheets Using Multiple Matching Columns
Question details
The user needs to reconcile two Excel datasets when neither contains a unique identifier.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Match records by email address, order date, and billing ZIP code even when the source files use different date or ZIP code formats.
- Observed behavior
- The datasets cannot be reliably compared with a single key, so records may appear unmatched until the matching columns are standardized.
Confirm that both worksheets have clear header rows and convert each dataset to an Excel table with Ctrl+T. Check whether ZIP codes contain leading zeros and decide which date and ZIP formats should be used consistently before loading the tables into Power Query.
Merge Excel tables in Power Query with standardized matching columns
Use this method when two worksheets must be compared by email, order date, and billing ZIP code instead of a unique ID.
Power Query compares values, not how they are visually displayed. Convert both date columns to the Date type and both ZIP code columns to trimmed text with the same formatting before merging. Select the matching columns in the same order in both queries.
In Excel, select the first table and choose Data > From Table/Range. In Power Query Editor, select Home > Close & Load To > Only Create Connection. Repeat Data > From Table/Range for the second worksheet.
In each query, select the email column and use Transform > Format > Trim, then use Transform > Format > Lowercase if capitalization should not matter. Select the order date column and choose Transform > Data Type > Date; if the text uses a regional format, choose Transform > Data Type > Using Locale. Select the billing ZIP column, choose Transform > Data Type > Text, and use Transform > Format > Trim. Add leading zeros with Transform > Format > Pad if both datasets require a fixed ZIP length.
In Power Query Editor, select Home > Merge Queries as New. Choose the first table in the top selector and the second table in the bottom selector. Hold Ctrl while selecting the email, order date, and billing ZIP columns in that order in both tables. Set Join Kind to Left Outer, then select OK.
For a direct unmatched-record result, choose Home > Merge Queries as New again, select the two standardized queries, and set Join Kind to Left Anti. This returns rows from the first query that have no match in the second. Select Home > Close & Load to place the result on a new worksheet.
Repeat Home > Merge Queries as New with the second query as the first table and the first query as the second table. Select the same three matching columns, choose Left Anti, and click OK to return rows present in the second dataset but absent from the first.
Create normalized helper columns before merging
Use helper columns when source values contain inconsistent punctuation, spaces, date text, or ZIP code lengths that prevent exact matches.
Reconcile Excel-Compatible Worksheets in WPS Spreadsheets
WPS Office is a free, lightweight alternative for opening, reviewing, filtering, and manually reconciling spreadsheet data. Its familiar interface and Microsoft Office compatibility make migration simple, although Microsoft Power Query-specific automation should remain in Excel.
Frequently Asked Questions
Can Power Query merge two worksheets without a unique ID?
Yes. In Power Query, use Home > Merge Queries as New and select multiple columns—such as email, order date, and billing ZIP—as the matching keys. A row matches only when all selected values match.
Which join finds rows in the first worksheet that are missing from the second?
Use a Left Anti join with the first worksheet as the left table and the second worksheet as the right table. The result contains only rows from the first query with no complete match in the second.
How should different date formats be handled before merging?
In each Power Query, select the date column and choose Transform > Data Type > Using Locale. Convert both columns to the Date type using the locale that correctly interprets the source text, rather than matching the displayed text directly.
Why do ZIP codes fail to match even when they look identical?
ZIP codes may contain leading zeros, spaces, or different data types. Convert both columns to Text, trim spaces with Transform > Format > Trim, and pad values to the same length before selecting them as merge keys.
