Hubspot API POST request with relative path

[darko861]

Hi all,

I have the following API POST query which works great:

let
url = "https://api.hubapi.com/crm/v3/objects/companies/search",
headers = [#"Content-Type"="application/json", Authorization="Bearer XXX"],
postData = "{""filters"": [{""propertyName"": ""associations.task"", ""operator"": ""EQ"", ""value"": ""43339920668""}]}",
response = Web.Contents(
url,
[
Headers = headers,
Content = Text.ToBinary(postData)
]
),
jsonResponse = Json.Document(response)
in
jsonResponse

With this call, I do get the associated information when I search for TaskID 43339920668 (body). But still, how do I implement this in Power Query to retrieve all associated information for every task and not just TaskID 43339920668? How can I create a dynamic function with a dynamic TaskID value in the POST body, using relative path might be the correct answer, but I don't know how to implement it. How can I tweak/modify my m code for that purpose?

Your help is much appreciated, thanks.

[darko861 · Accepted solution]

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"

[lbendlin]

how do I implement this in Power Query to retrieve all associated information for every task 

How do you know what "every task"  is?  Do you have an API call to retrieve a list?

[darko861]

Hi @lbendlin ,

I've already retrieved a table with multiple columns from the GET API call (a list of records that are transformed into a table). One of those columns is called "id" (TaskID) (print screen below). I need to retrieve additional data associated with these TaskIDs by making a POST API call.

I need to loop through each TaskID in the "id" column, which means creating a function that can be invoked in the same table as the "id" column. The POST API doc can be found here (down below, I have also attached a print screen of what API call I'm using): https://developers.hubspot.com/docs/api/crm/search

I've already created a function (below code) that works and provides the desired results, but I've noticed that it's slow. I'm wondering if using a relative path might improve performance when publishing the report to Power BI Services. Additionally, I'd like to ensure that the report refreshes automatically when it's published to Power BI Services. Could you help me modify the function to implement a relative path?

Thanks for your assistance.

(id as text)=>

let
url = "https://api.hubapi.com/crm/v3/objects/companies/search",
headers = [#"Content-Type"="application/json", Authorization="Bearer XXX"],
postData = "{""filters"": [{""propertyName"": ""associations.task"", ""operator"": ""EQ"", ""value"":"""& 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"

[lbendlin]

use the "IN"  operator to request a bunch of them at the same time.

https://developers.hubspot.com/docs/api/crm/search#in-operator

[darko861]

Hi @lbendlin, thank you, I understand, but how do I modify my code with the IN operator to include all the values (without manually writing each id) from the "id" column as described above? Could you please alter my function so I can understand how to write the syntax?

[lbendlin]

show a sample output of the call that collects the IDs.

[darko861]

Hi @lbendlin,

This is the code that gives me the IDs:

let
tasksproperties = "hs_timestamp,hs_task_body,hubspot_owner_id,hs_task_subject,hs_task_status,hs_task_priority,hs_task_type,hs_body_preview",
propertiesQString = "&properties=" & Text.Replace(tasksproperties, ",", "&properties="),
baseuri = "https://api.hubapi.com/crm/v3/objects/tasks?limit=100"&propertiesQString,
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer XXX"]],

initReq = Json.Document(Web.Contents( baseuri, headers)),
#"Converted to Table" = Record.ToTable(initReq),
initData = initReq[results],

//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,

//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then

//create a table from those records

gather = (data as list, uri) =>

let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri, headers))[paging][next][after],

//build new uri using the original uri so we dont append offsests
newUri = baseuri & "&after=" & newOffset,

//get new req & data
newReq = Json.Document(Web.Contents(newUri, headers)) ,
newdata = newReq[results] ,

//add that data to rolling aggregate
data = List.Combine({data, newdata}),

//if theres no next page of data, return. if there is, call @gather again to get more data

check = if Table.Contains ( Record.ToTable(newReq) , [Name = "paging"] ) = true then @gather (data , newUri) else data

in check,

//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if Table.Contains ( Record.ToTable (initReq) , [Name = "paging"] ) = true then gather( initData , baseuri ) else initData ,

//then place records into a table. This will expand all columns available in the record.
expand = Table.FromRecords(outputList),
#"Removed Columns" = Table.RemoveColumns(expand,{"properties", "createdAt", "updatedAt", "archived"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}})
in
#"Changed Type"

The output looks like this:

The id column should then be passed in as a list of values to the IN operator, but I don't know how to do that.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3NTIxNjA3sVCK1QFzLQzNLQ3MULiGRqZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t]),
    body = "{
	""filterGroups"": [
		{
			""filters"": [
				{
					""propertyName"": ""dealstage"",
					""operator"": ""IN"",
					""values"": [""" & Text.Combine(Source[id],""",""") & """]
				}
			]
		}
	]
}",
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Web.Contents("https://api.hubapi.com/crm/v3/objects/deals/search",[ Headers = [#"Content-Type" = "application/json"],
Content = Text.ToBinary(body)]))
in
    #"Added Custom"

gives you the sample payload:

[darko861]

Hi @lbendlin , It didn't work for me I got Formula.Firewall: Query 'Tasks (2)' (step 'Expanded Table Column1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

But I managed to extract the correct information by using this query:

let
tasksproperties = "hs_timestamp,hs_task_body,hubspot_owner_id,hs_task_subject,hs_task_status,hs_task_priority,hs_task_type,hs_body_preview",
propertiesQString = "&properties=" & Text.Replace(tasksproperties, ",", "&properties="),
baseuri = "https://api.hubapi.com/crm/v3/objects/tasks?limit=100"&propertiesQString,
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer XXX"]],

initReq = Json.Document(Web.Contents( baseuri, headers)),
#"Converted to Table" = Record.ToTable(initReq),
initData = initReq[results],

//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,

//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then

//create a table from those records

gather = (data as list, uri) =>

let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri, headers))[paging][next][after],

//build new uri using the original uri so we dont append offsests
newUri = baseuri & "&after=" & newOffset,

//get new req & data
newReq = Json.Document(Web.Contents(newUri, headers)) ,
newdata = newReq[results] ,

//add that data to rolling aggregate
data = List.Combine({data, newdata}),

//if theres no next page of data, return. if there is, call @gather again to get more data

check = if Table.Contains ( Record.ToTable(newReq) , [Name = "paging"] ) = true then @gather (data , newUri) else data

in check,

//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if Table.Contains ( Record.ToTable (initReq) , [Name = "paging"] ) = true then gather( initData , baseuri ) else initData ,

//then place records into a table. This will expand all columns available in the record.
expand = Table.FromRecords(outputList),
#"Removed Other Columns" = Table.SelectColumns(expand,{"id", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Removed Other Columns", "properties", {"hs_body_preview", "hs_createdate", "hs_lastmodifieddate", "hs_object_id", "hs_task_body", "hs_task_priority", "hs_task_status", "hs_task_subject", "hs_task_type", "hs_timestamp", "hubspot_owner_id"}, {"hs_body_preview", "hs_createdate", "hs_lastmodifieddate", "hs_object_id", "hs_task_body", "hs_task_priority", "hs_task_status", "hs_task_subject", "hs_task_type", "hs_timestamp", "hubspot_owner_id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded properties",{{"id", type text}, {"hs_body_preview", type text}, {"hs_createdate", type datetime}, {"hs_lastmodifieddate", type datetime}, {"hs_object_id", Int64.Type}, {"hs_task_body", type text}, {"hs_task_priority", type text}, {"hs_task_status", type text}, {"hs_task_subject", type text}, {"hs_task_type", type text}, {"hs_timestamp", type datetime}, {"hubspot_owner_id", Int64.Type}}),

// I have turned the id column into a list

id = #"Changed Type"[id],

url = "https://api.hubapi.com/crm/v3/objects/companies/search",
headers2 = [#"Content-Type"="application/json", Authorization="Bearer XXX"],
postData = "{
""filterGroups"":[{
""filters"":[
{""propertyName"": ""associations.task"", ""operator"": ""IN"", ""values"":[" & Text.Combine(List.Transform(List.Distinct(id), each """" & _ & """"),",") & "]}]}]}",
response = Web.Contents(
url,
[
Headers = headers2,
Content = Text.ToBinary(postData)
]
),
jsonResponse = Json.Document(response),
results = jsonResponse[results],
#"Converted to Table2" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table2"

 The problem is now that there seems to be a limit when you for example input 1000 values you get a bad request (400), But if you input 100 values it works without a problem, so I think that some type of pagination must be added to the list (of IDs) I'm using as well, please feel free to add your suggestion on how I can write that pagination syntax?

[lbendlin]

that needs to be part of the POST request. 

According to the documentation:

Paging through results

By default, the search endpoints will return pages of 10 records at a time. This can be changed by setting the limit parameter in the request body. The maximum number of supported objects per page is 100.

For example, the request below would return pages containing 20 results each.

Shell script

curl https://api.hubapi.com/crm/v3/objects/contacts/search \
  --request POST \
  --header "Content-Type: application/json" \
  --data '{
    "limit": 20
  }

[darko861]

Yes, I have PUT the limit to 100 in the POST request. But in this case, it does not matter as I have transformed the entire IDs column into a List.

This means that all the values from the list will be used in the request body even if the limit is 100. So if you have 1000 IDs in the list, the response will be Bad request 400. For the request to work, I have to add a maximum of 100 IDs to the list before the Post request is sent. I have a screenshot of the list that is used in the request body below.

I tried your query but it didn't work, so I tried mine by transforming the IDs column to a list and it did work if you have a list of 100 values. This seems to be a very complicated process as I don't see how you can make the list itself more dynamic before you use it inside the POST request. I appreciate your help but I don't know what more we can do.

[lbendlin]

You can easily chop a list into 100 item chunks.

let
    Source = {1752430748..1752435748},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "id"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundDown([Index]/100)),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom"}, {{"Rows", each _, type table [id=nullable text, Index=number, Custom=number]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "body", each "{
	""filterGroups"": [
		{
			""filters"": [
				{
					""propertyName"": ""dealstage"",
					""operator"": ""IN"",
					""values"": [""" & Text.Combine([Rows][id],""",""") & """]
				}
			]
		}
	]
}"),
    #"Added Custom" = Table.AddColumn(#"Added Custom2", "Result", each Web.Contents("https://api.hubapi.com/crm/v3/objects/deals/search",[ Headers = [#"Content-Type" = "application/json"],
Content = Text.ToBinary([body])]))
in
    #"Added Custom"

[darko861]

Hi @lbendlin ,

Everything seems to be working up to a certain point. Please see the print screen below. As soon as I expand the Result column (yellow box) I get the following message:

The information in the red box has also been automatically created.

This is the syntax:

 Source = id,
#"Converted to Table3" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type2" = Table.TransformColumnTypes(#"Converted to Table3",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "id"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundDown([Index]/100)),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom"}, {{"Rows", each _, type table [id=nullable text, Index=number, Custom=number]}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "body", each "{
""filterGroups"": [
{
""filters"": [
{
""propertyName"": ""associations.task"",
""operator"": ""IN"",
""values"": [""" & Text.Combine([Rows][id],""",""") & """]
}
]
}
]
}"),
#"Added Custom" = Table.AddColumn(#"Added Custom2", "Result", each Web.Contents("https://api.hubapi.com/crm/v3/objects/companies/search",[ Headers = [#"Content-Type" = "application/json",Authorization="Bearer XXX"],
Content = Text.ToBinary([body])])),
#"Filtered Hidden Files1" = Table.SelectRows(#"Added Custom", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Result])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"

[lbendlin]

Your very first line is the issue. Keep all your sources in the same partition.

[darko861]

Previously I just copied just a part of my query, this is how it looks like in whole, but the error message is still the same. 

let

    tasksproperties = "hs_timestamp,hs_task_body,hubspot_owner_id,hs_task_subject,hs_task_status,hs_task_priority,hs_task_type,hs_body_preview",

    propertiesQString = "&properties=" & Text.Replace(tasksproperties, ",", "&properties="),

    baseuri = "https://api.hubapi.com/crm/v3/objects/tasks?limit=100"&propertiesQString,

    headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer XXX"]],




    initReq = Json.Document(Web.Contents( baseuri, headers)),

    #"Converted to Table" = Record.ToTable(initReq),

    initData = initReq[results],

    

//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,




//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then




//create a table from those records




gather = (data as list, uri) =>




let

//get new offset from active uri

newOffset = Json.Document(Web.Contents(uri, headers))[paging][next][after],




//build new uri using the original uri so we dont append offsests

newUri = baseuri & "&after=" & newOffset,




//get new req & data

newReq = Json.Document(Web.Contents(newUri, headers)) ,

newdata = newReq[results] ,




//add that data to rolling aggregate

data = List.Combine({data, newdata}),




//if theres no next page of data, return. if there is, call @gather again to get more data




check = if Table.Contains ( Record.ToTable(newReq) , [Name = "paging"] ) = true then @gather (data , newUri) else data




in check,




//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.

outputList = if Table.Contains ( Record.ToTable (initReq) , [Name = "paging"] ) = true then gather( initData , baseuri ) else initData ,




//then place records into a table. This will expand all columns available in the record.

expand = Table.FromRecords(outputList),

    #"Removed Other Columns" = Table.SelectColumns(expand,{"id", "properties"}),

    #"Expanded properties" = Table.ExpandRecordColumn(#"Removed Other Columns", "properties", {"hs_body_preview", "hs_createdate", "hs_lastmodifieddate", "hs_object_id", "hs_task_body", "hs_task_priority", "hs_task_status", "hs_task_subject", "hs_task_type", "hs_timestamp", "hubspot_owner_id"}, {"hs_body_preview", "hs_createdate", "hs_lastmodifieddate", "hs_object_id", "hs_task_body", "hs_task_priority", "hs_task_status", "hs_task_subject", "hs_task_type", "hs_timestamp", "hubspot_owner_id"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded properties",{{"id", type text}, {"hs_body_preview", type text}, {"hs_createdate", type datetime}, {"hs_lastmodifieddate", type datetime}, {"hs_object_id", Int64.Type}, {"hs_task_body", type text}, {"hs_task_priority", type text}, {"hs_task_status", type text}, {"hs_task_subject", type text}, {"hs_task_type", type text}, {"hs_timestamp", type datetime}, {"hubspot_owner_id", Int64.Type}}),

    id = #"Changed Type"[id],




    Source = id,

    #"Converted to Table3" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Changed Type2" = Table.TransformColumnTypes(#"Converted to Table3",{{"Column1", type text}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "id"}}),

    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),

    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundDown([Index]/100)),

    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom"}, {{"Rows", each _, type table [id=nullable text, Index=number, Custom=number]}}),

    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "body", each "{

 ""filterGroups"": [

 {

 ""filters"": [

 {

 ""propertyName"": ""associations.task"",

 ""operator"": ""IN"",

 ""values"": [""" & Text.Combine([Rows][id],""",""") & """]

 }

 ]

 }

 ]

}"),

    #"Added Custom" = Table.AddColumn(#"Added Custom2", "Result", each Web.Contents("https://api.hubapi.com/crm/v3/objects/companies/search",[ Headers = [#"Content-Type" = "application/json",Authorization="Bearer XXX"],

Content = Text.ToBinary([body])])),

    #"Filtered Hidden Files1" = Table.SelectRows(#"Added Custom", each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Result])),

    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

in

    #"Expanded Table Column1"

[lbendlin]

Read about RelativePath and Query.

Web.Contents - PowerQuery M | Microsoft Learn