Power BI QuickBooks Tables

[nicky_b]

I have created a connection between QuickBooks and Power BI that allows me to import many tables from QuickBooks. I would like to use this information to calculate an accurate income value for each individual customer. I am able to sum the amounts in the invoice table to calculate an accurate gross profit value but am having trouble locating all of the expenses necessary to calculate the net income.

In the example below, there are several expenses listed in the profit and loss report in QuickBooks. I have located each of the line items in Power BI needed to calculate the 52065 Contract Work value outlined in green. They live in the "Bill" table. However, I have not been able to locate the travel expenses outlined in red in any Power BI tables. I have confirmed that these values are present as a line item in the general ledger in QuickBooks, but the Power BI connection does not import the general ledger and I have not been able to locate these values in the tables provided. The general ledger lines that contain these values list "Invoice" as the transaction type but these values are not in the Invoice table in Power BI.

I am assuming that these values must live in the tables somewhere, but I cannot find them. I would appreciate any help in locating these values in Power BI. Thanks.

[v-rzhou-msft]

Hi @nicky_b 

What does general ledger mean? Does it mean that sum all invioce which less than 52000? Can you see all invoice in your data model? Please show me the data model in Power BI Desktop. 

Here I find a blog about how to get data from QuickBooks by ODBC connector in Power BI.

For reference: Importing QuickBooks Data into Power BI Through an ODBC Connection

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.