Quick Books Online to Power BI to Excel

Sue Bayes

May 5, 2022

How to Connect Quick Books Online (QBO) to Power BI and then to feed into Excel

To connect to Quick Books using Power BI you need a specific connector:

https://docs.microsoft.com/en-gb/power-query/connectors/quickbooksonline

To enable authentication to Quick Books, you need to:

Download the new Microsoft Edge based on Chromium

You then need to go into your Environment Variables to allow the pop up screen for Quick Books Online within Power Query.

You can search for Systems properties in Control Panel, and then click on Environment Variables

Add PQ_EnableEdgeChromiumOAuth = True to system variables – see visual below

Restart machine

Open up Power BI and using a blank Power Query connect to the Source using the Connector

Choose the table that you need and make necessary changes to decide which columns you want.

M Code below, which if you copy and paste into a blank query in the Advanced Editor will give you access to the Invoice Table, and selection ID, CustomerRef Value and Name column, Due date and Total Amount.

Once you connect to Power Query, the pop up authenticator will “pop up”.

				
					let
  Source = QuickBooksOnline.Tables(null), 
  Invoice_Table = Source{[Name = "Invoice", Kind = "Table"]}[Data], 
  #"Removed Other Columns" = Table.SelectColumns(
    Invoice_Table, 
    {"Id", "CustomerRef_Value", "CustomerRef_Name", "Due_Date", "Total_Amount"}
  ), 
  #"Renamed Columns" = Table.RenameColumns(
    #"Removed Other Columns", 
    {{"Due_Date", "Date"}, {"Total_Amount", "Amount"}}
  ), 
in
  #"Renamed Columns"

You can then create your data model and visuals within Power BI, and publish to the Power BI service.

Once published to the Service, you can then access the data in Excel 😊 😊 😊 to create your tables, reports for those users that don’t necessarily want to access Power BI.

Any questions, happy to help,

Sue

May 5, 2022