Excel RefreshAll can leave stale data without raising an error

A plausible total survived a broken source

Our expense workbook has a RawExport table, a Clean_Actuals Power Query, a loaded Actuals table and an October report. The source and original deliverable are downloadable. We copied the workbook and renamed RawExport to DeliberatelyMissingSource without changing the query that refers to it.

That breaks a concrete dependency: Excel.CurrentWorkbook can no longer find the named source. The expected successful-refresh outcome is therefore impossible until the source is restored or the query is changed. We did not change a customer file, a production connection or the original published workbook.

Before the failure injection, the loaded table contained 480 documents and Report!C9 was $686,500. Those values remained after the attempted refresh. Their plausibility is the problem: a reviewer who sees a tidy report and no formula errors may accept yesterday’s data.

Compare the aggregate call with a direct refresh

We disabled background refresh on the loaded query table, called Workbook.RefreshAll, and then waited with CalculateUntilAsyncQueriesDone. Our catch block saw no error. The call sequence took 1.471 seconds. The affected table and report retained their previous values.

We then called the loaded table’s QueryTable.Refresh(false) directly. That call raised an Expression.Error saying Excel could not find the RawExport table. The caught direct failure took about 0.061 seconds. We retained the error instead of turning it into a successful delivery receipt.

Microsoft’s current RefreshAll documentation explicitly describes this Power Query worksheet-table behavior, including retained previous data and the need to refresh tables directly to detect these errors. Our experiment reproduces that documented case; it does not claim that every Excel connection fails in the same way.

Idle is different from correct

After the failure, CalculationState was 0 and QueryTable.Refreshing was false. These properties describe calculation and background-query activity. They do not certify that the correct source was read, that a refresh succeeded, or that a new batch reached the report.

Adding a fixed sleep would not fix this missing table. Recalculating formulas also cannot reconstruct the absent source. The right response is to expose the failed dependency, preserve the old deliverable as old, and stop the release of a supposedly current report.

The complementary freshness-witness experiment shows a successful path: add one known $1 document, refresh the actual query, and verify both its unique identity and its expected effect on the report.

Put the refresh failure inside the task contract

Refresh the table you depend on and handle an exception as a failed stage. Then check the accepted source batch, row coverage and reconciled totals before saving a final deliverable. If a workbook has several dependent queries, define their order and acceptance conditions explicitly instead of assuming one aggregate call proves the entire chain.

try {
    $ok = $clean.QueryTable.Refresh($false)
    if (-not $ok) { throw 'Refresh was cancelled' }
} catch {
    throw ('Query refresh failed; do not publish this report: ' + $_.Exception.Message)
}
# Validate the expected batch and financial result here.
# Save and release only after those checks pass.

Reproduce the failure without damaging the source

Download the result report, test script and original expense workbook. Upload the original as expense-original.xlsx in a fresh scratch directory. The script writes a separate missing-source.xlsx whose broken source is deliberate. Treat that output as a failed test artifact, never as a deliverable.

The Panes Office environment lets an agent execute these native application checks and download the resulting files. The environment gives you the application boundary; your workflow still needs an honest definition of done and independent verification.

Our workbook validation guide covers the final source-to-report checks. Retain the failed attempt in your run ledger: deleting failures would make a reliability report impossible to assess.