Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raldred
New Member

Connecting to Xero data from Desktop application?

Whilst I know of the Xero Content Pack, available through the web service - I cannot find a way to connect to Xero data from the PowerBI desktop application.

 

Does anyone have any ideas on how to achieve this?

 

Thank you,

 

Ryan.

38 REPLIES 38
fabiobell
Regular Visitor

I had a good experience with a custom connector. It is a bit complicated to get it done, but it avoids monthly subscriptions and external intermediary agents. Power Query SDK overview - Power Query | Microsoft Learn.

arnieayton
New Member

You cant do it directly

 

We built this for connecting Xero to PowerBI Desktop via PowerQuery - https://accounting.bi/ 

 

MichaelTomar
Helper I
Helper I

Try using Skyvia to connect Xero and Power BI.

 

This is a cloud service without coding that supports the connection between these services.

themistoklis
Community Champion
Community Champion

Hello All,

 

Microsoft has released a Xero connector on Azure which is on preview mode (waiting for customer feedback).

You can see the connector on this link.

 

You can create a free Azure account and test it... It is working fine but it has a few issues.

 

One of the major issues is the one reported here. You can vote for this issue and if Microsoft manages to finalise this connector on Azure hopefully they will easily move it to PowerBI Desktop.

 

Thanks

ElizabethTachji
Helper III
Helper III

Hi,

 

It is possible to connect power bi to Xero using an ODBC or connector.  I currently create many customised reports in Power BI desktop using data directly from Xero and other applications.

 

Elizabeth Tachjian

Analytics Realtime

Elizabeth Tachjian
jllau84
Regular Visitor

Hi,
I am new and now evaluating the xero and powerBI as my selection for my new Accounting and Reporting System.
I wondering and not saw XERO in "get data" selection for powerBI Desktop, thats meant powerBI Desktop is not able to connect to XERO directly? Or we need to have powerBI Pro to do that?

 

Hello @jllau84. I am not a consultant so....ya know.

 

Anyway, couple of questions.

 

1. What are you expecting the reporting to do for you?

2. Are you trying to generate reports for a really big organisation or a small one?

3. How many transactions are you expecting to come through each week?

4. Do you need near real time processing or is it a case of needing to see transactions up to say yesterday?

5. Does it need to be fully automated or would it be okay to have an extract run?

 

PowerBi is an incredibly powerful platform but still in its infancy so not all things are yet catered for eg a Xero connector on desktop. In saying that a monthly update cycle is pretty good and so many of these issues go away quite swiftly if there are enough users voices backing the idea.

 

There are a couple of ideas I can think of and seen implemented that i can share if you can give me an idea of what you are after.

 

Cheers,


Sam

 

// if this is a solution please mark as such. Kudos always appreciated.

Hi Sam,

 

1. What are you expecting the reporting to do for you?

- Able to generate Financial Report, Budget Report, Forecasting Report etc.... Maybe link to HR data for HR Report etc.

2. Are you trying to generate reports for a really big organisation or a small one?

- We are concrete supplier, we might need to generate the sales report and other internal report.

3. How many transactions are you expecting to come through each week?

- for sales, normally 400 to 500 line but may be higher than that.

4. Do you need near real time processing or is it a case of needing to see transactions up to say yesterday?

- yes, for management planning, material planning and sales forecast.

5. Does it need to be fully automated or would it be okay to have an extract run?

- depend on the complexity of the extract task, if got automated will use automated method.

 

@jllau84 I am not a particularly high user of zero - i try to keep out of the way of accountants - so you will need to excuse my ignorance with some of my responses.

 

1. The volume of transactions is not a problem. Thats easily within the limits of API calls so not a problem.

2. Linking to other systems will require multiple tables - at least one for each system and then for good practice a date table at the very least. That cannot be done in PBI service. It does not allow the relationship creation.

3. If by realtime info you are talking about an order comes in, is entered into the system and the say mix quatities are updated and reported on immediately then PBI wont be a solution for you at the moment.

 

with a whole bunch more investment into say pubnub connectors or an overly complicated system where you have data extracted on PBI servcie then connected as a data source in excel then published back up to PBI service while being mashed up with other sources, it could be achieved. But that sounds a bit too hard does it not?

 

I suspect from the volume of transactions you are looking for a solution which is out of the box and will (we all know that never actually happens) just work without a whole bunch of dedicated IT time.

 

I would love to say use powerbi because i genuinely like using it and reccomending it, but in this case i dont see that its a good solution for you at the moment. If you were able to wait 24 months it would be virtually guaranteed to be an out of the box fit.

 

Cheers,

 

Sam

// if this is a solution please mark as such. Kudos always appreciated.

Hi Sam and other, My replies in red

 

1. What are you expecting the reporting to do for you?

- Able to generate Financial Report, Budget Report, Forecasting Report etc.... Maybe link to HR data for HR Report etc.

-Can be done, use the actual table tables from Xero(for example invoice and credit notes tables), create a budget table and forecast table in excel.  Get data the excel files and Xero data,. Establish a relationship with power BI desktop using chart of account numbers or items codes.  Once the relationship is established you can develop the variance reports.

2. Are you trying to generate reports for a really big organisation or a small one?

- We are concrete supplier, we might need to generate the sales report and other internal report.

create a sales report using the sales invoices table in Xero.  Simply!

3. How many transactions are you expecting to come through each week?

- for sales, normally 400 to 500 line but may be higher than that.

Not an issue.

4. Do you need near real time processing or is it a case of needing to see transactions up to say yesterday?

- yes, for management planning, material planning and sales forecast.

Once you have developed the Power BI desktop report, publish the report to Power BI Service.  In service set refresh as often as you like.  I normally refresh data 4 times a day.   Xero has a limitation of 5000 API's per day.  

 

5. Does it need to be fully automated or would it be okay to have an extract run?

- depend on the complexity of the extract task, if got automated will use automated method.

 Power Bi is all about automated.  Once the report is created, it is automated.  For example I spent a couple hours creating a sales dashboard for a reasonble size conference.  The customer asked for realtime analytics such as number of registration per event, $ values earning per event, total earning for the conference, are the people registered attending all events, how many gala dinners need to organise for, budget variance graph per each event for units and $. Slice up the data any way you like as long as you have the data in Xero.  The 6 report users get a refreshed set of conference analytics 4 times a day.

 

There is no need to extract all data to excel.  You may need to create a few tables in excel because the data is not available in Xero.  Tables I normally create are budgets, forecast, calendar table.  

 

Enjoy

Elizabeth Tachjian

Hi All,

 

Note, there is Power BI desktop and Power BI Service.  Desktop is where you mash the data and can connect to many data sources.  Power BI service has limited capabilities of mashing data and is a great tool to share the dashboards.

 

Desktop is for free, Power BI service Pro user is no more than $20.00 per user per month. 

 

Cheers

elizabeth@analyticsrealtime.com.au

Elizabeth Tachjian

This is all great stuff and i agree that with the additional drivers it can all be made nicely in desktop, but the CData drivers for Xero are an additional cost and an additional addon to manage. I guess my point is for @jllau84 purposes, this is not an out of the box solution. Maybe i am too much a fan of keeping things as simple as possible?

// if this is a solution please mark as such. Kudos always appreciated.

Hi Sam,

 

The decision the user needs to make is would they prefer:-

 

1. to use clearjelly to transfer data to excel, then link excel to Power BI desktop which is what you are suggesting and your preferred method

2. Get data directly out of Xero to Power BI desktop using an ODBC driver which is what I am suggesting and my preferred method.

 

Cheers

Elizabeth

Elizabeth Tachjian

@ElizabethTachji Elizabeth,

 

Could I please ask you to refrain from making any statements about our product www.acterys.com that are completely incorrect on public forums? I don't have to comment too much on the quality of your "advice" in this thread as it clearly reflects your limited knowledge about businesss intelligence/analytics in general and about the technical details around our product and the Xero API in particular. So, just for the record:

 

1. Acterys does NOT "transfer data to excel, then link excel to Power BI desktop".

2. Acterys creates a relational data model optimized for analytics purposes (star schema, time dimension handling, etc. etc. etc.) in the cloud using the Xero API. The update process from Xero can be triggered by the user at any time with one click or you can setup a process that does this automatically. This means that the user is not limited by API call limits when querying the data which is an issue as soon as you have multiple users, the need to include historic data and larger organizations with a higher number of transactions. They also don't have to go through a lot of work (and have technical experience) to modify raw API tables into a useful data model. 

3. The Acterys data mart which can include as many Xero entities as you like (we have now customers with >70) can then be queried with any tool that supports SQL Server access. 

4. This includes access directly from Power BI Desktop (supported in the Acterys version (https://www.acterys.com) by custom visuals that include advanced write back and comments.

5, From Excel using the data model tables directly (nothing apart from report definitions is stored in Excel the data is queried live) alternatively Power Pivot can be used if you want to work offline and store the data in a local model. The Acterys Excel Add-in here again offers extensive planning, writeback and data modelling options.

6. I have already stated the reasons for our approach before so just briefly: a direct API approach is unlikely to support multiple entities. Xero states this here (https://www.xero.com/au/partnerships/microsoft-powerbi/😞

"I’m an accountant/bookkeeper. Can I view data across multiple clients using Xero?

You can view data for each of your clients on their own dashboard within a single copy of Power BI. However, you can't combine multiple Xero clients on one dashboard." and it's the same with QuickBooks.

 

7. Please feel free to review the comments about the Quickbooks Power BI Connector that is using Elizabeth's suggested direct API approach here: https://apps.intuit.com/powerbi#

 

Martin

 

 

Hey Martin,

 

Again you are going on about Quickbooks app and Xero App connecting to Power BI service.  You are on the wrong page.  The topic is about connecting to POWER BI DESKTOP.  I have connected to many different databases at one time so please stop.

 

Moving forward, I will not be replying to your message because I feel your comments or advice are of no value to me.  Your criticism is of annoyance and if you choose to continue I will report you for inappropriate content.

 

Warm Regards

Elizabeth Tachjian

Elizabeth Tachjian

nope, not a proponent of either, more just disappointed that PBI is not a great solution for xero users who want to do a bit more than play around with a single exisiting data source. I guess the best thing to do is get a annual cost for each type of connector from:

@karaoan and @ElizabethTachji

 

given that the number of calls is minimal, either system would work so really the decision becomes one of cost and reliability.

 

Cheers,

Sam

// if this is a solution please mark as such. Kudos always appreciated.

Hi,

 

Power BI desktop can connect to Xero directly however you need a couple add ons.  I provide a service to business, to connect Power BI desktop to Xero and develop bespoke business insights.  If interested send me an e-mail elizabeth@analyticsrealtime.com.au.

 

Cheers

Elizabeth Tachjian.

Elizabeth Tachjian

Even if a direct API access from Power BI Desktop is possible, it is typically not a great idea as:

1. You will always be at the mercy of "allowed API calls per day" limitations.

2. There are often issues with multi entity (consolidation) access.

3. Access via the API will not provide a proper data warehouse/star schema which means that working with it will be slow, have a limited user experiene and will always require additional work on the front end side. 

 

All these problems are already apparent when you use the QuickBooks direct access connector in Power BI Desktop that uses the Quickbooks API.

 

Feel free to contact me, for further questions on this.

 

Martin

 

Planning With Power BI -> https://www.acterys.com

@jllau84 There is no data source in Power BI for Xero. There is only an app which only supports one entity and it can't be edited in Power Desktop. For consolidations and to use all the features in the Desktop you will need a solution like https://www.acterys.com.

hi @karaoan,  i not understand with "There is only an app which only supports one entity and it can't be edited in Power Desktop." That's mean will be only some "build-in report" for xero that is not editable?

clearjelly is free apps or need to pay?

 

How about powerBI Pro and Premium? can connect to XERO directly?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.