Power QueryExercises

Reconcile two tables in Power Query (bank vs ledger, system vs system)

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

The recipe

  1. Standardise keys on both sides: trim, case, types, and amounts rounded to cents.
  2. Check each side's key is unique, or reconcile at an aggregated level.
  3. Full outer merge, so rows from either side survive.
  4. Expand the other side's columns with distinct names.
  5. Classify each row; then summarise by status.
let
    Bank = #table({"Ref", "Amount"}, {{"TX-01", 120}, {"TX-02", 45.5}, {"TX-04", 18}}),
    Ledger = #table({"Ref", "Amount"}, {{"tx-01 ", 120}, {"TX-02", 45}, {"TX-03", 60}}),
    Clean = (t as table) as table => Table.TransformColumns(t, {{"Ref", each Text.Upper(Text.Trim(_)), type text}, {"Amount", each Number.Round(_, 2), type number}}),
    Joined = Table.NestedJoin(Clean(Bank), {"Ref"}, Clean(Ledger), {"Ref"}, "L", JoinKind.FullOuter),
    Expanded = Table.ExpandTableColumn(Joined, "L", {"Ref", "Amount"}, {"Ledger Ref", "Ledger Amount"}),
    Status = Table.AddColumn(Expanded, "Status", each
        if [Ref] = null then "Only in ledger"
        else if [Ledger Ref] = null then "Only in bank"
        else if [Amount] = [Ledger Amount] then "Matched"
        else "Amount differs", type text)
in
    Status

Result

RefAmountLedger RefLedger AmountStatus
TX-01120TX-01120Matched
TX-0245.5TX-0245Amount differs
TX-0418nullnullOnly in bank
nullnullTX-0360Only in ledger

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Without the Clean function, tx-01 wouldn't match TX-01 and both rows would appear as missing.

Summarise by status

let
    Rec = #table({"Status", "Amount", "Ledger Amount"}, {{"Matched", 120, 120}, {"Amount differs", 45.5, 45}, {"Only in bank", 18, null}, {"Only in ledger", null, 60}}),
    Summary = Table.Group(Rec, {"Status"}, {
        {"Items", each Table.RowCount(_), Int64.Type},
        {"Bank total", each List.Sum([Amount]) ?? 0, type number},
        {"Ledger total", each List.Sum([Ledger Amount]) ?? 0, type number}
    })
in
    Summary

Result

StatusItemsBank totalLedger total
Matched1120120
Amount differs145.545
Only in bank1180
Only in ledger1060

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Read the status column before the totals. Only in bank items are usually timing (posted in the ledger next month) or missing entries; Amount differs items are usually fees, rounding or keying errors. Each status suggests a different owner and a different fix.

Choosing the key

A shared reference is best. Without one, reconcile on a composite key such as date + amount + counterparty, and review partial matches separately. Never match on amount alone: two payments of 45.00 on different days aren't the same payment.

Duplicates on either side

If a reference appears twice on one side, a full outer merge pairs it with every match on the other side and the totals double. Check uniqueness first; if duplicates are legitimate (split payments), group each side by the key and reconcile the totals instead of the lines.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.