Hubspot and Power BI Coding Issue

Developer Support APIs & Integrations
1

I have been playing with some code I have used elsewhere successfully on the hubspot API but it seems to be failing and I cant figure out why. The whole code is below but I seem to fail on “outputList = try if initReq[paging][next][after] = null then initData else gather(initData, apiUrl) otherwise error “Failed outputList””. initReq[paging][next][after] does seem to have data in it however the function fails due to refering to “results” which does not have the paging data in it. Any thoughts on how to approach this please ? TIA

let

apiUrl = "https://api.hubapi.com/crm/v3/objects/deals?limit=100&archived=false",
apiUrlnew = "https://api.hubapi.com/crm/v3/objects/deals?limit=100&archived=false&after=",

queryStringaccessTokenvar = "xxxxxxxxxxxx",
queryStringContentTypevar = "application/json",
queryStringnew = "",

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

initReq = try Json.Document(Web.Contents(apiUrl, headers)) otherwise error "Failed to retrieve data from the API 1st Pass",

// Convert JSON data to a table
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
 //build new uri 
 newUrinextpage = try Json.Document(Web.Contents(uri, headers))[paging][next][after] otherwise error "Failed to retrieve data from the API 2nd Pass",
 debugResponse = try Json.Document(Web.Contents(uri, headers))[paging][next][after],
 newUri = apiUrlnew & newUrinextpage,
 //get new req & data
 newReq = try Json.Document(Web.Contents(newUri, headers)) otherwise error "Failed to retrieve data from the API 3rd Pass",
 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 newReq[paging][next][after] = null then data else @gather(data, newUri)
 in check,
 //before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
 outputList = try if initReq[paging][next][after] = null then initData else gather(initData, apiUrl) otherwise error "Failed outputList"
in
 outputList

Thanks

2

Hi @ZAbdeen,
Thank you for reaching out to the Community!
I understand that you’re encountering an issue with your HubSpot API code where the function fails at the outputList step due to referencing “results” instead of the paging data, even though “initReq[paging][next][after]” does contain data
I’d like to tag in some of our Community Champions to see if they have any ideas on this one -- @Bortami @mangelet @Gonzalo Have any of you seen this before?
Cassie, Community Manager

3

Hi @ZAbdeen

You’re running into the classic Power Query recursion + HubSpot API pagination snag.

The key detail is that HubSpot’s CRM v3 endpoints return paging info at the root level of the response, while your function is only checking inside results. That’s why initReq[paging][next][after] exists, but the recursive gather is tripping when it references results alone.

Two quick adjustments usually help:

  1. Make sure you always pull paging[next][after] from the raw response object, not from results. In your gather step, you’re doing both in one expression. Break it into two: store newReq = Json.Document(Web.Contents(…)), then newdata = newReq[results], and separately after = try newReq[paging][next][after]. This keeps the scopes clean.
  2. Protect against missing paging blocks. HubSpot only returns paging when there’s more data. Wrapping in try … otherwise null before concatenating avoids the “reference error” when you hit the final page. See HubSpot’s docs here for the paging pattern:
    (Understanding the CRM APIs - HubSpot docs )
    (Accounts Dashboard | HubSpot )

That should get your outputList working with recursive calls.

And just to note: some teams skip writing their own pagination logic in Power BI altogether. They’ll connect HubSpot data into a sync layer that handles paging, rate limits, and retries automatically. A workflow automation platform like Stacksync does exactly that, so by the time Power BI connects, you’re querying a clean, real-time table without worrying about the API quirks.

Hope this helps.