Guides · Excel automation

Power Query Merge Lost Rows: Find Them Before You Deliver

Published 9 October 2026 · Tested with Microsoft 365 Excel 16.0 (build 20430, 64-bit), Windows 11 Pro, PowerShell 7.6.6 and Windows PowerShell 5.1.26100.9444

You merge an orders table with a customers table in Power Query. The result has fewer rows than the orders, or it has all of them and some customers are blank. Nothing failed, and the report goes out. This guide reproduces how that happens in a small Excel workbook, shows a merge that cleans the keys first, and gives a PowerShell check that refreshes the query and fails with an exit code when rows are lost, keys have no match or the result grew.

Before you start

• Windows with Excel desktop and a workbook where a Power Query merge reads two Excel tables. The check opens a copy of the workbook in an Excel process of its own, so it needs Excel installed but never touches the Excel you have open.
• A trusted workbook. Refreshing a query runs whatever the query does. Run the check only on files you made or trust.
• The left table is the one you want to keep whole (here, orders); the right table is the lookup (customers). Microsoft puts it the same way: “The position (left or right) of the tables becomes very important when you select the correct join kind to use” (Microsoft Learn, Merge queries overview).

The test workbook

Everything below was measured on a synthetic workbook. The sheet Orders holds a table Orders (OrderID, CustomerKey, Amount) with 10 invented orders. The sheet Customers holds a table Customers (CustomerKey, CustomerName) with 7 invented customers, C001 to C007. A query OrdersWithCustomer merges them and loads the result into a table on the sheet Result. Five orders have a key that does not match Customers exactly:

The other five orders (C001, C002, C006, and C002 and C001 again) match. The queries were refreshed from PowerShell through COM in an Excel process of its own, with alerts off. Each case was checked three times in each PowerShell with the same exit code, and the inner, left outer and cleaned-key workbooks were also built three times with the same rows.

Where the rows go

1. An inner join keeps only the matches. The merge below uses JoinKind.Inner. Microsoft's reference for Table.NestedJoin says that “a left outer join is performed if a joinKind is not specified” (Microsoft Learn, Table.NestedJoin), so an inner join is something somebody chose, in the dialog or by editing the step. The refresh raised no error:

= Table.NestedJoin(Orders, {"CustomerKey"}, Customers, {"CustomerKey"}, "Customer", JoinKind.Inner)

orders: 10   rows after the merge: 5

The five orders with a dirty or missing key were gone from the report, and so was their amount.

2. A left outer join keeps the rows and shows the problem as blanks. With JoinKind.LeftOuter all 10 orders stayed. The five from the table above came back with an empty CustomerName. Microsoft's page on the left outer join says: “a null value is the result of the merge for that row” when there is no match (Microsoft Learn, Left outer join):

1001 | C001  | 120 | Alder Ltd
1003 | c003  | 45  | <null>
1004 | C004␣ | 60  | <null>
1005 | C005␣ | 30  | <null>       (the second space is U+00A0)
1007 | <null> | 15  | <null>
1009 | C009  | 90  | <null>

3. Keys are compared exactly. In our test c003 did not find C003, C004␣ did not find C004, and a key ending in a non-breaking space did not find its customer. Power Query reported none of it. Case, spaces and invisible characters are yours to clean.

4. Different types match nothing, without an error. We merged a whole-number key (1, 2, 3) with a text key ("1", "2", "3"), left outer: three rows came back and all three lookups were empty. Microsoft warns that “the columns must be of the same data type, otherwise the merge operation might not yield correct results” (Microsoft Learn, Merge queries overview). The merge dialog also shows an estimate of the matches before you click OK; if it is lower than you expect, stop there.

Two more things we saw. An empty key matched an empty key: with null and "" in both tables, an inner join paired each with its twin, so an order without a customer key can pick up a customer that also has none. And a duplicate key on the right makes the result grow: with C001 twice in Customers the result had 12 rows for 10 orders.

A merge that cleans the keys

The merge compares keys the way you mean it if both sides are first turned into one clean text key. The query below does that for both tables, keeps every order (left outer) and drops the helper column:

let
    NormalizeKey = (k as any) as nullable text =>
        if k = null then null
        else Text.Upper(Text.Trim(Text.Clean(Text.From(k)))),
    Orders0 = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Customers0 = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    Orders = Table.AddColumn(Orders0, "Key", each NormalizeKey([CustomerKey]), type nullable text),
    Customers = Table.AddColumn(Customers0, "Key", each NormalizeKey([CustomerKey]), type nullable text),
    Merged = Table.NestedJoin(Orders, {"Key"}, Customers, {"Key"}, "Customer", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Customer", {"CustomerName"}, {"CustomerName"}),
    Result = Table.RemoveColumns(Expanded, {"Key"})
in
    Result

The function converts any key to text, removes control characters, trims white space, and upper-cases it. In our build Text.Trim removed the non-breaking space too; in a separate test the whole number 1 and the text "1" became the same key. With this query, three of the five problem orders found their customer (c003, C004␣, C005␣). The other two are real: one order has no key and one customer does not exist. Cleaning cannot fix those, and it should not hide them. An empty key stays null, which still matches another null, so the check below flags every row with an empty key.

You may expect to pass a comparer such as Comparer.OrdinalIgnoreCase as the last argument of Table.NestedJoin instead. Microsoft documents that argument as “currently intended for internal use only” and, in our test, the refresh failed with Local evaluation of Table.Join or Table.NestedJoin with key equality comparers is not supported. Clean the keys instead.

A check that counts before you deliver

A clean merge is still a claim. Test-MergeRows.ps1 checks it: it copies the workbook, refreshes the result table in the foreground in a separate Excel process, reads the two source tables and the result, and compares them. It reports rows lost, rows added by duplicate keys, and every row without a match with the likely reason. Exit code 0 means it passed, 1 that a check failed, 2 that it could not run (for example, the refresh raised an error). -MaxUnmatched is the number of unmatched rows you accept, 0 unless you say otherwise.

#requires -Version 5.1
<#
Refreshes the merge query in a copy of the workbook and checks the result against
its two source tables. Exit codes: 0 passed, 1 check failed, 2 could not run.
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory)][string]$Workbook,
    [string]$LeftTable = 'Orders',  [string]$LeftKey = 'CustomerKey',
    [string]$RightTable = 'Customers', [string]$RightKey = 'CustomerKey',
    [string]$ResultTable = 'OrdersWithCustomer',
    [string]$MatchColumn = 'CustomerName',   # a column that is filled only when the right row was found
    [int]$MaxUnmatched = 0
)
$ErrorActionPreference = 'Stop'
[Threading.Thread]::CurrentThread.CurrentCulture = [Globalization.CultureInfo]::InvariantCulture

function Get-Column($Book, [string]$Table, [string]$Column) {
    foreach ($ws in $Book.Worksheets) {
        foreach ($lo in $ws.ListObjects) {
            if ($lo.Name -ne $Table) { continue }
            $body = $lo.ListColumns.Item($Column).DataBodyRange
            if ($null -eq $body) { return @() }          # table with no rows
            $v = $body.Value2
            if ($v -is [array]) { return @(1..$v.GetLength(0) | ForEach-Object { $v[$_, 1] }) }
            return @($v)
        }
    }
    throw "table '$Table' not found"
}
function Get-Table($Book, [string]$Table) {
    foreach ($ws in $Book.Worksheets) { foreach ($lo in $ws.ListObjects) { if ($lo.Name -eq $Table) { return $lo } } }
    throw "table '$Table' not found"
}
function Show([object]$k) { if ($null -eq $k) { '<null>' } else { '"' + ([string]$k).Replace([string][char]160, '<NBSP>') + '"' } }
function Fold([object]$k) { if ($null -eq $k) { $null } else { ([string]$k).Trim().ToUpperInvariant() } }

$copy = Join-Path ([IO.Path]::GetTempPath()) ("merge-check-{0}.xlsx" -f [guid]::NewGuid().ToString('N'))
$excel = $null; $excelPid = 0
try {
    Copy-Item -LiteralPath $Workbook -Destination $copy
    Add-Type -Namespace SL -Name Win -MemberDefinition '[DllImport("user32.dll")] public static extern int GetWindowThreadProcessId(int h, out int pid);'
    $excel = New-Object -ComObject Excel.Application
    [void][SL.Win]::GetWindowThreadProcessId($excel.Hwnd, [ref]$excelPid)
    $excel.Visible = $false; $excel.DisplayAlerts = $false
    $book = $excel.Workbooks.Open($copy)
    $lo = Get-Table $book $ResultTable
    $lo.QueryTable.BackgroundQuery = $false
    [void]$lo.QueryTable.Refresh($false)           # a failed refresh throws with Power Query's message
    $left = Get-Column $book $LeftTable $LeftKey
    $right = Get-Column $book $RightTable $RightKey
    $resKey = Get-Column $book $ResultTable $LeftKey
    $resMatch = Get-Column $book $ResultTable $MatchColumn
    $book.Close($false)
} catch {
    Write-Host "COULD NOT RUN: $($_.Exception.Message)"
    exit 2
} finally {
    if ($excel) { $excel.Quit(); [void][Runtime.InteropServices.Marshal]::ReleaseComObject($excel) }
    if ($excelPid) { Start-Sleep -Milliseconds 500; Stop-Process -Id $excelPid -ErrorAction SilentlyContinue }
    Remove-Item -LiteralPath $copy -ErrorAction SilentlyContinue
}
$problems = New-Object Collections.Generic.List[string]
Write-Host ("left rows {0}, result rows {1}" -f $left.Count, $resKey.Count)

$rightFolded = @{}
foreach ($k in $right) { $f = Fold $k; if ($null -ne $f) { $rightFolded[$f] = 1 + [int]$rightFolded[$f] } }
function Get-Reason([object]$k) {
    if ("$k" -eq '') { return 'key is empty' }
    if ($right -ccontains $k) { return 'key exists on the right' }
    if ($rightFolded.ContainsKey((Fold $k))) { return 'differs only by spaces or case' }
    'no such key on the right'
}

if ($resKey.Count -lt $left.Count) {
    $problems.Add("$($left.Count - $resKey.Count) left row(s) lost")
    $left_ = @{}; foreach ($k in $resKey) { $left_["$k"] = 1 + [int]$left_["$k"] }
    foreach ($k in $left) {                       # keys of the left table that the result no longer holds
        if ($left_["$k"] -gt 0) { $left_["$k"]-- } else { Write-Host ("  lost: {0}: {1}" -f (Show $k), (Get-Reason $k)) }
    }
}
if ($resKey.Count -gt $left.Count) { $problems.Add("$($resKey.Count - $left.Count) extra row(s): duplicate keys on the right side?") }
$dupes = @($rightFolded.GetEnumerator() | Where-Object Value -gt 1)
if ($dupes.Count) { $problems.Add('duplicate keys on the right: ' + (($dupes | ForEach-Object Key) -join ', ')) }

$unmatched = @(if ($resKey.Count) { 0..($resKey.Count - 1) | Where-Object { "$($resKey[$_])" -eq '' -or "$($resMatch[$_])" -eq '' } })
Write-Host ("rows without a match: {0}" -f $unmatched.Count)
foreach ($i in $unmatched) { Write-Host ("  {0}: {1}" -f (Show $resKey[$i]), (Get-Reason $resKey[$i])) }
if ($unmatched.Count -gt $MaxUnmatched) { $problems.Add("$($unmatched.Count) row(s) without a match, allowed $MaxUnmatched") }

if ($problems.Count) { Write-Host ('FAILED: ' + ($problems -join '; ')); exit 1 }
Write-Host 'OK'; exit 0

Run it from a scheduled task or a build step and stop the delivery when the exit code is not 0:

pwsh -File .\Test-MergeRows.ps1 -Workbook .\report.xlsx -MaxUnmatched 0
if ($LASTEXITCODE -ne 0) { throw "Merge check failed ($LASTEXITCODE); not delivering." }

Results

The same check on the same data (copied from the test runs; the exit codes were the same in PowerShell 7.6.6 and Windows PowerShell 5.1, three runs each):

The output for the first, second and fourth rows, and the passing run:

left rows 10, result rows 5
  lost: "c003": differs only by spaces or case
  lost: "C004 ": differs only by spaces or case
  lost: "C005<NBSP>": differs only by spaces or case
  lost: <null>: key is empty
  lost: "C009": no such key on the right
rows without a match: 0
FAILED: 5 left row(s) lost

left rows 10, result rows 10
rows without a match: 5
  "c003": differs only by spaces or case
  "C004 ": differs only by spaces or case
  "C005<NBSP>": differs only by spaces or case
  <null>: key is empty
  "C009": no such key on the right
FAILED: 5 row(s) without a match, allowed 0

left rows 10, result rows 12
rows without a match: 2
  <null>: key is empty
  "C009": no such key on the right
FAILED: 2 extra row(s): duplicate keys on the right side?; duplicate keys on the right: C001

left rows 10, result rows 10
rows without a match: 2
  <null>: key is empty
  "C009": no such key on the right
OK

With the inner join the lost orders are not in the result, so the check finds them by comparing the keys of the left table with the keys the result still holds.

Limits

• One machine, one Excel. Windows 11 Pro, Microsoft 365 Excel 16.0 build 20430, PowerShell 7.6.6 and Windows PowerShell 5.1.26100.9444. We did not test Power BI, Excel for the web, Mac, or other Excel builds, where the merge dialog and error messages can differ.
• The sources are Excel tables. The check reads the left and right tables from the same workbook. If your query reads CSV files, a folder or a database, load those inputs to tables (or extend the script) so there is something to compare. We did not test those sources.
• One merge, one key column. We tested a single key. Multi-column keys, several merges in a chain and merges that follow a filter or a group are not covered; the check compares only the final table.
• Cleaning can join what should stay apart. Upper-casing treats ab1 and AB1 as the same key. If your IDs are case-sensitive, drop Text.Upper from the query and ToUpperInvariant from the script.
• Other invisible characters are not tested. We tested a space and a non-breaking space. A zero-width space or a tab inside the key may need its own replacement step.
• Fuzzy matching is not tested. The merge dialog offers it; we did not use it, and the check would count a fuzzy match as a match.
• Row order changes. In our runs the merged rows did not come back in the order of the orders table (1008 came before 1003). Compare by key, never by position.
• It counts and compares keys; it does not read the data. A matched row can still hold the wrong customer. The check finds lost, extra and unmatched rows, nothing more.
• Small tables. We ran 10 and 7 rows. The script reads each column through COM in one call and should cope with thousands of rows; we did not time larger tables.
• It refreshes a copy. The script does not save the original. If your result is a PivotTable or lives only in the Data Model, point it at a table that holds the merge result.

The code on this page was written for this guide and tested on the versions above. The test runs also set the culture to en-US, so that messages and numbers came out in English. The workbook and its data are invented.