Xero is an online accounting tool for small businesses. I have been working with it for the past few months and the richness of its features, along with its simplicity is impressive.

You can check it out at www.xero.com.

In this post, I will show you how to connect Xero to Power BI Desktop using a custom connector. 

I created a Xero connector that allows you to retrieve your YTD P&L for the current and previous year, and then analyze it in Power BI. You can download the custom connector plus a sample dashboard for free at the bottom of this blog post.

The connector leverages the Xero API. API stands for 'Application Program Interface' and it is the standard way to access Xero data from an external application.

As I am not a Xero App Partner yet, this connector is limited to the first 25 organizations that will use it. I hope that I will be able to lift this limit soon.

If the connector does not work for you, meaning that the 25 organization limit is reached, or you need more data from Xero (bank transactions, customer invoices, bills, balance sheet, project profitability, etc), you can contact me at lc@finance-bi.com. 

How does it work?

The custom connector acts as the link between Xero API and Power BI. 

When you open Power BI and choose to get data from the Xero Connector, a pop-up will ask you to log in to Xero. Xero will then approve your credentials and send the information to Power BI. 

The custom connector will interpret the information received from Xero and transform it into a table, ready to be used by Power BI.

Does this connector save a copy of my data somewhere?

No, the connector simply acts as the 'interpreter' between Xero API and Power BI. 

How to set-up Xero P&L Connector for Power BI?

First, download the zip file at the bottom of the post. The zip file contains 
– the connector 'Xero PL Connector – Finance BI.mez'
– a sample Power BI report 'Xero PL Connector.pbix'

Copy the connector file and paste it to 'Documents/Power BI Desktop/Custom Connectors'. If this is your first time using a custom connector, the folders 'Power BI Desktop' and 'Custom Connectors' will not exist. Thus you will need to create them.

The next step is to activate Power BI permissions for custom connectors. Go to File -> Options and Settings -> Options -> Security -> Data Extensions. Check 'Allow any extensions to load without validation or warning'.

You can read more about allowing custom connectors here.

You are now ready to get your data! 

In the Power BI toolbar click on Get Data -> More. Type 'Xero PL Connector' in the search box that appears. Then select it and click Connect.

A window will prompt you to sign-in. Click on Sign In, then follow the instructions from Xero.

Once you authorize the connector, you can click on Connect.

Finally, a navigator window will pop-up with your company name. You can select it and click Load. 

That's it, you now access to your Xero data in Power BI!

As mentioned above, this connector is limited to the YTD P&L of the current and previous year. If you need access to more data, send me an email to lc@finance-bi.com.

Bonus: Power BI report to analyze Xero P&L

As a bonus, the zip file includes a pre-made Power BI report to visualize your data right away. You don't need this report to start analyzing your data.

Below is what the report looks like.

Below are the steps to link this report with your connector.

Click on 'Transform Data' in the toolbar. This will open Power Query. On the left, you will see a Query with the name of your company. Click on it, then click on 'Advanced Editor'. 

If you don't see it, you can follow the steps above to create the Query (from Get Data -> More).

A window will pop-up with code for the connector. Select everything, then copy it (Ctrl+C) and finally click Cancel to exit.

Now open the 'Xero PL Connector.pbix' file that you extracted from the zip.  Click on 'Transform Data' to go to Power Query.

On the left, you will a Query called 'Xero Connector – Profit & Loss'. Select it, then open the Advanced Editor. Delete all the code, and paste the code that you copied before. Click on Done.

Now you can choose 'Close & Apply'. This will take you back to Power BI and the report will show your numbers!

Enjoyed the article? Subscribe to the newsletter and download the Power BI and connector for free.