KajoData / Career & business
Power Query Merge: keys, missing matches and duplicate rows
I use orders and a product reference to explain join selection, missing matches and repeated amounts caused by duplicate keys.

You merge orders with a product reference table. The names look sensible and most gaps disappear, but the order total suddenly increases. Several matches for one key can produce exactly that effect. Successful execution of Merge does not establish that the resulting table has the meaning you intended.
I start by identifying what one row on the left represents and how many matches it is allowed to find on the right. I choose the join kind afterwards. This guide follows the reasoning through small synthetic tables, including missing keys, duplicate matches and totals after expansion.
Establish the grain of both tables
The first synthetic table contains orders. For simplicity, each order has one line and one product code. Amount represents the full amount of that order in an arbitrary common unit. This assumption makes accidental repetition easy to identify.
| Order | Code | Amount |
|---|---|---|
| Z1 | P1 | 100 |
| Z2 | P2 | 50 |
| Z3 | P9 | 30 |
The reference table is intended to have one current product name per code.
| Code | Product |
|---|---|
| P1 | Monitor |
| P2 | Mouse |
| P3 | Keyboard |
The order total is 180. I also know that P9 is absent from the reference and P3 has no order. Both cases are deliberate. They distinguish an existing order without a recognised product from a product which nobody ordered within this small list.
I do not carry this simplification into a real source without checking it. An order with several lines requires a decision about whether an amount belongs to a line or the whole document. An Amount heading can conceal a different grain in each table.
Choose Merge or Append according to the question
Merge joins tables through matching key values. Append places their contents into a combined sequence of rows. Microsoft’s Append documentation also explains matching columns by header names and nulls for fields absent from one input.
For January and February order lists with compatible meanings, I consider Append. For orders and a product reference, I need Merge. The task is to retrieve a name corresponding to a code, rather than place product reference rows below order rows.
The mnemonic that Append makes a table longer and Merge makes it wider is useful only within limits. After merging and expanding, the number of rows can also increase. I therefore avoid promising that Merge preserves the input row count. Matching multiplicity and the chosen join kind matter.
Before starting, I describe the intended result: retain all three orders, add the product name where the reference recognises the code, and preserve the missing name for P9. That sentence gives the join a concrete purpose and points towards the appropriate direction.
Prepare keys without losing their meaning
The matching columns should have the same data type, as explained in the merge overview. Here Code is text in both tables. For identifiers with meaningful leading zeroes, I avoid conversions that would discard information.
I also inspect whitespace and notation. P1 and a value with an accidental trailing space need an agreed normalisation rule. I do not remove characters indiscriminately: a hyphen distinguishing two valid codes cannot safely disappear just to improve the match rate.
Missing keys deserve separate handling. I do not assume that null matching in Power Query follows SQL NULL semantics. For this identifier-based process, an absent code should not be accepted as a recognised product simply because another record also lacks a code. I establish that requirement before choosing how to handle the missing records.
When a key must be extracted from a longer text field, I inspect the extraction rule first. My guide to Column from examples develops that preparation step. Several correct-looking prefixes do not establish that the complete join key has been preserved.
Begin with Left Outer and check the table positions
In the editor, I choose Merge queries as new, put orders on the left and the reference on the right, select Code in both and choose Left Outer. This keeps left-side rows and brings in right-side matches, as documented in the Left Outer guide.
Left and right describe the selections in the operation dialog, rather than worksheet positions in the workbook. I check them before confirmation. Reversing the tables while retaining the same join kind changes the question being answered.
The merge introduces a column containing nested match tables. Table.NestedJoin describes that mechanism. At this stage, I want to understand what each nested table contains, rather than merely confirm that a new column appeared.
I expand only Product. Initially, I retain a source prefix or give the field an unambiguous name. When both inputs have a Status column, two unexplained status fields can cause confusion later. Selecting only the required output also makes the first inspection easier to follow.
Check Left Outer against the known missing key
With the original unambiguous reference, the expanded result is straightforward. Z1 receives Monitor, Z2 receives Mouse, and Z3 has no product name. All three orders remain and their amounts still total 180 because each order has at most one reference match.
I do not automatically call Z3 an invalid order. The evidence establishes only that P9 found no corresponding code in this reference. The reference might be incomplete or outdated, or the order code might need correction. Those are hypotheses to discuss with the source owner.
I keep Z3 and its original code. Without identifiers, the reader would hear about a missing product name but have no practical way to locate the record. A short exception list with context is more useful than a general warning that missing data exists.
I separately decide whether the missing name should block the report or appear as an unrecognised product. That depends on the analysis. In either case, I want the number and amount of affected orders available during acceptance rather than silently excluded.
Duplicate reference keys can repeat an order
Now deliberately add another P1 reference row named Monitor Pro. Z1 has two matches. After expansion, Z1 appears twice, each time carrying 100. Z2 retains 50 and Z3 retains 30. Summing the visible Amount column now gives 280.
Table.ExpandTableColumn documents expanding nested tables into multiple rows. The merge does not automatically decide which product name is the authoritative one when the source supplies two matching records.
This does not mean Power Query invented a real transaction. The output structure changed, and I can no longer interpret each output row as an independent order. A reference total exposes that change faster than a visual inspection of plausible product names.
I avoid repairing the result by arbitrarily removing duplicates after the merge. First I ask why P1 occurs twice. It could be a reference error or two legitimate versions requiring an additional key or effective period. Those explanations call for different treatments.

Use Inner when only matched records belong in the result
An Inner join retains matching records. With the original reference, it gives Z1 and Z2. Z3 disappears and the remaining amounts total 150. That answers a question about orders with recognised products.
For a complete sales list, silently dropping Z3 would instead be a problem. I choose the join kind from the report definition, rather than from a desire to remove empty product names. A missing reference entry describes a data condition which the reader may need to see.
Inner also does not eliminate the effects of multiple matches. Adding the second P1 can still repeat Z1. I keep two questions separate: whether a record has a match and how many matches it has. Answering the first does not establish the second.
Extract unmatched records with an Anti join
For a list of orders without reference matches, I use Left Anti with orders on the left. Microsoft defines Left Anti as left-side rows without a corresponding right-side match. Here it leaves Z3, code P9 and amount 30.
That is useful material for investigating gaps. The list needs the order identifier, code, source period and the reference used for comparison. Otherwise someone could attempt to explain a historical mismatch using a later version of the product list.
Reversing the inputs asks a different question. Reference on the left and orders on the right can identify products absent from this order list. In the original data, that is P3. It does not establish that the product has never been ordered or should be removed from the catalogue. Those conclusions exceed the evidence from three synthetic orders.
Use Full Outer to reconcile both sides
Full Outer retains records from both inputs. The original example includes matches for P1 and P2, the P9 order and the P3 reference entry without an order. This helps investigate coverage, provided both source keys remain available.
For P3, the order-side fields can be null. If I expand only Product and discard the reference code, the resulting row becomes harder to explain. During reconciliation, I retain left and right codes separately before considering a combined presentation key.
I also identify which fields are measures and what they represent before adding them up. Two source lists may contain amounts at different grains or with different definitions. Full Outer supports comparison of records. It does not establish the correct accounting or aggregation method for every amount found in a wide result.
Select composite keys in the same order
Code alone may be insufficient. Imagine a price reference defined by product and country. P1 in Poland and P1 in Germany are legitimate distinct records. Joining only on P1 creates several matches even when the reference has no erroneous duplicates.
In that situation, I select the corresponding columns in the same order on both sides, as described in the merge overview. The requirement is a Product and Country pair. I avoid immediately concatenating fields into one text without deciding how separators and missing values should work.
Historical data introduces another question: the effective date. Product and country may still fail to identify one price record. That signals a need to design period matching. A simple current-reference example does not automatically solve the historical problem, and Remove duplicates cannot decide which period applies.
The same reasoning applies to a training attendance list
Consider another synthetic task from HR: employee reference E1, E2 and E3, and attendance records for E1, E1 and X9. A row in attendance represents an attendance record rather than a unique employee. The repeated E1 may mean two sessions or an accidental duplicate.
A Left Anti join from attendance to employees finds X9. The evidence says that X9 is absent from this employee reference. It does not establish that the person is an external trainer. That interpretation requires confirmation from the data owner.
Reversing the comparison finds E2 and E3 without attendance in the selected list. Again, scope matters. It does not establish that they have never attended training. I need the list’s period and the events it covers before describing the result.
If I join the complete employee reference to attendance, E1 can appear twice after expansion. Counting output rows would answer a different question from counting distinct employees. For a yes-or-no participation report, I first establish the required employee-level summary. I do not remove a record until I know whether it describes another legitimate session.
During acceptance, I ask the reader to explain one matched employee and one unmatched case. They need to identify both the row meaning and the comparison period. A title such as No attendance in the November list is more precise than Employees without training. A technically correct table still needs an accurate description.
Check sorting and performance separately
Power Query does not guarantee that order survives a merge and other operations. Microsoft documents that limitation in Common issues. If output order matters, I sort at the end using explicit criteria and an appropriate tie-breaker.
An index can preserve information about a particular input order. It does not by itself create a stable order for data retrieved from a source. If retrieval order changes between refreshes, the same index number may refer to a different record. I prefer a key and agreed ordering criteria to whatever the current preview happens to show.
I also avoid promising that Merge always outperforms worksheet formulas. Source, volume, matching multiplicity and the opportunity to delegate operations to the source all matter. The latter is explained in query folding basics. Without a measurement of the actual process, I cannot give a universal percentage improvement or row-count threshold.
Keep fuzzy matching as a separate decision
Fuzzy matching can help compare similar text. I do not enable it merely to make unmatched formal codes disappear. Two visually similar identifiers may legitimately represent different products.
For names containing spelling mistakes, an approximate match can be a candidate for review. I then need a way to distinguish accepted pairs from disputed ones. For orders with formal product codes, I first establish the quality and definition of the code. Approximation cannot make that conversation unnecessary.
Exercise: predict the rows and totals before expansion
Recreate the two small tables. For Left Outer, write down three orders and total 180; for Inner, two orders and total 150; for Left Anti, one order and total 30. Explain each expectation by identifying the relevant codes.
Then add the second P1 and predict the expanded result before refreshing. Explain why 280 does not indicate newly created orders. Write a question for the reference owner that distinguishes a duplicate error from a missing component of the key.
Finally, describe acceptance conditions: retained identifiers, expected matching multiplicity, an exception list and a reference total. You can then pass the prepared data into a report. My guide to Power BI filters and slicers develops the next question: what scope do the displayed numbers have after the reader makes a selection?
Key takeaways
Does Merge preserve the left table row count?
Not always after expansion. Several right-side matches can repeat a left-side row, and the join kind also determines which unmatched records remain.
When should I use Left Anti?
When I need left-side rows without right-side matches, such as orders whose codes are absent from a product reference.
Should I remove duplicates when a total rises?
First establish why several matches exist. They may be reference errors or legitimate versions needing a more complete key; arbitrary removal can discard information.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
