Get QuickBooks Exports into Excel with Power Query

Welcome to this tutorial on how to pull QuickBooks exports into Excel with Power Query. In this tutorial, we will run a QuickBooks report, download it to an Excel format, and then use Power Query to clean it up and get the data into an Excel table.

We will use Power Query to import this QuickBooks report:

When we are done, the import will look like this:

That way, we can use it with all of the advanced features of Excel like PivotTables and formulas.

Video

Downloading a QuickBooks Report to Excel Format

In this tutorial, I’ll be demonstrating with QuickBooks Online. First, log in to QuickBooks, then click on the “Reports” tab and pick the P&L report.

We want monthly columns, so choose “Months” and then make sure the report ends on the last day of the month. In this case, let’s use April 30.

Run the report and you will have your report broken into monthly columns:

Select “Export to Excel.”

Once you download the file to your computer and open it in Excel, you will notice a bunch of zeros and a yellow Protected View dialog.

To be able to view the amounts and generally use the file, click Enable Editing. This enables the file so you can work with it and import it via Power Query.

Now Save the file, and you are ready for the next step.

Formatting the Data with Power Query

Now that the report is stored as an Excel file, we’ll use Power Query to import it into another workbook and clean it up on its way in.

From a different workbook click on “Data > Get Data > From Excel Workbook.” Browse to the downloaded Excel report and double-click. Select the sheet you want, which in this case is the profit and loss sheet, and click “Transform Data.”

Power Query provides a preview of the data:

Now, we’ll define several Transformations that will clean up the data and put it in the format that we want. First, remove all rows above the row that contains the months by clicking on “Remove Rows > Remove Top Rows.” Results:

Note: If you want, you can also remove rows from the bottom by clicking “Remove Bottom Rows.”

Get the month labels to be used as the column headers: go to “Transform > Use First Row as Headers”. Results:

The next thing we want to do is remove the total column. Select that column, then hit the delete key on your keyboard.

Now, select the account column, right-click, and select “Unpivot Other Columns.” This will place all amounts into a single column:

If you want, remove the leading spaces in the account column by right-clicking the column and selecting Transform > Trim. Results:

You can now rename the columns “Account,” “Month,” and “Amount” by double-clicking each label.

You can convert the Month column from text labels to actual dates by selecting the data type drop down (it says ABC and is located on the left side of the column label) and clicking Date. Results:

Once the data is looking good, click Close and Load to a table in the workbook.

At this point, your data is in a table, nice and clean, and ready for PivotTables, formula reports, or even lookup functions. You can use it to quickly summarize the data and keep it organized.

Refreshing Your Data Next Month

Now, let’s refresh the data. Go back to QuickBooks, and run a monthly report through the next period (in this case, through May 31). Click “run report,” and now you have January through May columns.

Export to Excel again, open the file, and Enable Editing.

Remember that your query is referring to a specific folder path and file name. So, if you want to refresh the data, you need to replace the old version of that file with this new file and keep it in the same folder path and with the same file name. Note: If you don’t, you can always go and edit the query and modify the source step to browse to the new location.

Once done, open your reporting workbook and click “Data > Refresh All.” Now Power Query does its thing and imports the new file, applies all of the applied steps, and updates the data table.

Conclusion

Using Power Query, you can easily import and clean QuickBooks exports in a fast and efficient way. The resulting data table makes it easy to work with the data using the features and capabilities of Excel. Use this technique in combination with formulas, PivotTables, and other features to make the most of your QuickBooks data.

If I can provide any additional information, or if you have alternatives or suggestions, please share by posting a comment below … thanks!

Sample file