Power BI is a market-leading tool for financial analysis but it does not connect natively to Xero. This throws many Power BI users off since Xero is one of the leading accounting software providers.
Around 2022 there used to be an in-built Xero app allowing users to analyse Xero data in a pre-set Power BI dashboard. However this app is now deleted which makes it more difficult to connect the Xero data to Power BI.
Despite all this, it is still possible to connect Xero to Power BI and enable automatic data refresh. This article would cover in detail how to create this connection.
CONTACT US TO SEE OUR XERO CONNECTOR IN ACTION
Companies that want to get more insights and reporting capabilities would need this integration.
1. Companies with multiple Xero accounts: accountancies, retail companies with one account per branch, real estate companies with one account per property, business owners who manage multiple companies. Power BI might be used for combining Xero data and seeing the consolidated financial statements for all the parts of their business together.
2. Companies that create custom management reports with the data extracted from Xero. The process of extracting data manually is tedious and tends to be procrastinated. Connecting Xero data to Power BI removes the need for manual data exports. It also opens more opportunities for data visualisation and custom analysis.
3. Businesses that have management reports from multiple data sources. This way the Xero data could be combined with the data from other data sources inside of Power BI. This is a common scenario when businesses provide management reports to investors.
If any of the above points apply to you, the approach described below would be the easiest way of connecting Xero data to Power BI.
CONTACT US TO SEE OUR XERO CONNECTOR IN ACTION
Xero provides an API which we can use to connect to data automatically. API stands for “Application Programming Interface”. We can essentially perform actions on our Xero application by programming our own code. Our code would send requests to Xero API and the output of these requests will be a data extract.
Writing your own code for extracting data from APIs could be very technical. We have an article on some basic principles for writing custom connector code which could help you get started.
If you do not want to write the code for the integration yourself, you can get the ready-made code for connecting Xero to Power BI here. We will cover the data format that comes out of the API below.
The data inside of Xero API is split into several tables. The code for the data extraction needs to be written separately for every table. You can see all the available columns for all the tables here.
The main table that you need from the Xero API in Power BI is called JournalLines. Every line in this table is a transaction and you get all the transaction details in there including net/gross amounts, account grouping, transaction description, etc.
Tracking categories is also an essential piece of data for many clients. It is possible to extract the tracking category for every transaction through the TrackingCategory table.
The Journal table would allow you to group transactions into financial statement accounts.
The Contacts table returns the data on how much you owe to all your contacts or how much they owe you.
The Bank Transaction table returns the currency code and exchange rate for every transaction made through your bank. It also returns the data on which transactions were reconciled.
If you decide to work with us to extract your Xero data into Power BI, you will also get our support to find the exact data you need for producing your Power BI reports. If you want to discuss your data needs, please reach out to us.
If you are a Xero accounting partner, you will likely have multiple accounts grouped under on instance of Xero. Using our connector, it is possible to extract data from all the accounts into a single dataset. Furthermore, if new accounts are added later, the data from them will be automatically extracted as well.
Our quick setup process ensures that Xero data is pulled into an Azure SQL Server database that will be owned by you. Once the database is pulled into Azure, you will be able to connect it to Power BI using the native Azure SQL Server connector
If you do decide to work with us to extract your data from Xero into Power BI, we will set our connector up for you. The process for setting it up is the following:
1. We would get access to your Xero and Azure. We would guide you how to raise access properly.
2. We would install our Python script that extracts data from Xero to Azure. Here is a demonstration of how this works.
3. We would schedule our script to run on a schedule. This would ensure that the data is refreshed automatically.
Once the data from Xero is extracted, you will be able to pull in inside of Power BI and build your reports.
The Xero Power BI connector discussed in this article allows to extract the data from multiple Xero accounts and combine it together. Along with the connector, you will get our specialist consultancy support to find the data that you need.
This article also briefly explained the tables that are available to pull from Xero into Power BI.
