ReconVerify

POWER QUERY RECONCILIATION GUIDE

Power Query refresh succeeded — is the reconciliation actually correct?

Not necessarily. A successful refresh shows that Power Query was able to execute the configured transformation steps. It does not, by itself, prove that the expected records were present, match keys stayed one-to-one, amounts still agree, or the refreshed result is safe to rely on.

Published · Last reviewed

Short answer

A refresh can complete successfully while the reconciliation has changed in a material way. Duplicate keys can multiply rows, missing records can remain unmatched, amounts can differ on matched keys, and input populations can change without causing the refresh itself to fail. Treat refresh completion as an execution result, then validate the assumptions that make the reconciliation trustworthy.

EXECUTION VS. VALIDATION

What a successful Power Query refresh does — and does not — establish

Power Query executes the transformation steps defined in the query. If those steps can run against the current inputs, the refresh may finish without an error. That is useful operational evidence, but it is different from testing whether the current inputs still satisfy the reconciliation's business assumptions.

Refresh success can tell you

  • the configured query steps executed;
  • the current source structure was usable enough for those steps to run;
  • the query produced a result.

Refresh success does not prove

  • every expected record was present;
  • join keys remained unique where one-to-one matching is expected;
  • matched amounts agree;
  • the result population is complete;
  • the reconciliation is accounting-correct or ready to approve.

SYNTHETIC EXAMPLE 1

The query refreshes, but a duplicate key changes a one-to-one reconciliation

Assume transaction_id is supposed to be unique in both sources. The ledger contains one A03 row. The bank source now contains two A03 rows. Power Query can still merge and expand the rows successfully, but the relationship is no longer one-to-one.

Ledger

A01100.00
A02200.00
A03300.00

Bank

A01100.00
A02200.00
A03300.00
A03300.00
Expected A03 relationship1 → 1
Observed A03 relationship1 → 2
What catches itDuplicate-key / cardinality control

Removing duplicate output rows after the fact can hide the symptom without establishing why the duplicate exists. If one-to-one matching is required, the safer question is whether the join keys are still unique before relying on the merged result.

Read the detailed duplicate-row troubleshooting guide →

SYNTHETIC EXAMPLE 2

A zero net difference can still hide missing or wrong records

Aggregate totals are valuable controls, but a matching final total is not proof that the correct records matched. Offsetting errors can cancel each other.

Expected

A10 = +500.00

A11 = -500.00

Combined impact = 0.00
Observed

A10 = +500.00 is missing

A11 = -500.00 is also missing

Combined impact on the aggregate total = 0.00

Two omissions can therefore leave the aggregate total unchanged even though record coverage is wrong. That is why reconciliation validation combines totals with structural checks such as unmatched queues, row counts, key uniqueness, and row-level amount comparisons.

FAILURE → CONTROL → EVIDENCE

What can change even when the refresh completes?

FailureWhat you may observeControlEvidence to retain
Duplicate keys / changed cardinalityUnexpected row multiplication after mergeCount keys on both sides; confirm intended relationshipAffected keys, occurrence counts, before/after row counts
Omitted recordsExpected transactions do not participate in the matchUnmatched records on both sidesUnmatched keys and source row counts
Amount differencesKeys match, values do notCompare matched amounts using the approved toleranceKey, source values, difference, tolerance
Invalid mapped amountsA value cannot be interpreted under the expected numeric rulesValidate mapped amount values before comparingAffected row/key and invalid value location
Changed source populationRow counts or signed totals move unexpectedlySource row-count and amount-total assertionsCurrent and prior/expected counts and totals
Schema or type driftColumns/types no longer match the expected contractRequired-column and required-type assertionsMissing/renamed fields, type errors, source version

REPEATABLE REVIEW

What should you verify after every recurring reconciliation refresh?

  1. Confirm the input population. Compare source row counts and signed amount totals with what the period or prior run makes reasonable.
  2. Check key completeness and uniqueness. Blank or duplicate match keys can invalidate one-to-one assumptions before the merge result is even reviewed.
  3. Review unmatched records on both sides. Do not reduce completeness to a final net difference.
  4. Compare amounts on unique matched keys. Apply the approved tolerance and retain enough detail to explain every exception.
  5. Review structural changes. Required columns, expected data types, and retained parse/transformation errors should be explicit review conditions where they matter to the workflow.
  6. Record the disposition. Keep failed controls, reviewed exceptions, source/query versions, and the human decision together.

Read the complete Power Query reconciliation validation checklist →

RECONVERIFY

Use deterministic checks after the refresh, before relying on the result

ReconVerify is a browser-local validator for existing recurring reconciliations. It currently checks blank and duplicate match keys, unmatched keys on either side, invalid mapped amounts, and amount differences beyond the tolerance you choose. It also 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 discussed in this article are useful verification practices, but they are not currently automated by ReconVerify.

Supported reconciliation inputs are read locally in the browser, so reconciliation source data and row-level financial values are not sent to ReconVerify infrastructure. The controls surface defined deviations and exceptions; they do not certify accounting correctness, audit readiness, compliance, fraud absence, or completeness beyond the controls actually run.

Try ReconVerify

PRIMARY REFERENCES

Power Query behavior referenced in this guide

Microsoft documents that Power Query merge operations use the selected join columns and join kind to determine matching rows, and documents common data-source/schema error conditions that can affect refresh behavior.