There is allegedly a Hubspot connector for Power BI however I have never got it to work. Even if it does I suspect it would not allow access to deal line items which are buried deep in the Hubspot API.

Deal line items are when you create a Deal and add Products which may be itemised in a Quote.

The goal is to get a table that can be used in Power BI that lists all the deal line items along with their respective companies and deal owners.

The Hubspot API is fairly easy to follow. This is the page dealing with Line Items: https://developers.hubspot.com/docs/guides/api/crm/commerce/line-items

In order to call the API you need to obtain a bearer token from Hubspot.

The overall plan

In Power Query we will get the line items from Hubspot and transform the response into a query table.

Then we do the same with Companies, Deals, Pipelines (the organisational units that contain Deals) and join them altogether in Power Query using the NestedJoin function.

…one slight problem

Hubspot will return 1 page for each API call which only gets us 100 rows. One of the query parameters is PageValue. So we have to do this:

• Make a PageValue parameter in Power Query to store the current page value.
• Make a function in Power Query “GetPageFunction-LineItems” that returns one page of line items taking the current page value as an arguent.
• Make a function “GetAllPagesFunction-LineItems” that iterates through the GetPage function and accumulates the data.
• Make a AllLineItemsWithDeals table that populates itself using the GetAllPages function.

AllLineItemsWithDeals

Working backwards, The AllLineItemsWithDeals table is defined like this:

let
    InitialPageValue = "1",
    InitialData = #table(type table [results.id = Int64.Type, results.properties.amount = Int64.Type, results.properties.createdate = DateTime.Type, results.properties.hs_lastmodifieddate = DateTime.Type, results.properties.hs_object_id = Int64.Type, results.properties.hs_product_id = Int64.Type, results.properties.quantity = Int64.Type, results.createdAt = DateTime.Type, results.updatedAt = DateTime.Type, results.archived = Logical.Type, paging.next.after = Int64.Type, paging.next.link = Text.Type], {}),
    AllData = #"GetAllPagesFunction-LineItems"(InitialPageValue, InitialData),
    #"Filtered Rows" = Table.SelectRows(AllData, each true),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"results.id", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([getDealIDCustom] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"getDealIDCustom", Int64.Type}})
in
    #"Changed Type"

The above creates the table the then uses Table.SelectRows to populate it using the function GetAllPagesFunction-LineItems defined as:

let
    GetAllPages = (PageValue as text, AccumulatedData as table) =>
    let
        CurrentPageData = #"GetPageFunction-LineItems"(PageValue),
        NextPageValue = if Table.IsEmpty(CurrentPageData) then null else CurrentPageData{0}[paging.next.after],
        NewAccumulatedData = Table.Combine({AccumulatedData, CurrentPageData}),
        Result = if NextPageValue = null then NewAccumulatedData else @GetAllPages(NextPageValue, NewAccumulatedData)
    in
        Result
in
    GetAllPages

This function tests if anything is in the current result of “GetPageFunction_LineItems(PageValue)”. If there is it append it to a temporary table called AccumulatedData, and runs the function again with the next PageValue (“paging.next.after” in the code). GetPageFunction-LineItems is defined below:

let
    GetPage = (PageValue as text) =>
    let
        Source = Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/line_items?properties=quartz_course_code&properties=name&properties=hs_recurring_billing_start_date&properties=quantity&properties=amount&limit=100", [Query=[after=PageValue], Headers=[Authorization="Bearer YOUR_HUBSPOT_TOKEN"]] )),
        #"Converted to Table" = Table.FromRecords({Source}),
        #"Expanded results" = Table.ExpandListColumn(#"Converted to Table", "results"),
        #"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"results.id", "results.properties", "results.createdAt", "results.updatedAt", "results.archived"}),
        #"Expanded results.properties" = Table.ExpandRecordColumn(#"Expanded results1", "results.properties", {"amount", "createdate", "hs_lastmodifieddate", "hs_object_id", "hs_product_id", "quantity","name","quartz_course_code", "hs_recurring_billing_start_date"}, {"results.properties.amount", "results.properties.createdate", "results.properties.hs_lastmodifieddate", "results.properties.hs_object_id", "results.properties.hs_product_id", "results.properties.quantity", "results.properties.name", "results.properties.quartz_course_code", "results.properties.hs_recurring_billing_start_date"}),
        #"Expanded paging" = if Table.HasColumns(#"Expanded results.properties", "paging") then Table.ExpandRecordColumn(#"Expanded results.properties", "paging", {"next"}, {"paging.next"}) else Table.AddColumn(#"Expanded results.properties","paging.next.after",each null),
        #"Expanded paging.next" = if Table.HasColumns(#"Expanded paging", "paging.next") then Table.ExpandRecordColumn(#"Expanded paging", "paging.next", {"after", "link"}, {"paging.next.after", "paging.next.link"}) else #"Expanded paging",
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded paging.next", {{"results.id", Int64.Type}, {"results.properties.amount", Int64.Type}, {"results.properties.createdate", type datetime}, {"results.properties.hs_lastmodifieddate", type datetime}, {"results.properties.hs_object_id", Int64.Type}, {"results.properties.hs_product_id", Int64.Type}, {"results.properties.quantity", Int64.Type}, {"results.createdAt", type datetime}, {"results.updatedAt", type datetime}, {"results.archived", type logical}}), //Table.TransformColumnTypes(#"Expanded paging.next", {{"results.id", Int64.Type}, {"results.properties.amount", Int64.Type}, {"results.properties.createdate", type datetime}, {"results.properties.hs_lastmodifieddate", type datetime}, {"results.properties.hs_object_id", Int64.Type}, {"results.properties.hs_product_id", Int64.Type}, {"results.properties.quantity", Int64.Type}, {"results.createdAt", type datetime}, {"results.updatedAt", type datetime}, {"results.archived", type logical}, {"paging.next.after", Int64.Type}, {"paging.next.link", type text}}),
        #"Added Custom2" = Table.AddColumn(#"Changed Type", "getDealIDCustom", each 
            let
                InnerSource = Json.Document(Web.Contents("https://api.hubapi.com/crm/v4/objects/line_items/", [RelativePath=Number.ToText([results.id]) & "/associations/deals", Headers=[Authorization="YOUR_HUBSPOT_TOKEN"]])),
                #"Inner Converted to Table" = Table.FromRecords({InnerSource}),
                #"Inner Expanded results" = Table.ExpandListColumn(#"Inner Converted to Table", "results"),
                #"Inner Changed Type" = Table.TransformColumnTypes(#"Inner Expanded results", {{"results", type any}}),
                #"Inner Expanded results1" = Table.ExpandRecordColumn(#"Inner Changed Type", "results", {"toObjectId", "associationTypes"}, {"results.toObjectId", "results.associationTypes"}),
                #"Inner Expanded results.associationTypes" = Table.ExpandListColumn(#"Inner Expanded results1", "results.associationTypes"),
                #"Inner Expanded results.associationTypes1" = Table.ExpandRecordColumn(#"Inner Expanded results.associationTypes", "results.associationTypes", {"category", "typeId", "label"}, {"results.associationTypes.category", "results.associationTypes.typeId", "results.associationTypes.label"}), 
                InnerGetOneVal = List.First(#"Inner Expanded results.associationTypes1"[results.toObjectId])
            in
                InnerGetOneVal
        ),
        Data = #"Added Custom2"
    in
        Data
in
    GetPage