x0
Power BI starter pack
Ready-made Power Query M queries for Power BI Desktop against your x0 OData cache.
Download
| Resource | Path |
|---|---|
Starter zip (Accounts, Contacts, Invoices, Payments .pq + README) |
/power-bi/starter.zip (login required) |
| OData API reference | odata.md |
| Excel desktop (same Bearer pattern) | excel-desktop.md |
| SQL warehouse (Professional+) | postgres.md |
Dashboard → Database or Excel panels also link to the zip.
Before you connect
- Issue an API key on the dashboard with read scopes (
xero.accounts.read,xero.contacts.read,xero.invoices.read, …). - Note your Xero tenant GUID if the account has multiple organisations (
X-Xero-Tenant-Idheader). - Prefer Bearer auth — do not embed the key in a shared
.pbixyou email around.
Parameters (recommended)
In Power BI Desktop: Home → Transform data → Manage parameters
| Name | Type | Example |
|---|---|---|
ApiBaseUrl |
Text | https://api.x0.co.nz/odata/v1 |
BearerToken |
Text | x0_live_….your_secret |
TenantId |
Text | (optional) tenant GUID |
Then open each .pq from the zip in Advanced editor as a new blank query.
OData paging
When a table is larger than one page, use $top on the URL. x0 returns @odata.nextLink when more rows exist — Power Query's OData.Feed follows it automatically.
Example first page:
GET /odata/v1/Invoices?$top=100&$count=true
Response includes @odata.nextLink with $skip advanced — no manual skip loop required in Power BI.
Default $orderby is applied when you omit it (e.g. Invoices by UpdatedDateUTC DESC). See odata.md.
Two paths: OData vs SQL
| Path | Tier | Best for |
|---|---|---|
| OData (this pack) | Standard+ | Same model as Excel; no warehouse setup |
| Postgres warehouse | Professional+ | Native SQL, multiple SCRAM logins, heavy joins |
For warehouse setup, create a SQL login labelled Power BI on the dashboard Database panel.
Refresh behaviour
Power BI schedules refresh against the cache, not live Xero. After posting in Xero, use portal Refresh or wait for the cache TTL (data-freshness.md). Do not set aggressive refresh on every panel if you also pass refresh=true — that burns Xero quota.
Related
- downstream-showcase.md — Grafana, Metabase, and other BI tools
- glossary.md — OData, Power Query, BI terms