Merge Two Excel Tables by a Common Field Using Power Query
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.

- 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.
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.
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.
In Excel, select a cell in each table and choose Data > From Table/Range. Create a Power Query for each table.
In Power Query Editor, choose Home > Merge Queries > Merge Queries as New. Select the Field A table first and the Field C table second.
Select the Field B column in both tables, choose Inner as the join kind, and click OK.
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.
Select all three columns, then choose Home > Remove Rows > Remove Duplicates. Select Home > Close & Load to return the result to Excel.

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.

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.




