Developing a Custom Xero Data Connector for Power BI


Date: Tuesday, April 13, 2021

Developing a Custom Xero Data Connector for Power BI 

As great as Power BI is for importing data from a wide range of sources, it doesn’t always have the data connector that you need. Village ourselves were recently in this position when we couldn’t analyse our Xero data in Power BI. Fortunately, custom connectors can be developed using the Power Query M formula language. 

Microsoft documentation outlines a 10 part walkthrough on building connectors for Power BI which provides a decent idea of what is required, I followed their suggested approach. I also inherited some code from a colleague which made things much easier. I began development by trying to get access to the invoices and contacts tables in Xero and after a few iterations, I imported the data successfully. The next challenge was to import invoice lines which required paging parameters. I’ve always found that pagination involves a little extra thought because you have to build up the data set from scratch while looping through the pages. A few development cycles later and I had a paging function created allowing me to import the invoice lines. With the three tables making their way into Power BI successfully, I had a simple data model to work with. 

Now that I had a custom connector up and running, the next step was to publish the data to Power BI Service so that colleagues could create their own reports. I started by creating a virtual machine in Azure to host a Power BI Gateway allowing the data to be refreshed. I checked that the gateway was online and connected to the Xero data set but I was running into a few errors. To be able to use custom connectors in Power BI Service a few settings need to be tweaked so I made the relevant configuration changes which resolved the errors. A few successful test refreshes later and the data set was good to go! 

Although developing the custom Xero connector was a challenging experience, it was a nice change of pace from the business intelligence work that I usually do. It’s also given me the necessary experience to develop more custom connectors in the future.  So, if you are looking to use this Xero connector or need one developing, contact Village and I’ll be happy to apply my new skills! 

 

https://unsplash.com/photos/ImcUkZ72oUs?utm_source=unsplash&utm_medium=referral&utm_content=creditShareLink

 

Tom Colbeck

Tom is involved in the heavy lifting end of data preparation for clients pulling in data from a range of sources and structuring it for clients to to gain management insights.

Read more posts by Tom Colbeck