Power BI Custom Connector for Xero Accounting
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.