Quickbooks Online-Connect Expenses to Vendor

[michelle_harris]

Hello! I'm working on building a dashboard to include expenses by vendor and I'm having a difficult time connecting our expenses to vendor. I'm using the Purchase_Account_Based_Expense_Line table for the detail (it's the only place I can seem to find it), but there doesn't appear to be a link to the vendor ID in that table. I looked for intermediary tables that I could use to link to the vendor but can't locate it. It should be somewhere since we select vendor when recording expenses. Anyone have any success in dashboarding expenses by vendor?

[freginier]

Hi Michelle,

Great question! Connecting expenses to vendors in QuickBooks Online through Power BI can be tricky because of how QBO structures its data.

The key is to use the Purchase table (not just Purchase_Account_Based_Expense_Line) in combination with the Vendor table. Here's the approach:

1. In Power Query / Power BI Desktop, when you connect to QuickBooks Online, make sure to also load the Purchase table and the Vendor table.
2. The Purchase table contains an EntityRef column (or VendorRef) that holds the Vendor ID — this is your join key.
3. Create a relationship between Purchase[VendorRef_value] (or similar) and Vendor[Id].
4. You can then relate the expense lines back to the Purchase header, and from there to the Vendor.

The typical data model looks like this:

Purchase_Account_Based_Expense_Line → Purchase (via TxnId) → Vendor (via VendorRef)

If the QBO connector you are using does not expose the VendorRef column directly in the expense line table, you may need to join through the parent Purchase table first, then link to Vendor.

Hope this helps! Let me know if you need more details on setting up the relationships in Power BI.