Connecting to Xero in Power BI Desktop

At FreshBI we like making things that don’t exist yet.

Xero is a bookkeeping suite for Small to Mid-Size businesses. It does a lot of the hard work for you at a decent price. It’s good at what it does however it is terrible with Power BI- until today. The current Power BI App for Xero, by Xero, is lacking. Content creators who wish to make complex reports and create custom measures are out of luck due to AppTemplate restrictions and the underlying data structure is completely hidden for the same reasons.

We’re hoping to change that for you with this custom connector.

Please be aware that this connector is currently in BETA.

What does the Xero Custom Connector allow?

Custom Measures:

Sometimes you just need to report on YOUR financials YOUR way. By using this Custom Connector, you can leverage all the Power of PowerBI! This means custom measures, custom columns and custom relationships.

Direct Integrations:

This custom connector allows you to integrate your Xero data with ANY other dataset. No more warehousing, no more refresh latency and no more canned reporting. You integrate with your data. All of your data.

Consolidations:

A.. feature.. of the Xero authentication process is that multi company Xero Consolidations is possible. If you have ONE account connected to multiple Xero entities, you can combine them all together for comprehensive consolidated reporting.

Scheduled Refresh:

No more manual refreshes. Get on-time refresh cycles and automatic failure notifications straight to your inbox. Configure your gateway one time and let itrefresh for you behind the scenes. Set it and forget it.

Set Up:

• Make sure your user account has the rights to access the accounting side of the API.

• Purchase and Download the Custom Connector here>>

• Download PowerBI and place the Custom Connector in the Custom Connector folder.

  • The path us usually something like C:\\Users\Michael B\Documents\Power BI Desktop\Custom Connectors

  • Depending on your version, you may have to use: C:\\Users\Michael B\Documents\Microsoft Power BI Desktop\Custom Connectors

• Configure your PowerBI data Gateway using this guide

  • Remember to DOUBLE check your custom connector is seen in the gateway under Custom Connectors in the local set up

  • Make sure you have allowed the gateway to actually USE custom connectors in under Manage Gateways

    • As of May 2020 it is the middle checkbox on the gateway settings in the service

• Allow 3rd Party Custom Connectors in PowerBI Desktop

  • File -> Options and Settings -> Options -> Global -> Security -> ‘Allow Any Extension to Load Without Validation’

  • Restart PowerBI

If you do not follow the above steps, you will encounter failure.

Getting Started:

Once you’ve dropped the Custom Connector in the CC folder, open up PowerBI and click on Get Data.

• Navigate to Other

• Choose Xero

• Assign your Parameters

• Click Sign In

• Sign in with your credentials

• Choose one company from the drop down list and click ‘Allow Access’

• If you have more than one company you want to include, click ‘sign in as a different user’ and choose the next company you want to allow the CC to access. This will authorize the CC to connect to each company you allow.

• Click Connect

Gathering Data:

At this point you should be presented with a list of datasets you can access.

• Accounts

• Bank Transactions

• Contact Group Linkage ( distinct combos of contact groups and contacts )

• Contact Group

• Contacts

• Currencies

• Invoices

• Invoices Repeating

• Items

• Journals

• Organisation

• Purchase Order Details

• Purchase Orders

• Tracking Categories

If you require something outside of this, let us know and we can customize the solution further for you.

Final Steps:

Finally, you are free to implement business logic as you see fit.

• Create custom Measures

• Create custom relationships

• Integrate your custom data sources

The last step is authenticating yourself online in the PowerBI Service with Xero and scheduling a refresh.

If you do not schedule a refresh you will be required to sign in every couple of days when you decide to push a manual refresh.

After you publish your report to your online portal, go to data sets and find your new Xero data set.

Under Schedule Refresh:

• If you see an error, choose discover data sources

  • Note: if you have not enabled your gateway to use custom connectors as noted in the above Microsoft article, you will not make it past this step.

• On the local machine where the gateway is installed, open the gateway and look for the ‘Connectors’ button on the side. Put the .PQX in the folder indicated there.

• Under Gateway Connection, find the Gateway linked to the folder where you put the .pqx file.

• Click the arrow to expand the settings

• You should see a link asking you to manually add Xero to the Gateway

• Follow the link and give your new data source a name

• Click Edit Credentials and Sign in to Xero following the same steps you followed in Power BI Desktop

• Click Apply and navigate back to Schedule Refresh

• Choose your gateway and set up your refresh frequency

And that’s it!

The sky is the limit now when it comes to your Xero-PowerBI Integration. You are no longer chained to the canned Xero app in PowerBI online.

Our Latest Blogs

About FreshBI

Based in Canada, South Africa and in the United Kingdom, we have helped hundreds of businesses achieve excellence & success through business intelligence.

Power BI runs in our blood, and we are ready to take your business to next level.