Power Query Problems

Match Two Excel Data Sets and Align Unmatched Rows with Power Query

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Create tables

Select each data range in Excel and choose Insert > Table. Confirm that each table has headers, including reference and date columns.

2
Load both tables

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.

3
Merge the queries

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.

4
Expand the second data set

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.

5
Sort and load the result

Use the column filter arrows to sort the output as needed, then choose Home > Close & Load to place the aligned results in a worksheet.

Matching on dates too: If a record should match only when both its reference and date agree, select both columns in each query in the same order in the Merge dialog. Ensure the corresponding date columns have the same data type.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLS files.Review reference numbers and dates in a familiar spreadsheet interface.Move existing workbooks into a free, lightweight office suite.
QA img-9

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.