Reconcile two tables in Power Query (bank vs ledger, system vs system)
Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated
The recipe
- Standardise keys on both sides: trim, case, types, and amounts rounded to cents.
- Check each side's key is unique, or reconcile at an aggregated level.
- Full outer merge, so rows from either side survive.
- Expand the other side's columns with distinct names.
- 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
StatusResult
| Ref | Amount | Ledger Ref | Ledger Amount | Status |
|---|---|---|---|---|
| TX-01 | 120 | TX-01 | 120 | Matched |
| TX-02 | 45.5 | TX-02 | 45 | Amount differs |
| TX-04 | 18 | null | null | Only in bank |
| null | null | TX-03 | 60 | Only in ledger |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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
SummaryResult
| Status | Items | Bank total | Ledger total |
|---|---|---|---|
| Matched | 1 | 120 | 120 |
| Amount differs | 1 | 45.5 | 45 |
| Only in bank | 1 | 18 | 0 |
| Only in ledger | 1 | 0 | 60 |
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.
- intermediate~8 minPro
Find orders from unknown customers (kite shop)
Use a left anti join to list orders whose customer isn't in the customer master.
- advanced~10 minPro
Look up prices by shop and product together (farm shops)
Merge Sales with Prices on both Store and Product, then calculate line revenue.
Related pages
Lessons
More resources
Function reference
Sources
Microsoft's official documentation for the features and functions used on this page.
- Merge queries overview (Concept, Microsoft Learn)
- Left anti join (Concept, Microsoft Learn)
- Inner join (Concept, Microsoft Learn)
- Table.TransformColumns (Function reference, Microsoft Learn)
- Text.Upper (Function reference, Microsoft Learn)
- Text.Trim (Function reference, Microsoft Learn)
- Number.Round (Function reference, Microsoft Learn)
- Table.NestedJoin (Function reference, Microsoft Learn)
- Table.ExpandTableColumn (Function reference, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
- Table.Group (Function reference, Microsoft Learn)
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.