How Do You Transform a Xero Trial Balance Export in Power Query?
A Xero trial balance export is a report, laid out for a person to read. It has a title block, section headings, subtotals, a grand total and a pair of year-to-date columns sitting beside the period columns. Everything a human needs to scan it on screen is exactly what makes it awkward as data.
Power Query can turn it into a clean, long table in one query that replays every month. The basic clean-up (remove the top rows, promote headers, filter the totals) is covered in Xero trial balance in Excel with Power Query. This piece goes a level further: each transformation the export needs, why a click-through recording of it tends to break, and the M code that holds up when the export shifts slightly. If you would rather not handle exports at all, a synced database that already holds the table in this shape is the alternative (the route Flow takes).
Start by looking at what arrives
Pull the export in with Data → Get Data → From File → From Excel Workbook, pick the sheet, and click Transform Data. Before applying anything, scroll through the raw preview. On a typical export you will see:
- A title block of several rows: the report name, the organisation name and an "As at" date line.
- A header row naming the columns, usually Account, Debit, Credit, YTD Debit and YTD Credit.
- Section heading rows with a label in the first column and nothing in the numeric columns.
- A "Total" row at the end of each section, and a grand total at the bottom.
The exact layout depends on how the report was run in Xero, and a customised layout or an added comparison period will change it. Check your own file against that list before writing a line of M, because each step below assumes something about the shape.
Read the report date before throwing the title away
Most recorded queries start with Remove Top Rows, which discards the one piece of metadata you need: the date the balances relate to. Relying on the filename for the period works until someone saves TB March final v2.xlsx. The export already states the date, so read it from there:
let
Source = Excel.Workbook(File.Contents(SourcePath), null, true),
Sheet = Source{0}[Data],
FirstCol = List.Transform(Table.Column(Sheet, "Column1"),
each if _ = null then "" else Text.Trim(Text.From(_))),
AsAtLine = List.First(List.Select(FirstCol, each Text.StartsWith(_, "As at "))),
ReportDate = Date.FromText(Text.AfterDelimiter(AsAtLine, "As at "), "en-GB")
in
ReportDate
SourcePath is a text parameter holding the file path (Home → Manage Parameters → New Parameter). The "en-GB" culture matters: "31 March 2026" parses the same anywhere, but a numeric date like 03/04/2026 does not, and a colleague's machine set to US regional settings will read it as the fourth of March.
Strip the furniture by content, not by count
Table.Skip(Sheet, 4) works while the title block is four rows high. Add a line to the report header in Xero, or export from an organisation with a longer name that wraps, and the header row moves. Find it by what it says instead:
HeaderAt = List.PositionOf(FirstCol, "Account"),
Body = Table.Skip(Sheet, HeaderAt),
Promoted = Table.PromoteHeaders(Body, [PromoteAllScalars = true])
List.PositionOf returns the zero-based index of the first row whose first cell reads "Account", which is exactly the number of rows to skip. If your export labels that column differently, change the string once here rather than recounting rows every time the layout drifts.
Keep the section headings as a column
The section headings (the rows that split the report into revenue, expenses, assets and so on) are usually deleted as noise. They are more useful kept, because they give you a free classification of every account without a mapping table. The pattern is to copy each heading into a new column, fill it down, then drop the heading rows themselves:
NumCols = {"Debit", "Credit", "YTD Debit", "YTD Credit"},
IsBlank = (r as record) as logical =>
List.MatchesAll(Record.ToList(Record.SelectFields(r, NumCols)), each _ = null),
Tagged = Table.AddColumn(Promoted, "Section",
each if [Account] <> null and IsBlank(_)
and not Text.StartsWith([Account], "Total")
then [Account] else null, type text),
Filled = Table.FillDown(Tagged, {"Section"}),
Accounts = Table.SelectRows(Filled,
each [Account] <> null
and not IsBlank(_)
and not Text.StartsWith([Account], "Total"))
Test all four numeric columns, not just Debit and Credit. An account with no movement this period but a year-to-date balance has blanks in the period pair, and a two-column test would mistake it for a heading and silently reclassify every account below it.
One caution on the "Total" filter: it removes any account whose own name starts with that word. That is rare, but worth a glance down your chart of accounts once.
Split the account code from the name
Depending on the layout, the code either has its own column or sits in brackets after the name, as in Sales (200). If it is combined, split it, taking the last bracket pair so a name that contains brackets of its own survives:
WithCode = Table.AddColumn(Accounts, "AccountCode",
each if Text.EndsWith(Text.Trim([Account]), ")")
then Text.BetweenDelimiters([Account], "(", ")",
{0, RelativePosition.FromEnd}, 0)
else null, type text),
WithName = Table.AddColumn(WithCode, "AccountName",
each if [AccountCode] = null then Text.Trim([Account])
else Text.Trim(Text.BeforeDelimiter([Account], "(",
{0, RelativePosition.FromEnd})), type text)
Keep AccountCode as text, never as a number. Codes like 090 lose their leading zero as numbers, and some codes are not numeric at all. Expect a few nulls too: Xero does not force a code on every account, and bank accounts are the usual gap. Report layers that key on code need a fallback for those rows, typically the name.
Set types with a UK locale, and coalesce before you unpivot
Promoted headers leave every column as type any. Type them explicitly and pass the culture, for the same reason as the date:
Typed = Table.TransformColumnTypes(WithName,
List.Transform(NumCols, each {_, type number}), "en-GB"),
Z = (x) => if x = null then 0 else x,
Nets = Table.AddColumn(
Table.AddColumn(Typed, "Period", each Z([Debit]) - Z([Credit]), type number),
"YTD", each Z([#"YTD Debit"]) - Z([#"YTD Credit"]), type number)
Debits come out positive and credits negative, one signed figure per basis. Replacing nulls with zero here is not cosmetic: the next step, unpivoting, discards null cells entirely, so any account with a blank period figure would lose that row without warning.
Unpivot to a long table
Two signed columns side by side still bake the report layout into the data. A long table, with the basis as a column, is what pivots, SUMIFS and dynamic arrays want:
Slim = Table.SelectColumns(Nets,
{"Section", "AccountCode", "AccountName", "Period", "YTD"}),
Long = Table.Unpivot(Slim, {"Period", "YTD"}, "Basis", "Amount"),
Dated = Table.AddColumn(Long, "AsAt", each ReportDate, type date)
ReportDate is the date read from the title block in the first step. Check on your own export what the period pair represents for balance sheet accounts against a balance you know, before a report relies on it. The distinction between monthly movement and year-to-date is the one that most often catches out a balance sheet built from a trial balance.
Make the query prove it foots
A trial balance that does not net to zero has been mangled somewhere: a section dropped, a total row that slipped through the filter, a sign flipped. Assert it at the end of the query so the refresh fails loudly instead of loading a plausible wrong table:
Footing = Table.Group(Dated, {"Basis"},
{{"Total", each Number.Round(List.Sum([Amount]), 2), type number}}),
Checked = if List.MatchesAll(Footing[Total], each _ = 0)
then Dated
else error "Trial balance does not foot. Totals by basis: "
& Text.Combine(List.Transform(Footing[Total], Text.From), ", ")
If the grand total row ever survives the filter, the sum doubles and this check catches it immediately. For the other refresh failures you are likely to meet with exports, Why does my Xero Power Query refresh fail? walks through each error message in turn.
Turn it into a function for a folder of months
Once the query is right for one file, right-click it in the Queries pane and choose Create Function, with the file contents as the parameter. The same transformation then applies to every export in a folder, and because each file now carries its own AsAt date, the combined table is correctly dated no matter what the files are called. The folder pattern, including the lock-file filter, is set out in getting historical monthly trial balance snapshots from Xero.
The general principle behind every step above: record intent, not positions. "Skip to the row that says Account" survives a layout change; "skip four rows" does not.
Where Flow fits
Flow syncs your connected Xero organisations into an Azure SQL database, where the trial balance already sits as a long table of accounts by month-end, with account codes, names and amounts in fixed columns. There is no export to reshape, so none of the transformations above need maintaining.
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.