Why Do Dates, Signs and Account Codes Break When You Load Xero Data into Power Query?
The failures that cost the most time in a Xero reporting workbook are not the ones that stop the refresh. A missing column or an expired credential announces itself. The expensive ones load without complaint and produce a report that is slightly, plausibly wrong: March figures sitting in the third of December, income showing as a cost, an account that dropped out of a subtotal because its code no longer matched anything.
Nearly all of those come from three places. Dates, signs and account codes each have a natural-looking default in Power Query that is wrong for accounting data, and each is fixed the same way: decide the rule once, at the point the data enters your model, and make the query check it. The examples below assume Xero data arriving as exports or from a database (a synced database being the route Flow takes), but the rules are the same either way.
Dates: locale, type and month-end alignment
Days and months swap, but only some of them
Power Query's automatic Changed Type step converts text to dates using the regional settings of the machine running the refresh, unless a fixed locale has been set for the workbook under Data → Get Data → Query Options → Current Workbook → Regional Settings. A UK export saying 03/04/2026 means 3 April. On a machine set to US settings it becomes 4 March, and nothing errors.
The dangerous part is that the failure is partial. Any date with a day of 13 or above cannot be a valid month, so 15/04/2026 fails to convert on that machine and shows as an error. Dates from the first twelve days of each month convert to the wrong date without a trace. You notice the errors, fix or filter them, and the silent swaps stay in the data.
The fix is to state the culture on every date conversion rather than inheriting it. In the editor, right-click the column header and choose Change Type → Using Locale, then pick Date and English (United Kingdom). In M, that is the third argument:
Typed = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")
Do this even if everyone in the practice is on UK settings today. The workbook will eventually be opened by a contractor, a client or a new laptop with a default install.
One column, three kinds of value
Exports that have been opened and resaved in Excel often arrive with a mixed date column: some cells real dates, some text, and occasionally a bare serial number such as 46112 where a cell lost its date format. Data from a database or API adds a fourth kind, datetimes with a time component. A blanket type change handles whichever kind it expects and errors on the rest.
A small conversion function handles every case explicitly:
let
ToDate = (v) as nullable date =>
if v = null or v = "" then null
else if v is date then v
else if v is datetime then DateTime.Date(v)
else if v is datetimezone then DateTime.Date(DateTimeZone.RemoveZone(v))
else if v is number then Date.From(v)
else Date.FromText(Text.Trim(Text.From(v)), "en-GB"),
Source = TrialBalanceRaw,
Dated = Table.TransformColumns(Source, {{"Date", ToDate, type nullable date}}),
PeriodEnd = Table.AddColumn(Dated, "PeriodEnd",
each if [Date] = null then null else Date.EndOfMonth([Date]), type nullable date)
in
PeriodEnd
Date.From on a number treats it as an Excel serial date, which is what a stray 46112 is.
Normalise every period to its month end
The last step above matters more than it looks. Two sources can agree on the month and disagree on the day: one labels March as 31 March, another as 1 March, a third carries a timestamp. Joins and lookups on date then match nothing. Add a PeriodEnd column with Date.EndOfMonth in every query that carries a period, and join on that column only. If a period genuinely needs a different day, such as a 4-4-5 calendar, put that rule in a calendar table and join to it rather than adjusting dates inside each query.
Signs: pick one convention at the boundary
Debits and credits arrive in different shapes
A trial balance export gives you separate Debit and Credit columns. A database view or API pull usually gives one signed amount. A P&L or balance sheet report export gives you figures in reading convention: income shown positive, expenses shown positive, liabilities shown positive. All three are correct for their purpose, and combining any two without converting first produces nonsense that still adds up to something.
Choose one convention for everything below the boundary. Debit positive, credit negative is the natural choice because it is how a trial balance nets to zero, and it lets the query test that it does:
Z = (x) => if x = null then 0 else x,
Net = Table.AddColumn(Typed, "Net", each Z([Debit]) - Z([Credit]), type number)
Coalescing nulls to zero before the subtraction is not tidiness. null - 100 is null in M, so an account with no debit figure would lose its credit entirely. The transformation steps for a Xero trial balance export cover the same coalescing for the year-to-date columns and before an unpivot.
Avoid building from report-style exports at all where you can. A trial balance carries the same accounts in a convention you can verify. A P&L export carries the same numbers with the signs already rearranged for a reader, and reversing that reliably means knowing which section every line came from.
Credit notes are positive documents
The same issue appears at document level. Xero holds credit notes as their own document type with positive totals, so a credit note for £500 does not show as -500. Append credit notes to invoices for a sales or ageing analysis and the credit notes add to revenue instead of reducing it. Negate them on the way in:
Signed = Table.AddColumn(Documents, "SignedTotal",
each if [DocumentType] = "CreditNote" then -[Total] else [Total], type number)
Check the column holding the document type in your own source, since the name and the values vary between export, connector and database.
Flip signs for presentation, not in the data
Reports still need income shown positive. Do that in the reporting layer, from a sign held against each reporting line in your mapping table, and never by editing the underlying amounts. The join uses the AccountKey column built in the account codes section below:
Joined = Table.NestedJoin(Net, {"AccountKey"}, AccountMap, {"AccountKey"}, "Map", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Joined, "Map", {"ReportLine", "Sign"}),
Reported = Table.AddColumn(Expanded, "ReportAmount", each [Net] * [Sign], type nullable number)
Sign is -1 for income, liabilities and equity lines and 1 for the rest. The data stays in one convention, and every presentation decision lives in one table you can read.
Account codes: text, not numbers
Leading zeros and type detection
Power Query detects column types from a sample of the first rows, 200 by default. A column of codes like 200, 400 and 610 looks numeric, so it is typed as a number. Two things then go wrong. Any code with a leading zero, such as 090, becomes 90. And if a code containing a letter appears below the sampled rows, that cell errors on refresh even though the query worked when you built it.
Type the code column as text explicitly, and delete the automatic Changed Type step that the editor adds after promoting headers. To stop it being added at all, go to Data → Get Data → Query Options → Current Workbook → Data Load and turn off type detection for unstructured sources. If the zeros were already lost before the data reached you (a code stored as a number in the source workbook), they cannot be recovered from the value alone. Text.PadStart([AccountCode], 3, "0") restores them only if every code in your chart is the same length.
Blank codes and stray spaces
Xero does not insist on a code for every account, and bank accounts are commonly left without one. A blank code joined to a mapping table matches nothing, or worse, every blank matches the same row. Build a key that falls back to the name, and trim while you are there, because a code with a trailing space does not match the same code without one:
AsText = Table.TransformColumnTypes(Source, {{"AccountCode", type text}}),
Trimmed = Table.TransformColumns(AsText, {{"AccountCode",
each if _ = null then null else Text.Trim(_), type nullable text}}),
Keyed = Table.AddColumn(Trimmed, "AccountKey",
each if [AccountCode] = null or [AccountCode] = ""
then "NAME:" & Text.Trim([AccountName])
else [AccountCode], type text)
Type the key column the same way in the mapping table. A join between a text "200" and a number 200 does not error in Power Query. It returns no match, and the account drops out of the report.
Codes that change
Account codes can be edited in Xero, and charts get renumbered when a practice tidies a client's ledger. A mapping table keyed by code then carries a stale row for the old code and nothing for the new one. If your source offers Xero's internal account ID, key on that and treat the code as a label. If it does not, the next section's check is what tells you a code changed.
Make the query catch all three
Each fix above removes a cause. The checks below catch whatever slips past them, and they fail the refresh with a sentence rather than loading a wrong table:
Unmapped = List.Distinct(Table.SelectRows(Reported, each [Sign] = null)[AccountKey]),
Footing = Number.Round(List.Sum(Reported[Net]), 2),
Checked = if not List.IsEmpty(Unmapped) then
error "Unmapped accounts: " & Text.Combine(Unmapped, ", ")
else if Footing <> 0 then
error "Trial balance does not net to zero: " & Text.From(Footing)
else Reported
A changed code shows up as an unmapped account. A sign flipped on the wrong side, or a total row that survived a filter, shows up as a trial balance that no longer nets to zero. Run the footing test per period if your table holds more than one. For the errors that do stop a refresh, why your Xero Power Query refresh fails goes through each message in turn, and the base clean-up of a trial balance export is in Xero trial balance in Excel with Power Query.
Where Flow fits
Flow syncs your connected Xero organisations into an Azure SQL database and serves them through views with fixed column types: dates as dates, amounts as signed numbers, account codes as text. Most of the conversions above are already done before Power Query sees the data, and the mapping and footing checks are what remain.
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.