Categories
Blog

Power BI Tricks for QuickBooks Online – P&L Report Data

Getting data from tables in QuickBooks Online (QBO) is available via two supported connectors in Power BI. The most visible method to use the Quickbooks Online connector via the GUI in the Get Data wizard. However, there are some cases where the data that you might want is not available using that connector.

Once such scenario is if you need to connect to objects such as the General Ledger. In this case, you can use the “old” but supported Quickbooks.Tables() connector by creating a blank query as described in this post on the Power BI community forums.

This old connector also allows you to access other data available via the QBO API, including QBP report entities, for example the ubiquitous P&L Report.

Thanks to the article by Imke Feldmann describing how to Get full Time Activity data from QuickBooks into Power BI I learned that the QBO API allows us to retrieve information from reports as well as the usual data in QBO tables. I started by creating a simple function in Power Query named “QB Tables”. Note that what is important is the line

report = Source{[Key="report"]}[Data]

Where we are using the QBO API to access the reports that QBO makes available.

In a second query, I reference the first query, and specify that I want to “run” the P&L Details Report, passing it a start and end date range as parameters.

let
Source = #"QB Tables"("/reports/ProfitAndLossDetail?start_date=2020-08-01&end_date=2020-08-31"),
#"Expanded Categories" = Table.ExpandRecordColumn(Source, "Categories", {"Category4"}, {"Category4"}),

The list of report entities available via the API, and their associated parameters are well-documents on the Intuit site. These are the API details for the P&L Details report used in my example.

Categories
Blog

Connecting QuickBooks Online to Power BI Desktop

In mid 2020, Microsoft posted an update to the documentation for the Power BI QuickBooks Online connector  

Beginning on August 1, 2020, Intuit will no longer support Microsoft Internet Explorer 11 (IE 11) for QuickBooks Online. When you use OAuth2 for authorizing QuickBooks Online, after August 1, 2020, only the following browsers will be supported

The list of supported browsers included Internet Explorer 11.  What this means, though it is not explicitly mentioned, is that if you were using an existing connection to QuickBooks online as a data source in Power BI, and needed to edit your credentials, or if you needed to create a new connection, you would not be able to.  This is because under the covers, Power Query uses IE11 to handle OAuth2 connections, a detail that is abstracted to users of Power BI Desktop.

Chris Webb posted a link to an update to the documentation today

Enabling OAuth Authentication in Power BI Desktop

Starting in December 2021 Power BI Desktop:

  • Install the new Edge Chromium browser, (at least beta) from https://www.microsoftedgeinsider.com/en-us/download.
  • In your Environment Variables, set the System variable PQ_EnableEdgeChromiumOAuth to true . Once that is set it will use Edge Chromium for only QuickBooksOnline. For this release, only QuickBooksOnline is enabled by default.
  • You should now be able to log in to QuickBooks Online.

This is great news, and the technique above works as expected!  However, there are two Power BI supports two connectors to QuickBooks online.  The visible one that you get when you select QuickBooks Online from the Get Data menu, and an “old”, but invisible via the GUI, one that I first learned about when trying to get data from the General Ledger table, a seemingly common requirement for users of QuickBooks data.  This second connector is mentioned here 

The default connector uses QuickBooksOnline.Tables()

As described, to access the old connector, in the Advanced Editor in Power Query, use the source QuickBooks.Tables()

let
Source = QuickBooks.Tables(),
generalledger = Source{[Key=”generalledger”]}[Data]
in
generalledger

This old connector, though making many other QBO objects visible, was not fixed with the recent Edge Beta workaround!  The included a different set of instructions for addressing the same OAuth2 issue when using the Salesforce Objects connector.

  • In your Environment Variables, set the System variable PQ_EnableEdgeChromiumOAuth to true . Once that is set it will use Edge Chromium for only QuickBooksOnline. For this release, only QuickBooksOnline is enabled by default.
  • To set it for Salesforce, in System Environment Variables set PQ_ExtendEdgeChromiumOAuthAllowList to Salesforce

You can use this same set of instructions to get the old QBO Quickbooks.Tables() connector to work, though it might be unsupported.  Follow the instructions for Salesforce Objects, but, instead set the value of the environment variable PQ_ExtendEdgeChromiumOAuthAllowList to QuickBooks *note that this value is case sensitive*.

Environment Variables

Once you have done that, re-open Power BI Desktop, and edit the connections on your connection to QBO, this time, you will get a new-looking window that asks for credentials.  You may also get an error.

Error when connecting

When I clicked Continue, I was then presented with the usual QBO login screen, and was able to connect!

QBO Login After Environment Variable