Refresh in Excel, Power BI Desktop and the service (concept)
Anticipate why a query that refreshes on your machine fails elsewhere, and add checks that report it.
~9 min read · intermediate · Module 19: Refresh robustness and performance concepts
By Tahir Nas · Examples run in our simulator · Updated
This is a concept lesson. The practice simulator doesn't run this feature; the explanation describes how real Power Query hosts behave, and any code the simulator can't run is labelled. See the compatibility page.
Before this: Power Query in Excel and Power BI: same engine, different hosts, Parameters: one value, many queries
A query is only as portable as its sources. On your laptop, C:\Exports\orders.csv exists and your credentials are cached. On a colleague's machine, a shared drive letter may differ. In the Power BI service, there's no C: drive at all: on-premises files and databases are reached through a gateway, with stored credentials.
Where refresh runs
| Host | Refresh runs on | Typical failure |
|---|---|---|
| Excel (desktop) | The user's PC, on demand or on open | A path or credential only one user has |
| Power BI Desktop | The author's PC | Works for the author, fails after publishing |
| Power BI service | Microsoft's cloud, on a schedule | No gateway for an on-premises source; expired credentials |
| This simulator | Your browser, against fixtures | Unsupported connector or feature (reported, never approximated) |
A local folder source is the most common portability trap:
let
Source = Folder.Files("C:\Exports\Orders")
in
SourceNot run by the simulator
Folder.Files is not supported in the Power Query Exercises simulator. See the compatibility page for the supported subset. This code is shown for real hosts only; see the simulator compatibility page.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Make failures visible
A refresh that 'succeeds' with half the data is worse than one that fails. A small check query that reports row counts and the latest date gives whoever opens the report a reason to trust it, or to ask:
let
Orders = #table({"OrderID", "OrderDate"}, {{"A-1", #date(2026, 10, 30)}, {"A-2", #date(2026, 10, 31)}, {"A-3", #date(2026, 10, 29)}}),
Check = #table({"Check", "Value"}, {
{"Rows loaded", Text.From(Table.RowCount(Orders))},
{"Latest order date", Date.ToText(List.Max(Orders[OrderDate]), [Format = "yyyy-MM-dd"])}
})
in
CheckResult
| Check | Value |
|---|---|
| Rows loaded | 3 |
| Latest order date | 2026-10-31 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Portability checklist
- Keep paths and server names in parameters, not in steps.
- Prefer shared locations (SharePoint, a database) over personal drives for anything that refreshes on a schedule.
- Know which sources need a gateway in the Power BI service before you publish.
- Never put credentials in query text; hosts store them separately.
Where to find this in Excel
- Excel for Windows
- Data > Refresh All; Data > Queries & Connections > right-click a query > Properties
- What you see
- Refresh All updates every query in the workbook. The Properties dialog's Usage tab holds refresh on open, refresh every n minutes and background refresh.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Refresh an external data connection in Excel (Microsoft Support).
Common mistakes
- Publishing a report whose source is a file on your own drive and expecting the service to refresh it.
- Hard-coding a path in several queries instead of one parameter.
- Treating a refresh without errors as proof that all the data arrived.
Excel, Power BI and the simulator
- Excel: Data → Queries & Connections → Properties sets refresh on open and background refresh per query.
- Power BI service: dataset settings show gateway connection, data source credentials and scheduled refresh; the refresh history shows errors.
- The simulator raises UnsupportedFeature for Folder.Files, File.Contents, SharePoint and database connectors; exercises supply fixture data instead.
Table.RowCountList.MaxNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Refresh robustness quiz