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.
