Feature Request - Add Product to Deals

[Rollins-Star]

Thanks for the great work! It would be awesome to add the product in the Deals dataset. Happy to test.

[goetz79]

same here, this is what I really need and am looking for!

been searching a while now and adding the line items table would solve all my issues

[goetz79]

I found a way to create the table I need. It expands the deal number so I can associate with the deal; but ufortunately I have no idea how to make the pagination work.

let Source = Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/line_items?limit=100&properties=name%2Chs_product_id%2Chs_sku%2Cprice%2Cproduct_number%2Cquantity%2Ctype%2Camount%2Chs_line_item-currency_code%2Chs_acv%2Chs_arr&associations=deals&archived=false&hapikey=##########")), #"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", "associations"}, {"results.id", "results.properties", "results.createdAt", "results.updatedAt", "results.archived", "results.associations"}), #"Expanded results.properties" = Table.ExpandRecordColumn(#"Expanded results1", "results.properties", {"amount", "createdate", "hs_acv", "hs_arr", "hs_lastmodifieddate", "hs_object_id", "hs_product_id", "hs_sku", "name", "price", "product_number", "quantity", "type"}, {"results.properties.amount", "results.properties.createdate", "results.properties.hs_acv", "results.properties.hs_arr", "results.properties.hs_lastmodifieddate", "results.properties.hs_object_id", "results.properties.hs_product_id", "results.properties.hs_sku", "results.properties.name", "results.properties.price", "results.properties.product_number", "results.properties.quantity", "results.properties.type"}), #"Expanded results.associations" = Table.ExpandRecordColumn(#"Expanded results.properties", "results.associations", {"deals"}, {"results.associations.deals"}), #"Expanded results.associations.deals" = Table.ExpandRecordColumn(#"Expanded results.associations", "results.associations.deals", {"results"}, {"results.associations.deals.results"}), #"Expanded paging" = Table.ExpandRecordColumn(#"Expanded results.associations.deals", "paging", {"next"}, {"paging.next"}), #"Expanded paging.next" = Table.ExpandRecordColumn(#"Expanded paging", "paging.next", {"after", "link"}, {"paging.next.after", "paging.next.link"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded paging.next",{{"results.id", Int64.Type}, {"results.properties.amount", Int64.Type}, {"results.properties.createdate", type datetime}, {"results.properties.hs_acv", Int64.Type}, {"results.properties.hs_arr", Int64.Type}, {"results.properties.hs_lastmodifieddate", type datetime}, {"results.properties.hs_object_id", Int64.Type}, {"results.properties.hs_product_id", Int64.Type}, {"results.properties.hs_sku", type any}, {"results.properties.name", type text}, {"results.properties.price", type number}, {"results.properties.product_number", type text}, {"results.properties.quantity", Int64.Type}, {"results.properties.type", type text}, {"results.createdAt", type datetime}, {"results.updatedAt", type datetime}, {"results.archived", type logical}, {"results.associations.deals.results", type any}, {"paging.next.after", Int64.Type}, {"paging.next.link", type text}}), #"Expanded results.associations.deals.results" = Table.ExpandListColumn(#"Changed Type", "results.associations.deals.results"), #"Expanded results.associations.deals.results1" = Table.ExpandRecordColumn(#"Expanded results.associations.deals.results", "results.associations.deals.results", {"id"}, {"results.associations.deals.results.id"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded results.associations.deals.results1",{"results.id", "results.properties.amount", "results.properties.hs_acv", "results.properties.hs_arr", "results.properties.hs_object_id", "results.properties.hs_product_id", "results.properties.hs_sku", "results.properties.name", "results.properties.price", "results.properties.product_number", "results.properties.quantity", "results.properties.type", "results.associations.deals.results.id", "paging.next.after", "paging.next.link"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"results.properties.quantity", "qty"}, {"results.properties.product_number", "SKU"}, {"results.properties.name", "Name"}, {"results.properties.type", "Type"}}) in #"Renamed Columns"

[ovendoor]

I found a way to create the table I need. It expands the deal number so I can associate with the deal; but ufortunately I have no idea how to make the pagination work.

let Source = Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/line_items?limit=100&properties=name%2Chs_product_id%2Chs_sku%2Cprice%2Cproduct_number%2Cquantity%2Ctype%2Camount%2Chs_line_item-currency_code%2Chs_acv%2Chs_arr&associations=deals&archived=false&hapikey=##########"))...

Thanks... led me onto this: https://developers.hubspot.com/docs/api/crm/search for using "the CRM search endpoints to filter, sort, and search objects, records, and engagements across your CRM. For example, use the endpoints to get a list of contacts in your account, or a list of all open deals."

So, products, for example:

let Source = Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/products?archived=false&hapikey=PUT_YOUR_API_KEY_IN_HERE")), #"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", {"createdate", "description", "hs_lastmodifieddate", "hs_object_id", "name", "price"}, {"results.properties.createdate", "results.properties.description", "results.properties.hs_lastmodifieddate", "results.properties.hs_object_id", "results.properties.name", "results.properties.price"}) in #"Expanded results.properties"

Be aware that Hubspit defaults to a limit=10 but you can go to 100 then use paging after. So it's meant for searching and getting rather than filtering in BI, by the looks of it.