Using Power BI for advanced QuickBooks data analytics

Although accounting information systems offer vast value to accountants and their clients, they often lack the data analytical capabilities desired for creating additional value. QuickBooks, for example, provides basic analytical support through a powerful, affordable system for small and medium accounting applications.

However, no matter how comprehensive systems like QuickBooks are in offering data analysis, accountants often want to do more to meet client needs. When a client needs more analytical capability than QuickBooks can provide, accountants can use additional technological tools, like Power BI, to conduct the analysis. They can perform detailed analyses of transactions, account reconciliations, and other analytical reviews when data is linked or loaded from QuickBooks into Power BI, moving them one step closer to providing desired client value.

This article covers two ways to analyze and report QuickBooks Online data using Microsoft’s Power BI Desktop Version. The first method, which we call the Excel-based approach, is fairly simple and uses Excel to retrieve data from QuickBooks and load data into Power BI. The second method, which we call the QuickBooks interface approach, is more complicated and uses the custom interface in Power BI to retrieve data directly from QuickBooks. Either method can be used to create a visualization like the one shown in the screenshot “Power BI Dashboard,” below. View videos illustrating the steps below at the bottom of this page.

EXCEL-BASED APPROACH

Three steps are required to access QuickBooks data using the Excel-based approach:

■ Step 1: Generate a report in QuickBooks;
■ Step 2: Export the report to Excel; and
■ Step 3: Import, transform, and load the data into Power BI.

In Step 1, the user generates a report in QuickBooks compiling the data desired to be analyzed; either standard or custom reports can be used. For purposes of illustration, assume that you are using a QuickBooks standard report, “Transaction Detail by Account” (shown under the report heading “For my accountant”), to collect data to be analyzed.

To run this report, open QuickBooks, select Reports (ensure the Standard tab is selected), and scroll down to the Transaction Detail by Account standard report (see the screenshot “Running Reports,” below). Run the report.

In Step 2, click the Export icon (found in the upper right corner of the report) and select Export to Excel. Note the location where the exported file is saved (typically in the Downloads folder). The QuickBooks report can now be exported and opened by Power BI, where data transformation and formatting occur. See the screenshot “Exporting Report to Excel,” below.

In Step 3, open the Power BI Desktop, select Get Data from the Ribbon (indicated by the top red arrow in the screenshot “Opening Power BI Desktop,” below), and open the “Transaction Detail by Account” Excel file exported from QuickBooks.

Before we continue, please note the icons for the three view modes of the Power BI Desktop on the left gray bar (as shown in the “Opening Power BI Desktop” screenshot). The top icon in the left gray bar is the Report View, which allows users to create reports with visualizations. The middle icon is the Data View, enabling users to see and explore data after it has been imported into the Power BI desktop application. The third icon on the left is the Relationships View, which shows tables and columns, allowing the user to view and create relationships in the current model.

With the Report View highlighted, click Get Data and select Excel from the options shown in the “Opening Power BI Desktop” screenshot.

Next, select the file name of the previously saved QuickBooks exported file. Power BI establishes a connection with the exported file containing the data. Check the box next to the “Transaction Detail by Account” worksheet containing the data to be analyzed (see the left red arrow in the screenshot “Transaction Detail by Account,” below).

Data from the Excel file appears. The data needs to be cleaned and formatted before loading into Power BI. Click on the Transform Data button, as indicated by the bottom red arrow in the “Transaction Detail by Account” screenshot. The Power Query Editor (native to Power BI) opens to allow the transformation of the data.

The Power Query tool can be used in Excel to easily record steps taken to transform and format the data in preparation for loading into Power BI. (It can also be saved and reused to rerun the exact steps upon refresh of the QuickBooks report.) To prepare the data, take the following steps:

1. Remove the top three rows: Select the Ribbon icon Remove Rows, then select Remove Top Rows, as shown in the red circle in the screenshot “Removing Rows,” below, Afterward, input 3 in the Number of rows pop-up box and click OK. The three top rows are deleted from the data.

2. Fill in the account name: Click on Transform in the Ribbon menu, click Fill in the Any Column Ribbon category, then select Down (circled in red in the screenshot “Filling in the Account Name,” below).

3. Use headers: Click the Home Ribbon selection, then click Use First Row as Headers in the Transform Ribbon category (see the screenshot “Using Headers,” below).

4. Remove the Total and Header rows: Click on the Date filter (drop-down arrow to the right of the Date column label), and click Remove Empty (see the screenshot “Removing the Total and Headers Rows,” below).

5. Ensure there are no trailing empty spaces and that all account and transaction types contain only their name: Select Column1 and Transaction Type columns, and then select the Trim function from the ABC Format icon found in the Text Column portion of the Transform Ribbon category. This removes any leading or trailing whitespaces from each cell and makes the data consistent for analysis (see the screenshot “Removing Trailing Empty Spaces,” below).

6. Remove any unwanted columns for analysis purposes: In this example, the Class column is removed by clicking on the Class column header, then selecting Remove Columns in the Home Ribbon (see the screenshot “Removing Unwanted Columns,” below)

7. The final step in data cleaning is to remove unwanted rows: In this example, we are looking for expense accounts. Because the Split column contains account information, we can filter unwanted accounts by clicking on the Split column, then clicking on the drop-down arrow on the right side of the column header. Uncheck any unwanted accounts for analysis purposes, deselect any bank accounts and the Accounts Payable account, then click OK. Upon completing this step, select Close & Apply to load the data into Power BI (see the screenshot “Removing Unwanted Rows,” below).

Power BI now contains the transformed data for the Transaction Analysis via visualization. Data can be viewed by selecting the Data View mode (described earlier).

QUICKBOOKS INTERFACE APPROACH

QuickBooks data is connected directly to Power BI using the QuickBooks interface connector when using the QuickBooks interface approach. To use this approach successfully, it’s best that users are knowledgeable about primary key and foreign key databases in order to develop the necessary relationships for Power BI data analysis.

To use this approach, open the Power BI Desktop and select Get Data (as shown in the screenshot “Get Data,” below), as illustrated earlier using the Excelbased approach. Scroll down and select QuickBooks Online (Beta), then click Connect. Upon attempting to connect, Power BI will ask you to “Sign in” to QuickBooks Online.

Next, Power BI displays the Navigator, which allows you to select which tables you wish to use to access data for analysis purposes. For this example, check the box next to the Purchase and Purchase Account Based Expense Line tables to select for data analysis, then click Load (see the screenshot “Loading Tables,” below).

Power BI returns to the Visualization view. From there, select the Relationship view. Note that Power BI has automatically formed a relationship between the Purchase and Purchase Account Based Expense Line tables. Power BI automatically forms the relationship by detecting like fields in each table (see the screenshot “Forming Relationships in Power BI,” below).

When forming a relationship, there should be a field in one table that represents a unique value for every row in the table (primary key). The association is formed by joining the primary key with a field that contains a like value in the desired table with which to relate (known as the foreign key). Note that the foreign key does not have to be unique on every row in the table (i.e., it can repeat on multiple rows), but it does need to be like the values in the primary key row to form the relationship.

Also, note the “Id” field highlighted in gray in the Purchase table (primary key) and the “Id” field highlighted in gray in the Purchase Account Based Expense Line table (foreign key). These two keys represent the two fields joining the tables. You can now create visualizations using purchasing data.

COMPARISON OF EXCEL-BASED APPROACH VS. QUICKBOOKS INTERFACE APPROACH

The Excel-based and the QuickBooks interface approach both have their advantages and disadvantages. The Excel-based approach can be easier to use when data to be analyzed requires multiple QuickBooks tables. However, this approach has one disadvantage when the user wishes to use updated data from QuickBooks. Then, the user will need to run the report in QuickBooks, then repeat the load and transformation steps in Power Query to update the data for visualization and analysis purposes.

One major advantage of the QuickBooks interface approach is that data is automatically updated upon reentering Power BI after QuickBooks data has been modified. The disadvantage of this approach is that multiple tables are required to create the desired visualization and analysis, and Power BI cannot create the relationships between them automatically. Therefore, users need to have more advanced knowledge of data contents and the relationship between multiple tables when using this approach. Determining an accurate primary key and foreign key are crucial steps when the user is required to form the relationships between tables.

The examples supplied here provide yet another peek at the power of Power BI and the utilization of integration capabilities with accounting software such as QuickBooks. Such knowledge opens the door for higher levels of crucial analytical power when clients rely on it and need it most.

About the authors

William Stewart Thomas, CPA, CGMA, Ph.D., is a professor of accounting and former vice chancellor at the University of North Carolina at Pembroke. Dena Dail Breece, CPA, CGMA, Ph.D., is an assistant professor of accounting at the University of North Carolina at Pembroke. To comment on this article or to suggest an idea for another article, please contact Courtney Vien at Courtney.Vien@aicpa-cima.com.

LEARNING RESOURCES

AICPA ENGAGE 23

June 5–8, Las Vegas and live online

With multiple tracks designed to empower you with innovative strategies and best practice solutions, there is no better event for you to enhance your skills.

CONFERENCE

Microsoft Power BI: Power BI Series

A nine-part self-study online series created to help you develop the skills necessary to use Microsoft Power BI tools.

SELF-STUDY

Excel for Accounting Professionals Webcast Series

The Excel for Accounting Professionals Webcast Series is designed to walk through the Excel features, functions, and techniques that will save you time.

WEBCAST

For more information or to make a purchase, go to aicpa.org/cpe-learning or call the Institute at 888-777-7077

AICPA RESOURCE

Article

“Power BI: An Analytical View,” JofA, March 2020