A left anti join in Power Query returns the rows of the first table that have no match in the second. Merge your bank query with your ledger query on a key, choose Left Anti as the join kind, and you get the bank lines that are missing from the ledger. Flip the order for the reverse, and each result can load to its own sheet and refresh with one click.
How do you load both lists into Power Query?
- Turn each list into an Excel Table (select the range and press Ctrl+T). Name them Bank and Ledger.
- Click inside the Bank table, then Data, From Table/Range. The Power Query editor opens.
- On the Home tab choose Close & Load To, pick Only Create Connection, and click OK.
- Repeat for the Ledger table.
You now have two queries that feed the matching step but do not clutter the workbook with duplicate sheets.
How do you create the match key?
The merge needs one column that is identical for matching rows. In each query, use Add Column, Custom Column, name it Key, and enter:
=Date.ToText([Date], "yyyy-MM-dd") & "|" & Number.ToText([Amount], "0.00")
If both sides carry a reference, add it as well: & "|" & Text.Upper(Text.Trim([Ref])). Power Query is case-sensitive and exact about spaces, which is why the Upper and Trim parts matter. Also confirm that the Date column's type is Date, not Date/Time, on both sides.
How do you run the Left Anti merge?
- In the editor, Home, Merge Queries, Merge Queries as New.
- Pick Bank as the top table and click the Key column. Pick Ledger as the bottom table and click its Key column.
- In Join Kind, select Left Anti (rows only in first).
- Click OK. You get the Bank columns plus a column called Ledger that is empty for every row.
- Right-click the Ledger column and choose Remove. Rename the query Bank not in Ledger.
Close & Load that query to a new sheet. Those are your bank lines that have no match in the ledger.
Which join kind answers which question?
| Question | Merge order | Join kind |
|---|---|---|
| Bank lines with no ledger entry | Bank first, Ledger second | Left Anti |
| Ledger entries with no bank line | Bank first, Ledger second | Right Anti |
| Everything that matches | Bank first, Ledger second | Inner |
| All rows from both, matched or not | Bank first, Ledger second | Full Outer |
Right Anti keeps the rows of the second table that have no partner in the first, so you can answer both questions with the same merge order. After running it, remove the Bank column that appears and name the query Ledger not in Bank.
What if nothing matches, or everything does?
Check the Key column in both queries side by side. If every row comes back unmatched, the key format is probably different, for example one side shows 18000.00 and the other 18000. If the opposite happens and a suspicious row sneaks through, remember that two identical rows on one side both match the same single row on the other side, so duplicates can hide. Add a Count column with Group By on the Key to find them.
Review checklist
- Do the two result sheets add up to the totals you expect?
- Does each row on the bank-only sheet have an explanation, such as a bank charge, an unrecorded payment or a timing gap?
- Does each ledger-only row have a reason, such as a cheque still to clear?
- Have you saved the workbook before refreshing next month?
Power Query is part of Excel for Windows and recent Excel for Mac. Menu names can vary a little between versions, so look for the same words if a button sits in a slightly different place.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


