Xero Power BI Connector: Step-by-Step Setup Guide (2026)

Xero has a native Power BI connector — and for small organisations with modest transaction volumes, it works passably. But for finance teams running meaningful reporting workloads, the native connector has three structural problems that compound over time: API rate limiting that causes partial refreshes, missing line-item detail that blocks account-level and product-level analysis, and OAuth token maintenance that breaks unattended scheduled refresh.

This guide covers the reliable alternative: syncing Xero data to a SQL database with SyncTools, and connecting Power BI to that database via its native SQL connector — no rate limits, full line-item detail, and no OAuth token to manually refresh.

TL;DR: SyncTools syncs Xero records to a PostgreSQL, MySQL, SQL Server, or Azure SQL database on an automated schedule. Power BI Desktop connects to that database via its native SQL connector. Power BI Service refreshes on schedule without hitting Xero API rate limits. Full line-item detail included. Flat $50/month, unlimited rows.

Related: Xero Power BI Connector landing page — architecture overview, supported entities, and pricing.

Why the Native Xero Power BI Connector Falls Short for Serious Reporting

Xero’s Power BI connector is functional for simple use cases, but three limitations make it problematic for organisations doing real financial reporting.

API rate limiting causes partial refreshes. Xero enforces a limit of 60 API calls per minute per connected application. For a small organisation with one or two years of transaction history, a refresh may stay under this limit. For organisations with five or more years of history, multiple entity types, and multi-currency transactions, a single Power BI refresh regularly exceeds the limit. When Xero throttles the request, Power BI receives an incomplete dataset — sometimes silently, sometimes with a generic error. The result is dashboards that appear to have refreshed but contain partial data for recent periods.

Line-item detail is missing. The native connector returns invoice and bill headers — total amounts, dates, contact names — but does not expose line-item rows. This means you cannot break down revenue by product, by account code, by tax rate, or by Xero tracking category at the transaction line level. Any finance team that needs a proper P&L, margin analysis, or tax reporting breakdown hits this ceiling immediately.

OAuth token maintenance breaks unattended refresh. The Xero connector authenticates with a user-level OAuth 2.0 token. These tokens expire and require periodic re-authorisation through an interactive browser flow — which is not compatible with unattended scheduled refresh in Power BI Service. Finance teams discover the problem when dashboards stop updating, trace it to an expired token, and have to manually re-authenticate from a desktop machine.

The database sync approach avoids all three. SyncTools maintains a persistent service-level connection to Xero, handles API paging and rate limit backoff internally, and pulls full line-item detail into your database. Power BI reads from the database — no live Xero API calls at refresh time, no rate limits, no OAuth sessions to maintain.

What Xero Data Syncs to Your Database

SyncTools structures Xero data into query-ready SQL tables with header and line-item detail. You get normalised relational tables ready for Power BI DAX immediately.

Xero Entity	SQL Tables	Power BI Use Case
Invoices	invoices, invoice_lines	AR aging, revenue by account, product, and tracking category
Credit Notes	credit_notes, credit_note_lines	Revenue adjustments, returns analysis
Bills	bills, bill_lines	AP aging, spend by supplier and account
Contacts	contacts	Customer and supplier dimension
Chart of Accounts	accounts	Account dimension for P&L and balance sheet
Bank Transactions	bank_transactions	Bank feed, cash position
Manual Journals	manual_journals, journal_lines	Manual adjustments, accruals
Tracking Categories	tracking_categories, tracking_options	Segment dimension (department, project, location)
Items	items	Product/service dimension
Overpayments	overpayments	Unallocated cash receipts
Prepayments	prepayments	Advance payments

Xero tracking categories sync as dimension columns. Tracking category 1 and tracking category 2 values are included on line-item rows, enabling segment-level P&L and cost-centre reporting in Power BI.

Multi-currency is handled. Xero stores amounts in both original currency and the organisation’s base currency. SyncTools syncs both, so you can report in either currency in Power BI without manual exchange rate lookups.

Step 1: Connect Xero to SyncTools and Configure Your Sync

Sign up at synctools.io and select Xero as your source. Authorization uses Xero’s standard OAuth 2.0 flow — SyncTools requests read-only access to your Xero organisation. No Xero partner credentials, no developer app registration, no Xero Premium subscription required beyond the authorizing user being a Xero organisation administrator.

In the SyncTools dashboard, configure:

• Which entities to sync: For Power BI financial reporting, the minimum recommended set is Invoices + lines, Bills + lines, Contacts, Accounts, Bank Transactions, and Journal Lines.
• Tracking categories: Select which tracking categories to include as columns on line-item tables.
• Historical date range: Set how far back to sync on the initial run. SyncTools handles Xero’s pagination and rate limits automatically during the historical sync.
• Sync schedule: Hourly sync keeps dashboards current. Daily sync suits end-of-day reporting.

The initial sync handles Xero API pagination and rate limit backoff automatically. A 5-year history with 80,000 transactions typically completes in 1–2 hours depending on Xero API throughput.

Step 2: Configure Your Destination Database

SyncTools supports four destination options:

Azure SQL — Recommended for Power BI teams on Microsoft Azure. Power BI Service reaches Azure SQL directly for scheduled refresh without a gateway.

PostgreSQL — Works with cloud-hosted PostgreSQL (Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL). Supported natively by Power BI for gateway-free cloud refresh.

SQL Server — On-premises SQL Server requires the standard Power BI on-premises data gateway for cloud-scheduled refresh.

SyncTools-hosted database — Zero infrastructure setup. SyncTools provides a cloud-hosted database with the connection string pre-configured. Best for teams without existing database infrastructure.

SyncTools creates and maintains all table schemas automatically. New Xero entity types supported by SyncTools are added to your database without manual schema changes.

Step 3: Connect Power BI Desktop to the Database

Open Power BI Desktop → Get Data. Select the connector matching your database:

• Azure SQL: Use the Azure SQL Database connector.
• PostgreSQL: Use the PostgreSQL connector.
• SQL Server: Use the SQL Server connector.

Enter your server hostname, port, and database name from SyncTools. Load the tables for your report. The recommended initial import for Xero financial reporting:

invoices
invoice_lines
bills
bill_lines
contacts
accounts
bank_transactions
journal_lines
tracking_categories

Build relationships in Power BI’s model view:

• invoice_lines.invoice_id → invoices.id
• bill_lines.bill_id → bills.id
• invoice_lines.account_code → accounts.code
• invoices.contact_id → contacts.id
• bills.contact_id → contacts.id
• journal_lines.account_code → accounts.code

Common Xero Power BI DAX measures:

• Total Revenue: SUM(invoices[sub_total])
• Total AP: SUM(bills[sub_total])
• Gross Margin %: ([Total Revenue] - [Cost of Sales]) / [Total Revenue]
• AR Days Outstanding: AVERAGEX(FILTER(invoices, invoices[status]="AUTHORISED"), DATEDIFF(invoices[date], TODAY(), DAY))

Step 4: Publish to Power BI Service and Schedule Refresh

Publish your report to Power BI Service from File → Publish. In the dataset settings, configure data source credentials for your SQL database.

For Azure SQL and cloud-hosted PostgreSQL: Power BI Service connects directly. Set your refresh schedule under Scheduled refresh — up to 8 refreshes per day on Power BI Pro, up to 48 per day on Premium. No gateway configuration needed.

For on-premises SQL Server: Install the standard Power BI on-premises data gateway. Configure the gateway connection in the dataset settings before enabling scheduled refresh.

Your Xero dashboards in Power BI Service update automatically on schedule, pulling the latest records SyncTools has written to the database since the previous run — no Xero API calls, no rate limits, no token maintenance.

Common Xero Power BI Report Structures

Once Xero data is in a SQL database, Power BI can build the complete suite of financial reports:

Income Statement (P&L): Filter accounts on Revenue and Expense types. Join invoice_lines and bill_lines to accounts via account code. Pivot by month. Add tracking category slicers for department or project P&L.

Balance Sheet: Filter accounts on Asset, Liability, and Equity types. Use journal lines with a date filter for end-of-period balances. CALCULATE with LASTDATE for point-in-time balance snapshots.

AR Aging: Join invoices to contacts, filter for status = AUTHORISED (open), calculate days from due_date to today, bucket into 0–30, 31–60, 61–90, 90+ columns.

AP Aging: Same structure using bills and contacts filtered to supplier type.

Revenue by Product and Account: Join invoice_lines to invoices, contacts, items, and accounts. Pivot by month. Slice by tracking category for segment analysis.

Bank Reconciliation Status: Join bank_transactions to bank_accounts. Filter unreconciled transactions. Show cash position by account and date.

Native Xero Connector vs SyncTools: Side-by-Side

	Native Xero Power BI Connector	SyncTools
API rate limits at refresh time?	Yes — 60 calls/minute, causes partial refreshes	No — data already in your database
Line-item detail on invoices and bills?	No	Yes
Xero tracking categories in Power BI?	No	Yes — as dimension columns
OAuth token maintenance?	Yes — manual re-authentication required	No — service-level auth
Gateway required for Power BI Service?	Yes — on-premises gateway	No (for cloud databases)
Works with Tableau, Metabase, Looker?	No	Yes — any SQL tool
Price	Free (with limitations)	$50/month flat, unlimited rows

---

Get Started

Start a free SyncTools trial → — no credit card required, setup in under 15 minutes.

Also using QuickBooks, Sage, MYOB, or NetSuite alongside Xero? SyncTools can sync multiple accounting systems into the same database for a consolidated BI layer.

Related reading:

• Xero Power BI Connector overview — architecture, supported entities, and pricing
• Xero Tableau Connector — same sync approach for Tableau instead of Power BI
• QuickBooks Power BI connector guide — same database-sync approach for QuickBooks Online
• NetSuite Power BI connector guide — how the database-sync approach works for NetSuite