Power BI Integration with HubSpot API Pagination Error

Other Questions HubSpot
1

I’m trying to integrate HubSpot API with Power BI and I’ve hit a roadblock. My code, which has worked in the past, is now failing when I attempt to manage pagination.

Specifically, I’m having issues with this line: finalOutput = try if firstReq[paging][next][after] = null then firstData else processPages(firstData, baseUrl) otherwise error "Processing failed". I’m confused because firstReq[paging][next][after] seems to hold data, yet the function is unexpectedly referencing “results” instead of the paging information.

Here’s a snippet of my code:

let

baseUrl = "https://api.hubapi.com/crm/v3/objects/contacts?limit=50&archived=false",
baseUrlWithAfter = "https://api.hubapi.com/crm/v3/objects/contacts?limit=50&archived=false&after=",

accessToken = "xxxxxxxxxxxxxxx",
contentType = "application/json",

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

firstReq = try Json.Document(Web.Contents(baseUrl, requestHeaders)) otherwise error "Initial API call failed",

firstData = firstReq[results],

processPages = (currentData as list, url) =>
    let
        nextToken = try Json.Document(Web.Contents(url, requestHeaders))[paging][next][after] otherwise error "Token retrieval failed",
        nextUrl = baseUrlWithAfter & nextToken,
        nextReq = try Json.Document(Web.Contents(nextUrl, requestHeaders)) otherwise error "Next page request failed",
        nextPageData = nextReq[results],
        combinedData = List.Combine({currentData, nextPageData}),
        hasMorePages = if nextReq[paging][next][after] = null then combinedData else @processPages(combinedData, nextUrl)
    in hasMorePages,

finalOutput = try if firstReq[paging][next][after] = null then firstData else processPages(firstData, baseUrl) otherwise error "Processing failed"

in
finalOutput

Any thoughts on what could be wrong? Thank you for your assistance!

2

Been wrestling with HubSpot API integrations for years and this pagination mess is exactly why I stopped doing complex API work directly in Power BI.

Your recursive function’s making redundant API calls and the token logic is mixed up. But honestly, fixing this M language spaghetti just patches a bigger problem.

I moved all my HubSpot integrations to Latenode after similar headaches. Set up the HubSpot connection, add a simple pagination loop that handles token management automatically, then export the clean dataset wherever you need it.

No more debugging cryptic Power BI errors or writing recursive functions that break when HubSpot changes their response format. The visual workflow shows exactly what’s happening at each step.

Latenode handles all the rate limiting and pagination quirks out of the box for HubSpot. You focus on what data you actually need instead of fighting API mechanics.

Saved me about 20 hours on my last contacts sync project compared to Power BI.

3

you’re overcomplicating the token passing. the issue is you’re checking firstReq[paging][next][after] but then just sending baseUrl to processPages instead of actually building the next url with that token. also, your null check will fail if the paging object doesn’t exist.

4

Hit this exact same pagination bug with HubSpot’s contacts API last month. Your problem’s in the conditional logic - you’re checking if firstReq[paging][next][after] is null, but then you pass baseUrl to processPages instead of the actual next page URL with the after token.

processPages should get the ready-made next URL, not try to rebuild it. You’re checking one thing but processing something completely different.

Also heads up - HubSpot’s API response changes based on result count. If you’ve got under 50 contacts total, the paging object just won’t exist at all instead of having null values. That’ll break your null check with a field access error.

I’d pull the pagination token extraction into one spot per response and check that the paging object actually exists before digging into nested properties.

5

your logic’s backwards here. you’re checking firstReq[paging][next][after] but then calling processPages with baseUrl again instead of the next page URL. also, that null check will break if the paging object doesn’t exist.

6

Hit the same issue with HubSpot’s API recently. Your pagination logic’s grabbing the after token from the wrong request. In processPages, you’re hitting the API twice per loop - that’s wasteful and messes up which response has your pagination data.

I fixed it by passing the pagination token as a parameter instead of making extra calls. Also check if HubSpot tweaked their response structure lately - they’ll sometimes return different paging objects depending on the endpoint or when you’ve hit the end.

Log the actual JSON response first to see what’s inside firstReq. Sometimes the paging object just disappears on the last page instead of showing null values, which would break your null check.

7

Your processPages function has a big issue - it’s making an unnecessary API call just to grab the next token. You already got that paging info from firstReq, so why call the API again?

This mixes up data from different responses and overcomplicates everything. Just use the paging info you already have from each response.

Honestly though, Power BI’s M language is a nightmare for complex API pagination. I’ve fought with HubSpot integrations before and the error handling becomes a mess quickly.

I ended up ditching this approach and moved everything to Latenode. You can build the HubSpot API calls there, handle pagination visually, then feed clean data to Power BI. No more M language headaches.

Latenode handles tokens, pagination loops, and errors automatically. Plus you can actually see what’s happening instead of debugging cryptic Power BI messages.

Just set up your HubSpot connector, add a pagination loop, and export however you want. Much cleaner than forcing Power BI to handle heavy API work.