Hubspot API POST request with relative path

Hello everyone,

I'm excited to share a breakthrough I've achieved after extensive research into integrating HubSpot API with BI tools like Power BI, which I know has been a challenge for many in this community.

The Key Solution: The most effective method I've discovered involves creating a dynamic function within Power Query. This function efficiently loops through the deals table in HubSpot, associating each deal ID with its corresponding company ID. 

Getting Started: Your first step is to create a dynamic function. Provided below is the precise syntax necessary for constructing a function. This function will adeptly navigate through the deals table in HubSpot, effectively linking each deal ID to its respective company ID.

let
Source = (id as text)=>

let
url = "https://api.hubapi.com/crm/v4/associations/deals/companies/batch/read",
headers = [#"Content-Type"="application/json", Authorization="Bearer pat-XXX"],
postData = "{""inputs"": [{ ""id"": """& id &"""}]}",
response = Web.Contents(
url,
[
Headers = headers,
Content = Text.ToBinary(postData)
]
),
jsonResponse = Json.Document(response),
results = jsonResponse[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
in
Source

Your second step is to extract the deals table from HubSpot. To ensure seamless functionality when publishing your BI report in Power BI services, it's crucial to implement a Relative path and Pagination strategy. This approach resolves the common issue many have faced regarding publishing the BI report on BI services and setting up an automatic refresh schedule.

Syntax and Implementation: Below is the specific syntax you should use to retrieve the deals table (API GET request). This same logic can be applied to other tables such as companies and tasks, allowing for a more comprehensive and integrated data analysis experience.

let
baseuri = "https://api.hubapi.com",
relativePath = "/crm/v3/objects/deals",
headers = [#"Content-Type"="application/json", Authorization="Bearer pat-XXX"],
params = [limit="100", properties={"hs_forecast_amount,hs_manual_forecast_category,hs_forecast_probability,amount,amount_in_home_currency,closedate,createdate,dealname,dealstage,dealtype,pipeline,hubspot_owner_id,num_notes,num_contacted_notes,closed_lost_reason,closed_won_reason,hs_is_closed,hs_is_closed_won,engagements_last_meeting_booked,hs_closed_won_date,hs_deal_stage_probability_shadow,hs_deal_stage_probability,one_off_consulting_revenue,hs_arr,hs_tcv,solution__main_,deal_country,dealtype,win_probability"}],

// Define the GetPage function within the main let-expression
GetPage = (data as list, after as nullable text) as list =>
let
queryParams = if after <> null then params & [after=after] else params,
response = Json.Document(Web.Contents(baseuri, [RelativePath=relativePath, Headers=headers, Query=queryParams])),
newdata = response[results],
combinedData = List.Combine({data, newdata}),
hasMore = Record.HasFields(response, "paging"),
nextAfter = if hasMore then response[paging][next][after] else null,
result = if nextAfter <> null then @GetPage(combinedData, nextAfter) else combinedData
in
result,

// Initial call to GetPage
finalData = GetPage({}, null),
expandedTable = Table.FromList(finalData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(expandedTable, "Column1", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"id", "properties", "createdAt", "updatedAt", "archived"}),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"id", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Removed Other Columns", "properties", {"amount", "amount_in_home_currency", "closed_lost_reason", "closed_won_reason", "closedate", "createdate", "deal_country", "dealname", "dealstage", "dealtype", "engagements_last_meeting_booked", "hs_arr", "hs_closed_won_date", "hs_deal_stage_probability", "hs_deal_stage_probability_shadow", "hs_forecast_amount", "hs_forecast_probability", "hs_is_closed", "hs_is_closed_won", "hs_lastmodifieddate", "hs_manual_forecast_category", "hs_object_id", "hs_tcv", "hubspot_owner_id", "num_contacted_notes", "num_notes", "one_off_consulting_revenue", "pipeline", "solution__main_", "win_probability"}, {"amount", "amount_in_home_currency", "closed_lost_reason", "closed_won_reason", "closedate", "createdate", "deal_country", "dealname", "dealstage", "dealtype", "engagements_last_meeting_booked", "hs_arr", "hs_closed_won_date", "hs_deal_stage_probability", "hs_deal_stage_probability_shadow", "hs_forecast_amount", "hs_forecast_probability", "hs_is_closed", "hs_is_closed_won", "hs_lastmodifieddate", "hs_manual_forecast_category", "hs_object_id", "hs_tcv", "hubspot_owner_id", "num_contacted_notes", "num_notes", "one_off_consulting_revenue", "pipeline", "solution__main_", "win_probability"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Expanded properties", {{"hs_forecast_amount", each Text.BeforeDelimiter(_, "."), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"hs_lastmodifieddate", type datetime}, {"hs_manual_forecast_category", type text}, {"hs_object_id", Int64.Type}, {"hubspot_owner_id", Int64.Type}, {"num_contacted_notes", Int64.Type}, {"num_notes", Int64.Type}, {"pipeline", type text}, {"id", Int64.Type}, {"amount", type number}, {"amount_in_home_currency", type number}, {"closed_lost_reason", type text}, {"closed_won_reason", type text}, {"closedate", type datetime}, {"createdate", type datetime}, {"dealname", type text}, {"dealstage", type text}, {"dealtype", type text}, {"hs_forecast_amount", Int64.Type}, {"hs_forecast_probability", type number}, {"hs_is_closed", type text}, {"hs_is_closed_won", type text}, {"engagements_last_meeting_booked", type datetime}, {"hs_closed_won_date", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"closedate", type date}, {"createdate", type date}, {"hs_lastmodifieddate", type date}, {"hs_closed_won_date", type date}, {"engagements_last_meeting_booked", type date}, {"id", type text}, {"hs_deal_stage_probability_shadow", Percentage.Type}, {"hs_deal_stage_probability", Percentage.Type}}),

#"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Get Batch Comp/deal id", each #"Get Batch Comp/deal id"([id])),
#"Expanded Get Batch Comp/deal id" = Table.ExpandTableColumn(#"Invoked Custom Function", "Get Batch Comp/deal id", {"Column1"}, {"Column1"}),
#"Expanded Column1.0" = Table.ExpandRecordColumn(#"Expanded Get Batch Comp/deal id", "Column1", {"to"}, {"to"}),
#"Expanded to" = Table.ExpandListColumn(#"Expanded Column1.0", "to"),
#"Expanded to1" = Table.ExpandRecordColumn(#"Expanded to", "to", {"toObjectId"}, {"toObjectId"}),
#"Invoked Custom Function1" = Table.AddColumn(#"Expanded to1", "Get Batch Task/deal id", each #"Get Batch Task/deal id"([id])),
#"Expanded Get Batch Task/deal id" = Table.ExpandTableColumn(#"Invoked Custom Function1", "Get Batch Task/deal id", {"Column1"}, {"Column1"}),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Get Batch Task/deal id", "Column1", {"to"}, {"to"}),
#"Expanded to2" = Table.ExpandListColumn(#"Expanded Column2", "to"),
#"Expanded to3" = Table.ExpandRecordColumn(#"Expanded to2", "to", {"toObjectId"}, {"toObjectId.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded to3",{{"toObjectId", "Company_id"}, {"toObjectId.1", "Task_id"}, {"hs_tcv", "total_contract_value"}, {"hs_arr", "ARR"}, {"deal_country", "Market"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Company_id", Int64.Type}, {"Task_id", Int64.Type}, {"one_off_consulting_revenue", Int64.Type}, {"ARR", type number}, {"total_contract_value", type number}, {"win_probability", Percentage.Type}, {"solution__main_", type text}, {"Market", type text}, {"amount", type number}})
in
#"Changed Type2"