How to Set Up Xero to Power BI Connection: The Complete 2026 Guide

If you’re searching for how to setup xero to power bi connection, this guide walks you through the most reliable approach: land Xero data in a secure cloud database first, then connect with Power BI’s native SQL connectors. This staging pattern avoids API throttling, supports multi-entity organisations, and enables scheduled refresh for dependable dashboards. While dataSights delivers board-ready Management Reports by default (with Excel automation for spreadsheet teams), this article focuses on the Power BI connection path and the practical steps to get it running fast and safely.

Setting up a Xero to Power BI connection requires third-party tools since Microsoft deprecated the native connector in 2019, and it is no longer supported or available in AppSource. The most reliable method involves connecting Xero to a cloud database through automated solutions, then linking Power BI to that database using standard SQL connectors. This approach handles Xero’s API rate limits of 60 calls per minute while providing near real-time data refresh capabilities through high-frequency scheduled sync.

Why the Native Xero Connector No Longer Works

The Power BI Xero connector disappeared from the service in May 2019. Microsoft announced the deprecation without providing a replacement, citing authentication issues and changes in Power BI architecture. Despite initial promises to restore functionality, Microsoft has shown no intention of rebuilding the native connector.

This leaves finance teams with a critical gap. You can’t find Xero in Power BI’s Get Data menu anymore. The old content pack that provided pre-built dashboards is gone. Your existing connections throw authentication errors. Manual CSV exports become your only option – unless you implement a proper third-party solution.

Understanding Your Connection Options

A reliable Xero→Power BI setup usually follows one of two paths: a cloud database connector that stages Xero data for stable, scalable reporting, or a custom API build that pulls data directly with more engineering overhead. Below, we compare these routes so you can balance speed-to-value, maintenance, and refresh reliability.

Cloud Database Connectors

The most reliable approach is to stage your Xero data in a cloud database first. Solutions create a dedicated SQL database that syncs with Xero via API, then Power BI connects to this database using standard connectors. This method bypasses API rate limits since Power BI queries the database, not Xero directly.

dataSights syncs multiple Xero entities to a Microsoft cloud database with automated eliminations and consolidations. Connect Power BI using the standard SQL Server connector – no custom connectors needed. Handle both small and large entity consolidations with automatic refresh schedules.

OData Feed Connections

OData providers create standardised endpoints that Power BI can consume directly. Your Xero data gets transformed into an OData-compliant format that Power BI’s built-in OData connector understands. This approach requires minimal setup but will struggle at scale and is rarely suitable for multi-entity Xero datasets. OData connections typically degrade significantly with larger transaction volumes, making them impractical for finance teams managing multiple entities or historical data spanning several years.

Custom Power BI Connectors

Some providers offer .pqx files that add Xero as a data source in Power BI Desktop. These custom connectors require an On-premises data gateway for scheduled refresh in the Power BI Service. The gateway itself does not require Premium – custom connectors with scheduled refresh can work on Pro licences with a properly configured gateway. Premium capacity may be needed for other features such as larger dataset sizes or advanced deployment scenarios, but is not a gateway prerequisite. While custom connectors provide direct connectivity, managing authentication and dealing with certificate requirements adds complexity.

Manual Export Methods

The fallback option remains manual CSV exports from Xero. Navigate to each report, export to Excel format, then import into Power BI using the Excel connector. This method requires no additional tools but eliminates any possibility of automation. You’re back to manual processes that waste 10+ hours a month.

Step-by-Step Setup Process

This section walks you through two proven implementation methods – cloud database (recommended) and custom API – from authentication to data modelling and refresh. Before you begin, confirm Xero permissions, decide your entity scope, and define refresh targets so each step maps cleanly to your reporting goals.

Method 1: Cloud Database Connection (Recommended)

• Step 1: Choose Your Integration Platform: Select a solution that creates a dedicated database for your Xero data. Look for providers that offer SQL Server or a similar relational database for staging.
• Step 2: Authenticate with Xero: Connect your Xero organisation via OAuth2. Grant read access to accounting data. For multiple entities, repeat this process for each organisation.
• Step 3: Configure Data Sync: Set refresh frequency based on your needs. Most platforms offer schedules from every 5 minutes to daily. Consider Xero’s daily limit of 5,000 API calls when setting frequency.
• Step 4: Connect Power BI to Database: Open Power BI Desktop. Select Get Data > Database > SQL Server. Enter your database credentials provided by the integration platform. Choose tables to import or use DirectQuery for near real-time data.
• Step 5: Build Your Reports: Create visualisations using live Xero data. Set up automatic refresh in Power BI Service. Share dashboards with stakeholders for up-to-date financial visibility.

Method 2: API-Based Custom Connection

• Step 1: Register Xero Application: Visit developer.xero.com and create a new app. Configure OAuth2.0 settings. Note your client ID and secret.
• Step 2: Generate Access Token: Use Postman or a similar tool to authenticate. Exchange authorisation code for access token. Remember, tokens expire after 30 minutes.
• Step 3: Create Power Query Connection: In Power BI Desktop, select Get Data > Web. Build API calls using Power Query M language. Handle pagination for large datasets.
• Step 4: Transform Data: Shape API responses into usable tables. Handle nested JSON structures. Create relationships between entities.
• Step 5: Manage Authentication: Implement token refresh logic. Consider building an Azure Function for automated token management. Monitor for authentication failures.

Handling Multiple Xero Organisations

Consolidating multiple entities requires careful planning. Each Xero organisation needs separate authentication. Most third-party connectors support multi-entity connections, but implementation varies.

• Separate Schema Approach: Each entity gets its own database schema. Query across schemas for consolidated reporting. Maintain data isolation while enabling group analysis.
• Unified Model Approach: All entities flow into a single data model. Include entity identifiers in each record. Build consolidated reports with entity-level filtering.
• Elimination Handling: Automated solutions manage intercompany transactions. Manual methods require elimination journals. Ensure your solution handles multi-currency conversions properly.

dataSights customers consolidate 72 Xero entities in under 3 seconds, with automatic eliminations included. Without proper automation, the same consolidation takes days in Excel.

Dealing with Performance and Limits

High-quality reporting depends on how well you manage constraints across the stack – Xero API throttling, model size, refresh frequency, and gateway throughput. In this section, we show practical ways to stay fast and reliable: staging data to minimise API calls, using a star schema with incremental refresh and aggregations, and choosing Import vs DirectQuery wisely so your dashboards stay responsive as data volume grows.

API Rate Limits

Xero enforces strict limits: 60 calls per rolling minute, 5,000 calls per day. Hitting these limits returns HTTP 429 errors. Your refresh stops. Dashboards show stale data.

Solutions that stage data in databases avoid these limits. Power BI queries the database, not Xero. Unlimited Power BI refreshes without touching Xero’s API.

Data Volume Considerations

Power BI Pro limits datasets to 1GB. Large Xero files with years of transactions hit this quickly. Consider:

• Implementing date filters to limit historical data
• Using DirectQuery instead of Import mode
• Aggregating data before loading into Power BI
• Upgrading to Power BI Premium for larger datasets

Refresh Frequency

Without proper automation, you’re limited to manual refreshes. Automated solutions offer:

• High-frequency scheduled sync (subject to API limits)
• Scheduled refreshes (hourly, daily, weekly)
• On-demand refresh triggers
• Webhook-based updates for critical changes

Common Integration Challenges

Even well-planned connections hit snags – most commonly around authentication, gateway setup, schema drift, and API throttling. Use the quick guidance below to diagnose the root cause fast and apply the smallest effective fix.

Authentication Failures

OAuth tokens expire every 30 minutes. Manual connections require constant re-authentication. Automated platforms handle token refresh automatically.

Data Model Complexity

Xero’s API structure doesn’t match Power BI’s preferred star schema. Raw API data needs transformation. Relationships between invoices, payments, and contacts require careful mapping.

Multi-Currency Handling

Exchange rates need daily updates. Consolidated reports must use consistent currency. Historical rates for accurate period comparisons.

Tracking Categories

Xero’s tracking categories don’t map directly to Power BI dimensions. Custom transformation logic required. Consider how categories aggregate in consolidated views.

Staging Xero in a database, then connecting via Power BI, removes fragile CSV workflows and the pain of refreshes. You gain stable models, multi-entity coverage, and reliable schedules that keep finance and leadership aligned. It’s the fastest path to accurate, always-current reporting.