Power Query Problems

Merge Two Excel Tables by a Common Field Using Power Query

Adam DavisAdam Davis Nov 18, 2026 870 views

Question details

Combine two Excel tables that share Field B and return one row for each unique Field A, Field B, and Field C combination.

Merge Two Excel Tables by a Common Field Using Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
The tables have a many-to-many relationship through Field B, and every matching combination is needed.
Observed behavior
The desired result contains each distinct combination of Field A, Field B, and Field C once.
Before you start

Confirm that the first table contains Field A and Field B, and the second contains Field B and Field C. Make sure the Field B columns use compatible data types and matching values.

Solution 1Recommended

Merge on Field B and remove duplicate combinations

Use an inner join to return every matching Field A, Field B, and Field C combination, then remove repeated rows.

A many-to-many merge pairs each row in the first table with every matching row in the second table. An inner join excludes Field B values that have no match in the other table.

1
Load the tables

In Excel, select a cell in each table and choose Data > From Table/Range. Create a Power Query for each table.

2
Merge the queries

In Power Query Editor, choose Home > Merge Queries > Merge Queries as New. Select the Field A table first and the Field C table second.

3
Match Field B

Select the Field B column in both tables, choose Inner as the join kind, and click OK.

4
Expand Field C

Click the expand button on the merged column, select Field C, and click OK. Keep only Field A, Field B, and Field C in the result.

5
Remove duplicates and load

Select all three columns, then choose Home > Remove Rows > Remove Duplicates. Select Home > Close & Load to return the result to Excel.

Merge on Field B and remove duplicate combinations
Include unmatched rows: If you also need rows from the first table without a Field B match, choose a Left Outer join instead. Those rows will have a null Field C.
Free Microsoft Office alternative

Work with Excel tables in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open existing Excel files to continue working with table data and move between office suites with ease.

Review and edit spreadsheet tables in a familiar interfaceOpen and save Microsoft Excel XLSX filesUse a free, lightweight office suiteMove existing spreadsheet files to WPS Office without starting over
microsoft office alternative - wps office

Frequently Asked Questions

Why does the merge create more rows than either source table?

For each Field B value, Power Query pairs every matching row in the first table with every matching row in the second. This is expected for a many-to-many relationship.

How do I remove only repeated combinations?

After expanding Field C, select Field A, Field B, and Field C together, then choose Home > Remove Rows > Remove Duplicates in Power Query Editor.

Why are some Field B values not matching?

Check that both Field B columns have compatible data types and consistent values. In Power Query Editor, use the data type icon in each column header and Transform > Format to address differences such as extra spaces.

How can I keep rows that have no match in the second table?

In the Merge dialog, select Left Outer as the join kind. Rows from the first table without a match will remain, with null values in the expanded Field C column.