How to pull data in Power BI from GL table in Quick Books Online

How to pull data in Power BI from GL table in Quick Books Online

  • Post category:Power BI
  • Post author:

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:

  1. 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.

Addend Analytics is a Microsoft Power BI-partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com or Contact us.