Power BI is a very powerful tool which can extract data from hundreds of sources in order to derive meaningful insights out of it. It offers variety of interactive visuals to build reports which can be consumed over a lot of devices & on the web.
QuickBooks is one of the sources which Power Bi can connect & extract data from.
QuickBooks is the most widely used accounting software present in the market. It has lot of various features which is not available in other accounting software which makes it the go-to software for many small to mid-sized companies.
Till beginning of Y19, Power BI was able to access GL Table in QBO but that’s not the case any more. The GL table is one of the most important table for financial analysis so were surprised and disappointed when it disappeared in the Power BI connection.
We work on may financial analysis projects where we have to get data from GL table into Microsoft Power BI so we have created a work around for it. 😊
We are sharing it here so that everyone in the community can take advantage of it.
Connecting to QuickBooks online is very simple in Power Bi. You can achieve it by following the steps mentioned below:
- Select Get data from the Home tab
2. Search for “Quickbooks” and select “Quickbooks online (beta)” and click on connect.
3. Once you have entered the credentials, you will see a list of tables
You will notice that GL table is not appearing in the list of tables.
Solution:
1) Go to “Edit Queries” from the Home tab.
2) Select dropdown of “New Queries” from the home tabe in Query editor & click on “Blank Query”
2) Select dropdown of “New Queries” from the home tabe in Query editor & click on “Blank Query” 
4) Clear the existing code in the “Advanced editor” & replace it with the following code:
Here’s the code for General Ledger:
let
Source = QuickBooks.Tables(),
generalledger = Source{[Key=”generalledger”]}[Data]
in
generalledger
Step 5) Click on Done. Then Close & Apply.