QuickBook Online data modelling

We are moving to QuickBooks Online as our accounting solution. Currently, you can connect to QBO but you have two choices. Either pull the data to Power BI service where all the calculations are done but as we all know, you can’t do much of reporting building. Or, you can pull the data to Power BI desktop but without a lot of data modelling work. On the desktop version, you basically pull data at a very basic level which requires a lot of time to clean them and make them into meaningful data.

I wondered if anything has already done this and can share a sample file.

@Jawed Unfortunately the connectors built for Quickbooks online isn’t that flexible or great for custom Reports.

The same problem with MYOB’s connector which Microsoft has now pulled the pin on. You have to either build your own connector using Power Query SDK in Visual Studio.

The great thing about MYOB though is it is a hybrid product…

You can use it online or offline on a desktop/server/virtual or both.

Its API is accessible as long as you have the MYOB data file and install the API-connector. You can then query the MYOB API to return the JSON tables and build your data model by accessing the fact tables.

Do Quickbooks online files work offline too? Or is it 100% cloud-only?

QB has an API which means you can query the data yourself. https://developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/account

Hi @GarryA. I am sending you a sample of what you can pull out of QBO into Power BI desktop. The issue is that you cant access any reports such as Trial Balance, Income Statement, etc. You can only pull the documents and their details and you need to build TB and the likes. My QB is currently empty as I am building up the model and are currently uploading Chart of Accounts, Department, etc.

Have a look at it.

Example.pbix (206.5 KB)

@Jawed so the template looks pretty much blank to me… Did you review the API information? That should point you in right direction?

Hi @Jawed, we’ve noticed that no response has been received from you since December 10, 2019. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.