Power Query Left Anti Join: Find Missing Rows Without Losing Duplicates

Define missing records, not just missing values

This procedure is for desktop Excel on Windows with Power Query. The requested list contains two requests for A200, identified by L2 and L3. Both represent real lines, so both should remain if A200 is absent from AvailableItems. A report of distinct missing item IDs would have a different grain and needs a separate deduplication step after the line-level comparison.

The reference list includes A300 twice. Those repeated entries do not change the existence question: L4 has at least one match. We also include A900 only on the right to show the direction of the operation. A left anti result cannot tell you about that right-only item. It answers what is absent from the right for the selected left records.

Load both sources with a key policy

Create RequestedItems from requested-items.csv and AvailableItems from available-items.csv using Excel Tables with headers. Import each through Data > From Table/Range. Keep item_id as Text and line_id as Text. An item code is a label; converting 00123 to number 123 would change the comparison before the join begins.

Separate the empty-key request L5 before merging. This guide treats null, empty text, and whitespace-only text as invalid keys. They are not confirmed missing inventory items. Use an exception query or retain those rows in a separate table. For valid keys we deliberately keep surrounding whitespace, so the leading-space L6 remains a visible mismatch instead of being silently repaired.

Create the left anti result

In each query, apply the agreed invalid-key filter. The supplied complete M query implements this rule explicitly. The graphical steps below assume the valid-key versions are already available as connection-only queries. Keep the raw Tables unchanged so you can trace an unexpected exclusion.

1. Open the valid RequestedItems query and choose Home > Merge Queries > Merge Queries as New.
2. Select valid AvailableItems as the second query. Click item_id in both previews and choose Left Anti as the join kind. Leave fuzzy matching off.
3. Confirm the merge. Remove the nested right_matches or AvailableItems column; this report needs the left fields, so expanding right-side details adds no information.
4. Keep line_id, item_id, and quantity. Sort by line_id ascending to obtain L2, L3, and L6 in the displayed order.
5. Load to a new worksheet and compare every row with missing-items-expected.csv. Keep invalid-key-expected.csv separate from this business result.

Use a complete query when the filter needs to be explicit

The following query reads the two named Excel Tables and preserves valid keys exactly as supplied. Text.Trim is used only to decide whether a key contains anything besides whitespace. It does not replace the key used for matching. That is why L6 still differs from A100 after filtering.

Create a blank query, open Home > Advanced Editor, and replace its contents with this code after making the two source Tables. The result contains unmatched valid request lines only. Build a separate reference to the raw requests for invalid IDs if you need that exception output on every refresh. The attached file documents the one invalid record in this example.

let
    Left = Excel.CurrentWorkbook(){[Name="RequestedItems"]}[Content],
    Right = Excel.CurrentWorkbook(){[Name="AvailableItems"]}[Content],
    LeftTyped = Table.TransformColumnTypes(Left, {{"line_id", type text}, {"item_id", type text}, {"quantity", Int64.Type}}),
    RightTyped = Table.TransformColumnTypes(Right, {{"item_id", type text}}),
    ValidLeft = Table.SelectRows(LeftTyped, each [item_id] <> null and Text.Trim([item_id]) <> ""),
    ValidRight = Table.SelectRows(RightTyped, each [item_id] <> null and Text.Trim([item_id]) <> ""),
    Missing = Table.NestedJoin(ValidLeft, {"item_id"}, ValidRight, {"item_id"}, "right_matches", JoinKind.LeftAnti),
    Output = Table.RemoveColumns(Missing, {"right_matches"}),
    Sorted = Table.Sort(Output, {{"line_id", Order.Ascending}})
in
    Sorted

Choose whether cleaning should change the answer

If your item specification says leading and trailing spaces are accidental, create a normalized comparison column in both queries with Text.Trim before matching. Keep the original item_id for audit. Under that revised rule, L6 matches A100 and disappears from the missing result; only L2 and L3 remain, totaling 4. Changing the normalization rule is a substantive change to the report, not a formatting improvement.

For keys defined by multiple fields, such as warehouse and item_id, select both fields in the same order in each preview. First ensure you have the same types and conventions on both sides. A match in the wrong warehouse is still missing from the requested warehouse. Conversely, adding line_id to the key is inappropriate if the reference list never claims to contain request-line identifiers.

Check counts and reverse the comparison deliberately

The row partition is a strong first-load check: one invalid row plus two matched rows plus three unmatched rows equals the six inputs. Duplicate A200 lines remain distinct because line_id identifies the request. A count of distinct item IDs would not reconcile in the same way. Keep both counts if the report needs both workload and product coverage.

For the reverse report, use valid AvailableItems on the left and valid RequestedItems on the right with the same exact-key rules. The result is A900. If A900 appeared three times in the reference, a row-level reverse comparison would retain those three unmatched records; deduplicate only if the desired result is a list of unique reference IDs. Save the two reports with names that describe their direction.

The supplied M query was executed through the native Power Query provider in desktop Excel 16.0 build 5569. It returned L2, L3 and L6 exactly, totaling 10. An explicitly trimmed variant returned L2 and L3 totaling 4; a reversed variant returned A900. The six-row partition and separate invalid-key file were independently checked. These are named-table query-engine tests with synthetic inputs, not a recording of Merge dialog clicks or a guarantee about every source connector.