Why Does My Xero Power Query Refresh Fail? Common Errors and Fixes
A Xero reporting workbook that has refreshed cleanly for eight months will eventually break on a Tuesday morning, and it will almost never be Power Query's fault. Something upstream moved. A column got renamed in an export, a token expired, an account was added, someone opened the source file and left it open.
The errors themselves are terse and, once you have seen each one twice, completely diagnostic. Each section below is one error message, what it actually means, and what to change. If your workbook is not connected to Xero yet, how to connect Excel to Xero with Power Query covers the routes available first (one of them being a synced database, the approach Flow takes).
Read the failing step before you change anything
One habit saves most of the time spent on this. When a refresh fails, Excel shows the message but not the context. Open Data → Queries & Connections, double-click the failing query, and look at the step highlighted in the Applied Steps pane on the right. Power Query names the step that threw, and that name is usually the whole diagnosis.
A failure in an early step (Source, Navigation) means the data never arrived: a path, a permission, a credential. A failure in a later step (Changed Type, Removed Columns, Renamed Columns) means the data arrived in a shape your query was not written for. Those are two entirely different investigations, and the step name tells you which one you are in before you have read the message properly.
"The column 'Total' of the table wasn't found"
The most common refresh failure on Xero exports, and the easiest to make permanent.
Power Query records column names literally. When you clicked a column to remove, rename or retype it during setup, the name went into the M code as a string. Change the report layout in Xero, switch a trial balance from one comparison period to two, or export from a different Xero organisation whose report has been customised, and the column your query is looking for is not there. Every step after the missing one fails too, which is why one renamed column produces a wall of errors.
Two fixes, and you want both. The forgiving one is to tell the step not to care when a column is absent:
Renamed = Table.RenameColumns(
Promoted,
{{"Account Code", "AccountCode"}, {"Account Name", "AccountName"}},
MissingField.Ignore
)
That stops the refresh dying, which is right for a cosmetic column and wrong for one you actually report on. So pair it with an explicit check that fails loudly and names the problem:
let
Source = Excel.Workbook(File.Contents(SourcePath), null, true),
Sheet = Source{[Item = "Trial Balance", Kind = "Sheet"]}[Data],
Promoted = Table.PromoteHeaders(Sheet, [PromoteAllScalars = true]),
Wanted = {"Account Code", "Account Name", "Debit", "Credit"},
Missing = List.Difference(Wanted, Table.ColumnNames(Promoted)),
Checked = if List.IsEmpty(Missing)
then Table.SelectColumns(Promoted, Wanted)
else error "Xero export is missing columns: " & Text.Combine(Missing, ", ")
in
Checked
The refresh still stops, but it stops with a sentence naming the columns instead of an internal step name. Hand that message to whoever exported the file and they can fix it without opening Power Query. The cleaning steps a Xero trial balance export needs are covered in Xero trial balance in Excel with Power Query; this check belongs at the top of them.
"The key didn't match any rows in the table"
This is the navigation step, not the data. It means the sheet, table or database object your query asks for by name does not exist in the source.
In a workbook source, the usual cause is that the export landed with a different sheet name, or the query was built against a named table (Kind = "Table") and the new file only has a sheet. In a SQL source, it means the view or table name changed. Open the Source step's output, look at what the source genuinely contains, and re-point the navigation. If your exports arrive with slightly varying sheet names, take the first sheet positionally rather than by name:
Sheet = Source{0}[Data]
Positional navigation is fragile in a different way, so only reach for it where the export reliably has one sheet.
"DataFormat.Error: We couldn't convert to Number"
A Xero export column that looks numeric contains something that is not: a thousands separator in a text-formatted cell, a bracketed negative, a currency symbol, a hyphen standing in for nil, or a stray total row carrying the word "Total". The Changed Type step converts the whole column or nothing.
Handle the conversion yourself rather than letting the automatic type change guess:
let
ToNumber = (v) as nullable number =>
if v = null or v = "" then null
else if v is number then v
else let
Stripped = Text.Replace(Text.Replace(Text.Replace(
Text.Trim(v), ",", ""), "(", "-"), ")", "")
in
try Number.FromText(Stripped) otherwise null,
Source = TrialBalanceExport,
Amounts = Table.TransformColumns(Source, {{"Debit", ToNumber, type number},
{"Credit", ToNumber, type number}})
in
Amounts
One warning about otherwise null. It converts a hard failure into a silent nil, so anything it swallows disappears from your totals without complaint. Add a footing check on the loaded table so a swallowed value shows up as a trial balance that no longer nets to zero. An error you can see beats a refresh that succeeds and is wrong.
Credentials and authentication
Several different messages land here: "The credentials provided for the source are invalid", "Access to the resource is forbidden", or a sign-in prompt that reappears every refresh.
Excel stores credentials per data source, per Windows user, outside the workbook. Fix them at Data → Get Data → Data Source Settings, select the source, then Edit Permissions. If the stored credential is stale rather than wrong, Clear Permissions and refresh once to be prompted fresh. For an Azure SQL source, check the server firewall allows the machine you are sitting at before assuming the password is the problem, because a blocked connection often surfaces as an authentication message rather than a network one.
Where Xero sits behind an OAuth connector, an expired or revoked token cannot be repaired from inside Excel. The connector itself has to be reauthorised against Xero, and the refresh will keep failing until it is. Worth knowing before you spend twenty minutes on Data Source Settings.
"Formula.Firewall: Query references other queries or steps, so it may not directly access a data source"
The message that sends most people to a forum. It does not mean your query is wrong. It means Power Query cannot prove your query is safe, so it refuses to run it.
The trigger is a data source function whose argument comes from another query: a server name, a file path, a date pulled from a settings table, a filter value read from a sheet. Power Query will not let data from one source determine what it requests from another without knowing the privacy levels of both.
There are two fixes and they are not equivalent. The structural one is to stop referencing a query and use a parameter instead: Home → Manage Parameters → New Parameter, then reference the parameter in the source step. A parameter holds a plain value rather than data read from another source, so the firewall has nothing to isolate, and this is the fix that keeps working. The blunt one is to turn the check off at Data → Get Data → Query Options → Current Workbook → Privacy, choosing to ignore privacy levels. That clears the error immediately and is reasonable when every source in the workbook is your own accounting data. Do not reach for it first, because it hides a real class of mistake in workbooks that mix client data with anything external.
The refresh succeeds and the figures are stale
Nothing fails, and the numbers are last week's. Work through four causes in this order, because the first two are far more common than the last two.
The source has not changed. An export folder still holds last month's files, or an overnight sync has not run. Surface this instead of trusting it: put the maximum period in the loaded table on the front sheet with =MAX(tb[Period]) and look at it before you read any figure below it.
The query filters to a fixed date. A hardcoded #date(2026, 3, 31) written in during setup, or a window with a literal start that has drifted out of range. Search the M for #date( and replace anything absolute with something relative to DateTime.LocalNow() or a parameter.
A pivot or chart has not caught up. Data → Refresh All refreshes connections and PivotTables, but a PivotTable reading a table that a query loads in the same pass can render against the previous state, because queries refresh in the background by default. If a pack shows stale pivots after a clean refresh, refresh once more; if that clears it, this was the cause. The permanent fix is to untick Enable background refresh in the query's Properties (right-click it in Queries & Connections), so the pivots wait for the data.
Power Query's cache. Genuinely the least likely of the four, though it does happen after a source schema change. Clear it at Data → Get Data → Query Options → Global → Data Load → Clear Cache, then refresh.
It refreshes on your machine and fails on theirs
Three reasons, all worth designing out rather than troubleshooting each time.
Credentials are per user, so a colleague opening the workbook has none for that source and will be prompted. There is no way to ship them inside the file. Second, a local path (C:\Users\yourname\Reports\) exists only for you; move the source to a network or SharePoint location and hold the path in a parameter so one edit fixes every query. Third, a database source needs the other machine's IP allowed through the firewall too.
Folder-based archives add one more, and it is the classic Monday morning failure: Excel writes a hidden lock file named ~$Workbook.xlsx whenever someone has the workbook open, and Folder.Files picks it up as data. Filter it out in the query, as the folder pattern in getting historical monthly trial balance snapshots from Xero does:
Workbooks = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")
and not Text.StartsWith([Name], "~$"))
The pattern behind most of these
Read the list back and a theme emerges. Almost every failure above is a query that hardcoded something it should have parameterised, or trusted a shape it should have checked. Column names, sheet names, paths, dates and types were all true on the day you built the query, and none of them are guaranteed to stay true.
Two habits remove most of the recurring work: put every path, server name and date boundary in a parameter rather than in a step, and make each query assert the shape it needs. A change upstream then produces a sentence you can act on rather than a step name you have to decode.
Where Flow fits
Flow syncs your connected Xero organisations into an Azure SQL database, so the Excel side of this list gets shorter: one stable set of views with fixed column names, no export files to go missing or get locked, and no report layout that can be customised underneath your query. The failure modes that remain are ordinary database ones, credentials and firewall rules, which stay fixed once fixed.
Plans start at £20/month for up to 5 organisations with 2 years of trial balance history. Plus (£40/month) covers 30 organisations with configurable 1–5 year history and real-time refresh, and Premium (£60/month) adds tracking category breakdowns across up to 60 organisations. There is a 14-day free trial, no credit card required.