POWER QUERY RECONCILIATION GUIDE
How to validate a recurring Power Query reconciliation after refresh
A refresh that finishes without an error is not, by itself, evidence that the reconciliation still behaved as intended. Validation checks the assumptions around the refresh: source completeness, key cardinality, unmatched records, amounts, schema, data types, and the resulting exceptions.
What should you check after a Power Query reconciliation refresh?
At minimum, compare source and output row counts and signed totals; verify that keys expected to be unique are still unique; review unmatched records on both sides; identify amount differences; confirm required columns and data types; retain transformation errors rather than silently discarding them; and record the exceptions a human reviewer accepted or resolved.
THE CORE PROBLEM
Why a successful refresh is not the same as a trustworthy reconciliation
Power Query can successfully execute the transformation steps it has been given while the underlying inputs have changed in ways that alter the result. A duplicated match key can turn an intended one-to-one merge into a one-to-many merge. A missing source row can remain unmatched. A renamed column or changed type can break assumptions downstream. An amount can change while the join itself still succeeds.
The practical question is therefore not only “did the query refresh?” but “did the refreshed reconciliation preserve the relationships, coverage, structure, and amounts that the reviewer expects?”
CHECKLIST
Eight controls to review before relying on the refresh
Source row counts
Record the row count for each source and investigate unexpected increases or decreases. A count change is not automatically an error, but it is evidence that the input population changed.
Source signed totals
Record signed amount totals for each source. Do not rely only on absolute values or a final net difference, because offsetting errors can hide inside a total that appears reasonable.
Key uniqueness and cardinality
If a match key is supposed to be unique, test that assumption on both sides. Duplicate keys can multiply rows after a merge and inflate matched amounts without changing the query logic.
Unmatched records
Review unmatched ledger and bank rows separately. A clean net total does not prove that every expected transaction participated in the reconciliation.
Amount differences
For matched records, compare the relevant amount fields using the business-approved tolerance. Preserve the key, difference, and source values needed for human review.
Required columns and schema
Confirm that required columns still exist with the expected names. Treat missing or renamed fields as a review condition instead of letting downstream steps define the outcome implicitly.
Required data types and parse errors
Check that amount and date fields still parse to the expected types, and retain conversion errors. Replacing or removing error rows can make a workflow look cleaner while reducing visibility into what changed.
Output and change review
Compare output counts and totals with the expected relationship, then record failed controls, reviewed exceptions, source/query versions, and the reviewer’s disposition.
COMMON MISREAD
Why matching totals can still hide a reconciliation failure
A zero net variance is useful evidence, but it is not sufficient evidence of completeness or correct matching. Two errors can offset each other. A duplicated transaction can be balanced by an omitted transaction. Two unmatched records can have equal and opposite amounts. A row can match to the wrong record while the aggregate total remains unchanged.
That is why reconciliation validation should combine aggregate checks with structural checks: row counts, unique keys, unmatched queues, row-level amount comparisons, schema assertions, and retained errors.
MERGE TROUBLESHOOTING
Seeing duplicate rows after a Power Query merge?
When an intended one-to-one merge expands to multiple output rows, inspect the join key on both inputs before changing the query. The merge may be exposing duplicate keys that were already present in one source.
Read the duplicate-row troubleshooting guide →REPEATABLE REVIEW
A practical validation workflow for recurring reconciliations
- Capture the input state. Record source row counts, signed totals, expected columns, and the version or period being reconciled.
- Refresh the existing Power Query workflow. Do not treat refresh completion as the final control.
- Run structural controls. Check key uniqueness, cardinality, schema, and required data types.
- Review coverage and values. Inspect unmatched records and matched amount differences using the approved tolerance.
- Compare the output. Review output row counts, totals, and unexpected changes from the prior or expected state.
- Retain exceptions and disposition. Keep enough evidence for another reviewer to understand what failed, what was investigated, and why the result was accepted or rejected.
RECONVERIFY
Validate reconciliation files locally in the browser
ReconVerify is a browser-local validator for existing recurring reconciliations. Supported inputs are read in the browser so reconciliation source data and row-level financial values are not sent to ReconVerify infrastructure. You map the key and amount fields, run deterministic controls, review exceptions, and can open or download a self-contained validation report.
The current browser beta automates checks for blank and duplicate match keys, unmatched keys on either side, invalid mapped amounts, and amount differences beyond the tolerance you set. It reports source row counts and valid amount totals as review metrics. Schema assertions, required data-type assertions, retained transformation-error checks, and expected-output regression checks in the checklist above are review guidance and are not currently automated by ReconVerify.
ReconVerify surfaces defined deviations and exceptions. It does not certify accounting correctness, audit readiness, compliance, fraud absence, or completeness beyond the controls that were actually run.
Try ReconVerifyPrimary references
Microsoft documents how Power Query merges use selected join keys and join kinds, and how data-source, schema, and type changes can produce errors or unintended results.