Have you been curious about how businesses integrate QuickBooks Online with Power BI to maximize their financial insights?
Understanding financial data plays a crucial role in making informed choices and expanding a business in today's data-centric environment. For numerous businesses, QuickBooks Online is the primary platform for overseeing finances and creating reports.
Whether you are a small business owner, a finance expert, or a data analyst, mastering this connection can significantly change how you assess and visualize your financial information.
This guide will outline two methods for linking Power BI with QuickBooks Online reports. We will also delve into the pros and cons of each approach. By following these guidelines, you'll easily integrate your financial data, gaining invaluable insights.
Installing the Power BI QuickBooks Online Connector by Versich
Rather than spending countless hours on data mixing and limiting yourself to just one QuickBooks Online account, you can leverage the Power BI connector for QuickBooks Online provided by Versich.
Advantages:
Compiles all profit and loss metrics into one table, allowing you to drill down to individual transactions. It also lets you alternate between accrual and cash basis accounting in P&L while filtering by class.
Features all Balance Sheet information in a single table, also enabling you to switch between accrual and cash basis and filter by class.
Contains accounts payable and accounts receivable data.
Facilitates the connection of multiple QuickBooks accounts simultaneously.
Can be modified to include additional tables or alter the data format.
The connector extracts data via the QuickBooks Online API into an Azure SQL Server database. After the extraction, you can link your Azure SQL Server database to Power BI. The integration between Azure and Power BI is robust, allowing you to handle large amounts of data if necessary.
A free trial is available for this connector, giving you an opportunity to experience all the benefits firsthand.
Connecting Power BI to QuickBooks Online: A Step-by-Step Guide
Step 1 - Register Your Account
Visit https://versich-connectors.azurewebsites.net/ and set up your free account.
Step 2 - Install the Connector
After registration, you can choose your database preferences in the subsequent window. Checking the “Use Versich Database” box means your data will be loaded into a database hosted by Versich. Alternatively, if you have your own Azure account, you can create your database and input the required details in the fields below.
Clicking the “install” button will prompt the system to create the necessary tables in the database. Once you authenticate your QuickBooks Online account through the portal, the data will flow into those tables.
Step 3 - Authenticate to QuickBooks Online
Once your tables are set up, you can revisit the installation tab to see the next step. Simply click the “connect new company” button to extract data from the desired company. You can use this button for multiple companies.
When you click “connect new company,” a new window will prompt you to select a company and upload its data to the database.
After clicking next and granting access to your company, you will see it listed in the table below.
Note that the “Revoke Access” button lets you withdraw your QuickBooks Online account access from the Versich connector. If clicked, data for the specified company will stop being extracted, though existing data will remain stored in the database.
If you prefer to delete all prior data extracted to the database, you can use the “remove installation” button. This will erase all tables and their contents.
As your data extraction begins, you can monitor the process in the “Refresh Status” window. Your data will be fully available once all tables show 100% loaded.
Our QuickBooks connector features approximately 50 tables, but not all are loaded by default. If additional tables are needed, navigate to the “refresh settings” menu and select the required tables. After making your selections, click “apply changes” to start loading new tables.
Finally, once your data is loaded, return to the “Install” tab and click “Send database connection string.”
Pressing this button will send an email to your registered address containing essential connection details, including server name, database name, user ID, and password.
Step 4 - Connect the Database to Power BI
Open Power BI and select Get Data. You can search for a data source and choose Azure SQL Server. When prompted, simply input the server name and database name from the email.
Next, you must select the tables you wish to load into the dashboard.
Exploring the Data Within the Connector
One of the key benefits of the connector is its user-friendly data format. For instance, let’s take a look at the P&L Detail table.
Everything is neatly organized in one table:
The Balance value column represents the value of your accounts on a specific date.
The Memo/Description column outlines the description of the transactions that comprise each account.
The Lv1 value illustrates the highest level of your P&L hierarchy, such as income, COGS, and expenses.
Lv2 value shows the account names associated with income, COGS, and expenses.
The accounting method column enables you to filter data based on the cash or accrual basis for P&L.
A similar structure applies to the Balance Sheet table (shown here from a different project).
Lv1 indicates the top of your Balance Sheet hierarchy, including Assets and Equity & Liabilities.
Lv2 breaks down Equity and Liabilities.
Lv3 reveals specific accounts that make up Level 2.
In this instance, data from 64 QuickBooks accounts was combined. The QuickBooks account name effectively serves as the client_id.
The accounting method lets you filter data based on accrual or cash basis.
Installing Our Free QuickBooks Power BI Template
Alongside our connector, we also offer a free QuickBooks Power BI Template that visualizes P&L, Balance Sheet, and Cash Flow statements. This template allows you to choose the accounting method for viewing financial statements, filter by QuickBooks Online account, and drill down to specific transactions.
You’ll find instructions for installing our free QuickBooks Power BI template here.
Utilizing the Native Power BI QuickBooks Online Connector
The QuickBooks Online connector comes pre-installed in Power BI. Currently in Beta mode, this connector may exhibit some instability. Nonetheless, it remains a useful option for extracting data from QuickBooks Online.
This is particularly beneficial when you want to extract data from a limited number of tables.
Advantages:
It’s free to use.
Quick setup process.
No additional software necessary.
Disadvantages:
Over 100 tables segment the data, necessitating significant hours for data blending.
The refresh may fail intermittently due to the connector’s beta status.
Lacks Profit and Loss or Balance Sheet tables.
No data available for accounts payable and receivable aging.
Unable to combine multiple QuickBooks Online accounts.
To explore the QuickBooks Online connector yourself, follow these steps for setup:
Go to “Get Data” and search for “QuickBooks Online.”
As soon as you type “QuickBooks,” Power BI will showcase the relevant connector. It is currently in Beta (which has been the case for about three years). Select it and click “Connect.”
After clicking connect, a warning message will appear. Click continue to proceed to the next screen.
Upon accepting the warning, you will reach the authentication stage. Click “Sign in” to start the authentication process.
Input your username and password. Once completed, the authentication process will be finished.
Choose the tables you wish to load! A total of 113 tables are available in this connector.
The QuickBooks Online connector in Power BI proves to be helpful if you can quickly locate the tables containing your data. You may refer to the official QuickBooks API documentation to ascertain which data resides in each table.
Conclusion
There are various approaches to linking QuickBooks Online with Power BI. One method involves utilizing a native connector, while creating your own connector is another option.
Having tested both methods, we would assert that the integration that saved us the most time was our own connector. Ultimately, consultant hours tend to outpace software costs significantly.
