Introduction

To visualize QuickBooks Online data in Power BI effectively, you must automatically connect to it and retrieve the data in an easy-to-work-with format.

The tricky part lies in finding the “convenient data format” because the native Power BI connector to QuickBooks Online contains over 100 tables. Searching for the columns you need within it is akin to searching for a needle in a haystack.

Instead of using the native QBO Power BI connector, there is a better way to extract Quickbooks Online data to Power BI which we will explore in this article. There are multiple benefits of using this method of extracting data:

• All the data is grouped into tables like P&L, Balance Sheet, Cash Flow, etc. Just pick a table you need instead of trying to assemble it from 100+ others.
• You can connect Quickbooks Online accounts to Power BI and combine them in a single dataset

You can see the data format you would get in Power BI through this link.

CONTACT US TO GET THIS SOLUTION

Importance of connecting Quickbooks Online data to Power BI

Not everyone needs to extract data from Quickbooks Online into Power BI. However, it is important for:

1. Businesses who have multiple Quickbooks Accounts: accountancies, retail companies with one account per branch, real estate companies with one account per property, business owners who manage multiple companies. Extracting Quickbooks Online data to Power BI for these companies would allow to create consolidated view of all the entities in a single place.
2. Businesses that value data visualisation and custom analysis. Some professionals feel that keeping data in Quickbooks Online limits the kind of analysis they can produce.
3. Businesses that need to combine Quickbooks Online data with a different data source in Power BI. For example reporting simultaneously on financial data from Quickbooks and sales data in the CRM.

If you fall into one of those categories, the approach below would be the most convenient way of extracting Quickbooks Online data to Power BI for you.

Overview of the QuickBooks Online to Power BI Setup

Instead of using the native connector to QBO connector in Power BI, we will be demonstrating the Quickbooks connector by Vidi Corp. This connector extracts the Quickbooks Online data to Azure SQL database which can later be connected to Power BI or any other reporting tool of your choice.

If you want this connector set up for you, follow this simple 3 steps :
1. You raise access to Quickbooks Online to a technical expert from Vidi Corp.

2. Vidi Corp use a proprietary software to extract data from Quickbooks Online. This software would automatically extract data from QBO and start loading it to the Azure SQL database.

3. The first data load might take a bit long since we would be extracting a lot of data. While the data is extracting, you will get updates through the following view.

• Once all the historical Quickbooks Online data is extracted to Azure SQL, the software will take care of automatic table refresh going forward. This table refresh can be scheduled based on the customer needs. The standard is one refresh per day but it is possible to make the data refresh every hour or even more frequently.
• You will then be able to connect Power BI to the Quickbooks Online data inside of the Azure SQL database. Simply use the Azure SQL Server connector in Power BI to pull the data.

CONTACT US TO GET THIS SOLUTION

Using Quickbooks Online data in Power BI

Now that everything is set up, we can explore the data format inside of Power BI. Let’s start with the P&L Detail table.

Every row in this table is a transaction. We get the transaction date and value in the first 2 columns and transaction description as the forth column.

If we scroll to the right, we can see that all the transactions are grouped to accounts the same way as in the P&L statement of Quickbooks Online. We can also filter this table by accounting method: cash or accrual. This means that in our Power BI report either one of those should be selected in a filter for the numbers to make sense. Class data is also available in this table as well as Quickbooks account name.

As you see, with this connector it only takes one table to analyse your P&L metrics.  We can now use this table to create analysis like below.

You can view more Power BI templates that can be produced with the data from this connector here.

Let’s now take a look at the Balance Sheet table. Again, the data format in this table mirrors the Quickbooks Online Balance Sheet report. The first columns give us amount value, date of the transaction and the description of this transaction.

Just like before, all the transactions are grouped into account groups with 3 levels mirroring the Quickbooks Online report data format. You can also filter this table by accounting method: (cash or accrual) and Quickbooks account name.

Again, we only need one table to create a Balance Sheet Power Bi report using the Quickbooks Online data.

You can read more about this template or view other Power BI templates with Quickbooks Online data here.

Conclusion

The Quickbooks Online Power BI connector discussed in this article allows companies to extract data in a very convenient format. Having all your data grouped in tables like P&L, Balance Sheet, etc saves dozens of developer hours since no data blending work is needed to produce the reports. Ability to combine several Quickbooks Online accounts into a single Power BI dashboard is also extremely useful for companies with segregated data.

The connector software is continuously maintained and new features are added over time. All the clients get maintenance with the connector and get access to the new features once they are released.

CONTACT US TO GET THIS SOLUTION