Microsoft Excel · Power Query bounded introductory track · Intermediate
Merge related tables with Power Query
Merge orders with a product lookup in Power Query and detect unmatched keys and duplicate-match expansion.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Choose Home > Merge Queries as New, select matching key columns with the same data type, and explicitly choose a join kind. Expand only the lookup fields you need, then reconcile both row count and amounts. A successful merge dialog does not prove that the lookup is unique.
Create two local sources
Use English Excel for Microsoft 365 desktop on Windows and Power Query Editor. These steps are not a Mac/web connector-parity claim. Use a practice workbook with no external accounts or credentials.
On an Orders sheet, enter this block at A1:C4 and convert it to a table named Orders:
Order ProductID Amount
O1 P1 10
O2 P2 20
O3 P9 30
On a Products sheet, enter this block at A1:B3 and create a table named Products:
ProductID Category
P1 Office
P2 Kitchen
For each table, select a cell and choose Data > From Table/Range to create its query. Set ProductID to Text in both queries, Order and Category to Text, and Amount to Whole Number. Load or retain both queries so they are available in the query list. The original Orders data has three rows and totals 60.
Merge without silently dropping an order
- Open the Orders query in Power Query Editor.
- Choose Home > the arrow beside Merge Queries > Merge Queries as New. This preserves the original query while creating the combined result.
- Select Orders as the primary table and Products as the related table.
- Select the ProductID header in each preview. The corresponding columns must have the same data type.
- Explicitly choose Left outer from Join Kind, keeping every Orders row and bringing in matching Products rows. Do not rely on whichever join kind happens to be preselected.
- Confirm. In the new structured table column, select the expand icon and choose Category. You do not need a second copy of ProductID.
The result should contain O1/Office/10, O2/Kitchen/20, and O3/null/30. The unmatched P9 is intentional. A null category tells you the lookup lacks that key; it does not justify deleting the order. An inner join would omit O3 and reduce the amount total to 30, answering a different question.
Validate matching and uniqueness
Check three output rows and Amount total 60 before loading. A preview match count is a useful diagnostic, not a full audit of all source rows. Inspect whitespace and types when a key that looks identical fails to match. Do not enable fuzzy matching for controlled product identifiers merely to hide a data-quality problem.
Choose Home > Close & Load to place the merged query on a separate result sheet, and save the main practice workbook. Now append P9/Outdoor to the Products source table. Select the loaded merged table and choose Query > Refresh > Refresh. O3 should acquire Outdoor while row count and Amount total remain three and 60. This is the expected changed-input result: enrichment without adding transactions.
Save the updated main workbook with P9 included. Then save a separate duplicate-key test copy, leaving the main workbook intact. In that copy, add a second P2 row with Category Kitchen-extra, then select the loaded merged result and choose Query > Refresh > Refresh. Expanding matches now produces two O2 rows. The output grows to four rows and the duplicated Amount makes its total 80. Remove that duplicate from the source and refresh to restore three rows and 60. This illustrates why a lookup’s one-row-per-key rule matters even when the merge itself reports no error.
Save and handle problems
Close the duplicate-key test copy and reopen the saved enriched main workbook. Refresh its already-loaded result and verify O1/Office/10, O2/Kitchen/20, and O3/Outdoor/30: three rows totaling 60. Do not correct categories directly in query output, since refresh replaces derived results.
A merge combines columns through matching keys; append stacks rows and is not a substitute. If a privacy boundary blocks a real multi-source query, use privacy-safe Power Query troubleshooting rather than switching off protections.