Find what's missing with a Power Query anti join

To find invoices in the GL but not the subledger, the old routine is a VLOOKUP, a filter for #N/A, and doing it all again next month.

Power Query has a merge type built for this question.

The Left Anti merge

In Home → Merge Queries, pick both tables, select the key column in each, and set Join Kind to Left Anti. The result is only the rows from the first table that have no match in the second:

Table.NestedJoin(GL, {"Invoice"}, Subledger, {"Invoice"}, "Match", JoinKind.LeftAnti)

Run it again with Right Anti, or swap the tables, to find what’s on the other side only. Those two lists are your reconciling items, and you can refresh them every month.

The Match column it adds will be empty, so you can remove it.

Clean the keys first

This is where most false breaks come from. Power Query merges are exact and case-sensitive, so INV-001, inv-001 and INV-001 are all different.

Create a clean key in both tables before you merge:

Table.AddColumn(Source, "Key", each Text.Upper(Text.Trim(Text.From([Invoice]))), type text)

Text.From handles invoices stored as numbers in one system and text in the other, which is another common cause.

An anti join only finds missing rows

It won’t tell you about an invoice that’s on both sides with different amounts. For that, do a normal Full Outer merge, expand the amounts, and add a difference column.

And watch for duplicate keys. In a normal merge, a key that appears twice multiplies the matching rows and inflates your totals. An anti join won’t reveal that either, so count your distinct keys separately.