Power BI HubSpot API Integration Error with Pagination

Other Questions HubSpot
1

I’m trying to integrate the HubSpot API with Power BI and encountering a puzzling issue. I have a piece of code that has worked for me in other applications, but now it keeps failing at a certain step.

The problem occurs when I check if there are additional pages to load. My code errors out when I verify if mainRequest[paging][next][after] is null. Interestingly, this field does contain data, yet the function struggles to locate the “results” field when accessing the pagination information.

Here is the code I’m currently using:

let
    baseUrl = "https://api.hubapi.com/crm/v3/objects/contacts?limit=50&archived=false",
    paginatedUrl = "https://api.hubapi.com/crm/v3/objects/contacts?limit=50&archived=false&after=",
    
    accessToken = "xxxxxxxxxx",
    contentType = "application/json",
    
    requestHeaders = [Headers = [
        #"Content-Type"="application/json", 
        Authorization = "Bearer " & accessToken]
    ],
    
    firstCall = try Json.Document(Web.Contents(baseUrl, requestHeaders)) otherwise error "Initial API call failed",
    firstPageData = firstCall[results],
    
    fetchMorePages = (currentData as list, currentUrl) =>
        let
            nextPageToken = try Json.Document(Web.Contents(currentUrl, requestHeaders))[paging][next][after] otherwise error "Next page token retrieval failed",
            nextUrl = paginatedUrl & nextPageToken,
            nextCall = try Json.Document(Web.Contents(nextUrl, requestHeaders)) otherwise error "Pagination call failed",
            nextPageData = nextCall[results],
            combinedData = List.Combine({currentData, nextPageData}),
            continueCheck = if nextCall[paging][next][after] = null then combinedData else @fetchMorePages(combinedData, nextUrl)
        in continueCheck,
    
    finalResult = try if firstCall[paging][next][after] = null then firstPageData else fetchMorePages(firstPageData, baseUrl) otherwise error "Output processing failed"
in
    finalResult

Do you have any insights into what might be going wrong? The pagination data looks intact, but there seems to be an issue when I attempt to access it.

2

hey ryanl, it seems like ur trying to fetch the next page token when it’s not needed. save that paging info from ur last response and just reuse it. that should fix those pesky errors you’re struggling with!

3

Your pagination logic is the problem here. You’re making an extra API call in fetchMorePages just to grab the next page token, but you already have that info in your current response. Pass the whole response object to your recursive function instead of building a new URL.

I’ve hit this same issue with paginated APIs - sometimes the last page is missing fields and breaks everything. Fix it by extracting both your results and paging info from the same call. No need for separate requests.

4

Your fetchMorePages function is making double API calls because you’re fetching pagination tokens you already have from previous responses. This creates a loop where you’re hitting the API twice per iteration - totally unnecessary. I ran into this exact issue with HubSpot’s API last year. Here’s what fixed it: restructure your function to accept the full response object, not just the URL. Pull both results and pagination info from each single API call, then pass the complete response to the next iteration. One more thing - HubSpot sometimes returns broken pagination objects on the final page, which throws null reference errors. Add error handling to check if the paging object exists before accessing nested properties. Otherwise your function will crash when it hits the last page.