Published 17 August 2026

How to Connect Excel to Xero with Power Query

"Connect Excel to Xero" sounds like it should be a single setting. It isn't. Xero has no native Excel connector, and Excel has no Xero data source in the Get Data menu, so every working setup is one of three routes: you export and let Power Query clean up, you call the Xero API yourself, or you point Excel at a database that something else keeps in sync with Xero (the approach Flow takes).

This guide walks each route end to end, with the M code, and is honest about where two of them stop working. If you want the wider comparison including non-Power-Query options, start with getting Xero data into Excel.

First, the constraint that shapes everything

Xero's API uses OAuth 2.0. Access tokens are short-lived, refresh tokens rotate, and every request needs both an Authorization: Bearer header and an xero-tenant-id header naming the organisation you want.

Power Query has no way to complete that OAuth flow. Excel's built-in web connector offers Anonymous, Basic, Windows, Organizational account and API-key authentication — none of which is Xero's flow — and Excel cannot load the custom connectors (.mez files) that Power BI Desktop can. So there is no version of this where Excel signs into Xero and keeps itself signed in.

That single fact is why the three routes below exist, and why most practices end up on the first or the third.

Route 1: Export from Xero, clean with Power Query

The zero-cost route. Xero produces the report, Power Query does everything after that: strip the report furniture, set types, reshape into a tidy table, and replay those steps on every refresh.

Accounting → Reports, run your report, then Export → Excel.
Save it to a fixed path with a stable filename.
In your reporting workbook: Data → Get Data → From File → From Excel Workbook, then Transform Data.
Clean up in the editor, and load.

let
    Source = Excel.Workbook(File.Contents("C:\Reports\Xero\TB-current.xlsx"), null, true),
    Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
    Headers = Table.PromoteHeaders(Table.Skip(Sheet, 4), [PromoteAllScalars = true]),
    Rows = Table.SelectRows(Headers, each [Account] <> null),
    Typed = Table.TransformColumnTypes(Rows, {{"Debit", type number}, {"Credit", type number}})
in
    Typed

Overwrite the file next month, hit Data → Refresh All, and the whole transformation replays. Point the same pattern at a folder of monthly exports and you have a rolling multi-month history — the full walkthrough is in Xero trial balance in Excel with Power Query.

Where it stops: the export is manual, so "refreshable" only means "refreshable once someone has exported again". Historic months are frozen as files, so a back-dated journal into March never reaches your March figures unless you remember to re-export March. Per organisation, per month, that is a standing diary entry.

Route 2: Call the Xero API directly from Power Query

You can query Xero's API from M. What you cannot do is keep it working unattended.

Get an access token (Xero's OAuth playground or your own app registration), find your tenant ID, and write:

let
    Token    = "PASTE_ACCESS_TOKEN_HERE",
    TenantId = "PASTE_TENANT_ID_HERE",
    Response = Web.Contents(
        "https://api.xero.com/api.xro/2.0/Reports/TrialBalance",
        [
            Headers = [
                #"Authorization"  = "Bearer " & Token,
                #"xero-tenant-id" = TenantId,
                #"Accept"         = "application/json"
            ]
        ]
    ),
    Json    = Json.Document(Response),
    Reports = Json[Reports],
    Rows    = Reports{0}[Rows]
in
    Rows

That returns real data, and the response is deeply nested — Xero's report JSON is rows containing rows, so expect a fair amount of Table.ExpandRecordColumn work before it looks like a table.

Where it stops, and it stops hard:

The token expires. Access tokens are short-lived by design; when yours dies the query returns 401 and someone has to paste a new one into the formula bar. Refreshing tokens means an HTTP POST with a client secret — credentials you should not be storing in a workbook that gets emailed around.
Credentials live in the query. A pasted bearer token is plain text in the M code, visible to anyone with the file.
Dynamic URLs break scheduled refresh. Building a URL by concatenation trips Power Query's data-privacy and dynamic-data-source checks; the RelativePath and Query options in Web.Contents are the workaround, and it is fiddly.
Rate limits are per tenant, so a workbook that fans out across many organisations on refresh will hit them.

Route 2 is genuinely useful for a one-off pull or for proving what an endpoint returns. Treat anything beyond that as building an integration, and build it outside Excel.

Route 3: Point Power Query at a synced database

The third route moves the OAuth problem somewhere it belongs: a service holds the Xero connection, refreshes its own tokens, and writes the data into a SQL database on a schedule. Excel then connects to a plain SQL data source — something it has supported natively for years.

Data → Get Data → From Database → From SQL Server Database.
Enter server and database.
Choose Database authentication and enter your read-only credentials.
Pick a view in the Navigator, then load or transform.

let
    Source = Sql.Database("your-server.database.windows.net", "your-database"),
    TB     = Source{[Schema = "dbo", Item = "TrialBalance"]}[Data],
    Recent = Table.SelectRows(TB, each [SnapshotDate] >= #date(2025, 8, 31))
in
    Recent

Three things change materially:

Refresh is unattended. No export, no token, no diary entry. Open the workbook, refresh, done.
Query folding works. Power Query pushes that Table.SelectRows filter down into SQL, so Excel downloads only the rows you asked for rather than pulling everything and filtering locally. On multi-year, multi-organisation data that is the difference between seconds and minutes.
Multi-organisation is a column, not a folder. Every connected organisation is in the same table, so consolidations become a group-by instead of an append across files.

The trade-off is honest: you are depending on a service to keep the sync running, and on it handling back-dated journals by re-verifying historic months rather than writing each month once and never looking again. That is the specific thing worth asking any provider about, because it is the failure mode that silently produces wrong comparatives.

Choosing between them

Occasional work, one organisation, one report — Route 1. Don't over-engineer it.
Investigating what an endpoint returns, or a genuine one-off extract — Route 2, with the token pasted in and no illusion that it will survive the week.
A monthly pack, several organisations, or anything someone else has to refresh — Route 3. The moment the exporting itself is the bottleneck, no amount of Power Query cleverness downstream fixes it.

Pitfalls worth designing around

Privacy levels. Excel blocks queries that combine sources at incompatible privacy levels. If a query mysteriously refuses to fold or refresh, check Data → Get Data → Query Options → Privacy before rewriting the query.
Credential scope. Excel stores data-source credentials per user, not in the file. A workbook that refreshes on your machine will prompt a colleague for their own credentials — which is correct behaviour, but surprises people at month-end.
Map by code, not by name. Whichever route you take, join to account codes. Account names get edited in Xero, and a report layer keyed on names breaks silently when they do.
Be explicit about the current month. A month still in progress changes daily. Label it as provisional in the layout so nobody circulates a pack implying it's final.

Where Flow fits

Flow is Route 3 as a service: it connects to your Xero organisations, syncs trial balance snapshots, invoices, credit notes and the chart of accounts into an Azure SQL database daily, and re-verifies historic months so back-dated journals land in the right period. You get read-only credentials scoped to your own organisations and an Excel Power Query template, so the connection above is already written for you.

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.