AI isn’t always the answer: when Power Query beats AI for reconciliation

What should do the reconciliation?

Power Query is often the better starting point when finance reconciliation means repeating known transformations and comparing two structured records. AI becomes useful when the task needs interpretation of messy descriptions, not when a reliable identifier and a clear rule already exist. Neither tool removes the need to explain the final difference and approve its treatment.

The practical question is not whether a process can use AI. It is which steps should produce the same answer every time, which steps contain genuine ambiguity, and who is accountable for resolving that ambiguity. Separating these three concerns usually produces a smaller, more maintainable workflow than sending an entire ledger to a model.

Begin with the records, not the model

Consider an illustrative bank export and sales ledger. Both contain transaction dates, amounts and references, but the bank shows settlement dates while the ledger shows invoice dates. One export uses negative numbers for receipts; the other uses separate debit and credit columns. An intelligent-sounding match cannot repair a definition that was never agreed.

Preserve the original exports, record their reporting periods and agree what counts as a match. Normalize dates, signs, currency, whitespace and identifier formats in a working copy. Check row counts and control totals before and after preparation. Keep source row IDs so a reviewer can trace every result to the record that produced it.

Build an exact-match baseline

Microsoft documents Power Query merges as joins between tables using selected columns. A dependable transaction ID can provide a stronger basis than a supplier name. Where one key is insufficient, a documented combination of reference, amount and currency may help, provided its uniqueness has been checked. A repeated reference should not silently become a many-to-many match.

Use the join appropriate to the review question. A left join can expose records without a counterpart on one side; a full outer join can help surface unmatched records from both. Then separate matched, unmatched and ambiguous populations. Reconcile their totals back to the inputs. A refreshable query is valuable because its transformations are explicit and repeatable, not because a successful refresh proves the balance is correct.

Keep approximate matches out of the approved pile

A fictional supplier might appear as ‘North Café’ in one file and ‘NORTH CAFE #184’ in another. Power Query offers fuzzy matching for text columns, but similarity is not financial evidence. Two related businesses may have nearly identical names; two invoices may have identical amounts. A plausible candidate still needs a reference, date window or document to support it.

Use approved alias tables for known variations where practical. Put unresolved fuzzy candidates into an exception queue, retaining the match method and relevant evidence. Document thresholds and changes to them. Do not tune a threshold just until the exception count looks small: fewer flags can mean more missed problems, rather than a better reconciliation.

Where does AI actually help?

AI can propose categories for narrative descriptions, summarize recurring exception reasons or suggest why two descriptions may refer to the same transaction. Give it a bounded task and only data permitted by your organization. Ask it to distinguish evidence from a suggestion, and require a reviewer to compare any conclusion with the underlying records.

It should not independently certify a match, invent a missing invoice number, post an adjustment or explain a variance as a fact without support. The workflow should remain useful when the AI step is unavailable. Deterministic preparation and the review queue can still run, with unresolved narrative questions assigned to a person.

A small, controlled implementation

1. Select one recurring reconciliation and one complete reporting period. Define inputs, output and the owner of unresolved items.
2. Record totals and counts, normalize the fields and check key uniqueness before matching.
3. Run exact rules first. Keep unmatched and ambiguous records visible with source references.
4. Introduce fuzzy or AI suggestions only for a defined exception class; do not merge them into confirmed matches automatically.
5. Have a reviewer sample matches, resolve exceptions and sign off the final reconciliation. Save the query version and decision evidence.

Test duplicate references, missing IDs, reversed signs and unexpected currencies as well as the ordinary case. Changing source columns or export filters should produce a visible failure, not an apparently clean empty report. Agree what happens when the owner is absent or the input arrives late.

Choose the simplest dependable system

Power Query is not automatically suitable for every volume, access arrangement or scheduling requirement. APIs, database queries or other controlled code may be more appropriate. The principle remains the same: use the least complex tool that meets the control and maintenance requirements, then make uncertainty visible. SHORTEND’s reconciliation work begins with those requirements, not with a commitment to use AI.