Introduction

To effectively visualize QuickBooks Online data in Power BI, it's essential to connect automatically and retrieve data in a user-friendly manner.

The challenge arises in identifying the “convenient data format,” as the native Power BI connector to QuickBooks Online features over 100 tables. Finding the columns you need is like searching for a needle in a haystack.

Rather than relying on the native QBO Power BI connector, we will discuss a more effective way to extract QuickBooks Online data to Power BI in this article. This method offers several advantages:

All data is organized into tables such as P&L, Balance Sheet, Cash Flow, etc. Simply select the table you need instead of trying to navigate through 100+ other options.
You can connect multiple QuickBooks Online accounts to Power BI and combine them into a single dataset.
Check out the data format you'll receive in Power BI through this link.

Importance of connecting QuickBooks Online data to Power BI

Not every business requires extracting data from QuickBooks Online into Power BI. However, it's crucial for:

Businesses with multiple QuickBooks accounts: This includes accounting firms, retail businesses with a separate account for each location, real estate companies with individual accounts for properties, and owners with several companies. Extracting QuickBooks Online data to Power BI allows these businesses to generate a consolidated view of all entities in one place.
Companies that prioritize data visualization and comprehensive analysis. Many professionals believe that keeping data solely in QuickBooks Online restricts the types of analyses they can perform.
Organizations that want to combine QuickBooks Online data with another data source in Power BI. For instance, simultaneously reports on financial information from QuickBooks and sales data from a CRM system.

If you identify with any of these categories, the following approach is the most efficient way to extract QuickBooks Online data to Power BI for you.

Overview of the QuickBooks Online to Power BI Setup

Instead of utilizing the native connector for QBO in Power BI, we will showcase the QuickBooks connector by Versich. This connector transfers QuickBooks Online data to an Azure SQL database, which can later be linked to Power BI or any chosen reporting tool.

There are several benefits to extracting QuickBooks Online data to Azure SQL Server before loading it into Power BI:

QuickBooks Online accounts often hold substantial volumes of data. As a data warehouse solution, Azure SQL Server is well-suited to handle numerous requests to QuickBooks Online and aggregate the data. By leveraging Azure SQL Server, you can offload the heavy data tasks, allowing Power BI to focus on lighter data transformation and visualization tasks.
Azure SQL Server integrates seamlessly with Power BI. Both platforms are owned by Microsoft, ensuring smooth compatibility.
By using Azure SQL Server as the intermediary, Versich transforms the data into a user-friendly format before delivering it to clients. This results in tables such as P&L, Balance Sheet, Cash Flow, etc. Essentially, we handle your data transformation, so you won’t have to!

To set up this connector for yourself, follow these straightforward 4 steps or use our video guide:

Register for an account on the Versich Power BI Connectors platform.
Once logged in, go to the “Install” tab. This is the menu for creating the tables needed in your database of choice. You can enter your Azure SQL Server details here or opt for “Versich Database.” After clicking “Install,” the script will create the necessary tables in your database. Your QuickBooks data will flow into these tables once your account is connected to the system.
After the tables are created, you will be redirected to the menu for connecting your QuickBooks data. Click the green button to “connect new company” to begin the process.

You will then select a QuickBooks company to link to Power BI and proceed by clicking Next.

After this, you'll be redirected back to the previous table, where you will see your company name listed.

You can utilize the “Refresh Status” tab to track your data loading. When the load reaches 100%, your data will be fully prepared for use.

Once all historical QuickBooks Online data is transferred to Azure SQL, the software will manage automatic table refreshes moving forward. This refresh schedule can be tailored to customer preferences, with the standard set to every 30 minutes. Versich is actively developing the “Refresh Settings” tab, where clients will be able to set custom refresh schedules.

If you return to the install tab, you can click the blue button to “Send database string” to your registered email.

You will receive an email formatted like the one below. Use the details in this email to link Power BI to your Azure SQL Server database.

This enables you to connect Power BI to the QuickBooks Online data stored within the Azure SQL database. Simply utilize the Azure SQL Server connector in Power BI to pull the data.

Using QuickBooks Online data in Power BI

With everything set up, let’s take a look at the data format in Power BI. You can do this by installing the free Power BI QuickBooks Online template or creating one from scratch.

Let’s begin with the P&L Detail table. Each row represents a transaction, with the first two columns showing the transaction date and value, while the fourth column contains the transaction description.

If we scroll right, we’ll find that all transactions are grouped into accounts, similar to the P&L statement in QuickBooks Online. You can also filter this table by accounting method: cash or accrual. This means that in your Power BI report, one of these options must be selected for the numbers to make sense. Class data is also included in this table along with the QuickBooks account name.

As demonstrated, this connector simplifies the analysis of your P&L metrics by using just one table. We can now leverage this table to create various analyses.

Explore more Power BI templates that can be generated with the data from this connector here.

Now, let’s review the Balance Sheet table. The format here reflects the QuickBooks Online Balance Sheet report. The initial columns provide the amount value, transaction date, and transaction description.

Again, all transactions are grouped into account categories with three levels that mirror the QuickBooks Online report format. You can filter this table by accounting method (cash or accrual) and the QuickBooks account name.

Once more, only one table is needed to produce a Balance Sheet Power BI report using the QuickBooks Online data.

You can read further about this template or view other Power BI templates featuring QuickBooks Online data here.

Conclusion

The QuickBooks Online Power BI connector detailed in this article enables companies to extract data in a highly convenient format. Having all data organized into tables like P&L, Balance Sheet, etc., saves countless hours for developers, as no additional data blending is necessary for report generation. Being able to combine several QuickBooks Online accounts into a single Power BI dashboard is particularly advantageous for companies with distributed data.

The connector software is regularly maintained, with new features introduced over time. All clients receive ongoing support with the connector and gain access to new features upon their release.