Match Two Excel Data Sets and Align Unmatched Rows with Power Query
Question details
Match two Excel data sets by reference number and show unmatched records alongside blank cells.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Two data sets contain reference numbers and dates in separate column pairs and need to be aligned without manually inserting rows.
- Observed behavior
- The desired output places matching records on the same row and leaves the opposite side blank when a record has no match.
Check that the reference columns use the same data type and consistent formatting in both data sets. Identify duplicate reference numbers, as a merge can produce multiple rows for them.
Use a full outer merge to align both data sets
Use this method to retain matched and unmatched records in a new, refreshable query output.
A full outer merge keeps every reference from both data sets. Unmatched values appear as null in Power Query and as blank cells in the worksheet output.
This creates an aligned output rather than inserting rows into the original ranges. If references are duplicated, expanding the merge can create multiple combinations; establish a unique matching key first if each record should appear only once.
Select each data range in Excel and choose Insert > Table. Confirm that each table has headers, including reference and date columns.
Select a cell in each table and choose Data > From Table/Range. In Power Query Editor, check the reference-column data types, then use Home > Close & Load > Close & Load To and select Only Create Connection for each query.
Choose Data > Get Data > Combine Queries > Merge. Select the first query and its reference column, then select the second query and its reference column. Set Join Kind to Full Outer and select OK.
In the merged query, select the expand icon on the new table column. Choose the second data set's reference and date columns, then select OK.
Use the column filter arrows to sort the output as needed, then choose Home > Close & Load to place the aligned results in a worksheet.
Work with Excel Data Sets in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format support, making it easy to move existing workbooks into WPS Spreadsheets. The Power Query merge described above requires Microsoft Excel.

Frequently Asked Questions
Why should I choose a full outer join?
In the Merge dialog, Full Outer retains records from both queries, including those without a match. Expanding the second query then shows unmatched records with blank cells on the opposite side.
What happens if a reference number appears more than once?
A merge can return multiple combinations when the same reference appears in both queries. Create a unique matching key before merging if each record should be paired only once.
Can I match by both reference number and date?
Yes. In the Merge dialog, select the reference and date columns in both queries in the same order, and make sure each corresponding pair uses the same data type.
How do I edit the query later?
In Excel, choose Data > Queries and Connections, right-click the query, and select Edit to reopen it in Power Query Editor.
Does the merge insert blank rows into my original tables?
No. The merge creates a separate query result. Unmatched records have blank cells on the opposite side of that output; the original tables remain unchanged.




