Automatic Power Query Refresh and Error Monitoring
A report can open normally even when its latest refresh failed. The numbers are still there, but they may describe yesterday. I build monitoring screens that separate the last attempt from the last successful refresh, show the age of the published data and identify who needs to investigate.
This is useful when several people rely on a report but only one person understands its connections. The monitoring screen gives the team a clear status without asking everyone to inspect the workbook.
Example: yesterday’s result remains visible
In the illustrative screen, the last successful refresh finished yesterday at 09:00. Today’s attempt failed because the connection credentials expired. The report remains available with a visible “Data is stale” label.
After the credentials are repaired, a retry completes successfully at 10:15. Only then does the last-success timestamp change. Clicking Retry is not itself a successful refresh. The source must be read, the transformation completed and the expected output checks passed.
The failed attempt remains in the history. Otherwise, replacing an error with a green status would remove the information needed to understand recurring failures.
When existing tools are enough
Excel connection properties support refresh on opening and at intervals while the workbook is open. These controls may be enough for a report monitored by its owner. I check the connection, credentials and source date first.
Refreshing a closed workbook is a separate requirement. Scheduling a Power Automate flow does not prove that every Power Query connection will refresh. Microsoft documents that Office Scripts in a flow cannot refresh most data. The refresh-all-connections method only refreshes Power BI sources; other sources can return success without changing any data.
A custom screen is useful when several reports need oversight, failures need owners or stale results must be held. I test the supported execution route with sample data before including it in scope. Monitoring does not make an unsupported connection refreshable.
Status rules that prevent misleading results
A queued job is different from a running job. A job that takes longer than expected must not quietly turn into a success. Missing source files also need a different explanation from expired access credentials.
I define freshness separately from execution success. An import might complete correctly while reading an old file. In that case, the connection worked but the business data is still late.
If a user requests another refresh while one is running, the application shows the active job or queues the request. Two overlapping runs must not publish conflicting results. Where a report combines several sources, its status reflects the required sources rather than whichever source finished most recently.
These rules can support an existing Excel dashboard. They explain which reporting period the totals represent.
Implementation and ongoing ownership
I start with the workbooks, connection owners, expected schedule and definition of “current”. The agreed delivery can include a status screen, run history, authorised retry controls and failure notifications.
Testing includes expired credentials and missing files as well as a successful run. The handover identifies who renews access, who investigates failures and how a new report is added. Source structure changes remain maintenance work; the monitor cannot infer every new business meaning from a renamed column.
If the data should be collected separately from the workbook, a scheduled API import may provide a clearer operating model.
Frequently Asked Questions
Can the monitor repair refresh failures automatically?
Some temporary failures can be retried within defined limits. Missing permissions or changed source structures usually require someone to fix the cause. Retry limits and escalation rules are agreed in advance.
Must the old report become unavailable?
Not necessarily. It can remain readable with its data date clearly displayed. A critical report can instead be held until the required sources are current.
Discuss your workflow
Send me the report’s current setup and explain who depends on it. I will first assess the existing refresh route, then define the monitoring screen that the team needs.
How do I work?
Work-hour packages
Tell me about your business and what you need on WhatsApp or by phone; we agree on a work block of somewhere between 5 and 40 hours and I start doing what is needed. What you get is not a calendar window — it is net working hours spent directly on your business. I can use 40 hours working three days straight, or deliberately spread it out and finish it over a year — whatever the work calls for. I do not spend my time estimating how long something will take, I spend it doing the work; every hour I spend leaves a lasting result in your business. We keep going for as long as you find it useful.
Why not monthly?
Is 40 hours a week?
On a fixed monthly fee both sides are watching the wrong thing: you watch a fixed cost, the other side watches how many more contracts like this they can line up. Working by the hour puts us both in front of the same thing — the real value of the work that got done. I know not having an upfront answer to "how much will this cost in total?" is the natural cost of this model. But instead of paying a fixed fee for months to a setup that is not producing results, you only pay for the qualified time spent on your work — and you see exactly where that time went, start to finish. That is why my clients keep coming back.