Power Query M Language Error with HubSpot API Pagination

Other Questions HubSpot
1

I’m working on a Power BI integration with HubSpot’s API using Power Query M language. My code works fine in other scenarios, but I’m hitting an issue with the pagination logic.

The problem occurs in this line: finalOutput = try if startReq[paging][next][after] = null then startData else fetchMore(startData, endpointUrl) otherwise error "Processing failed"

Even though startReq[paging][next][after] contains valid data, the function throws an error about missing “results” field in the paging structure. Can anyone help me understand what’s going wrong here?

let
    endpointUrl = "https://api.hubapi.com/crm/v3/objects/contacts?limit=50&archived=false",
    nextPageUrl = "https://api.hubapi.com/crm/v3/objects/contacts?limit=50&archived=false&after=",
    
    bearerToken = "your_token_here",
    contentTypeHeader = "application/json",
    
    requestHeaders = [Headers = [
        #"Content-Type"="application/json", 
        Authorization = "Bearer " & bearerToken]
    ],
    
    startReq = try Json.Document(Web.Contents(endpointUrl, requestHeaders)) otherwise error "Initial API call failed",
    startData = startReq[results],
    
    fetchMore = (recordList as list, currentUrl) =>
        let
            nextToken = try Json.Document(Web.Contents(currentUrl, requestHeaders))[paging][next][after] otherwise error "Token retrieval failed",
            testToken = try Json.Document(Web.Contents(currentUrl, requestHeaders))[paging][next][after],
            nextUrl = nextPageUrl & nextToken,
            
            nextReq = try Json.Document(Web.Contents(nextUrl, requestHeaders)) otherwise error "Subsequent API call failed",
            nextData = nextReq[results],
            combinedList = List.Combine({recordList, nextData}),
            
            hasMore = if nextReq[paging][next][after] = null then combinedList else @fetchMore(combinedList, nextUrl)
        in hasMore,
        
    finalOutput = try if startReq[paging][next][after] = null then startData else fetchMore(startData, endpointUrl) otherwise error "Processing failed"
in
    finalOutput

Any suggestions would be appreciated!

2

your fetchMore func is calling itself with endpointUrl instead of nextUrl. you need to pass the updated URL with the pagination token. change that line in finalOutput to use nextPageUrl + after token, not endpointUrl.

3

Your error handling is masking the real problem. That try...otherwise error wrapper on finalOutput is hiding what’s actually breaking inside fetchMore. The “missing results field” error is probably coming from one of your API calls, but you’re covering it up with that generic “Processing failed” message.

Ditch the try-otherwise wrapper and let the real error show through. You’ll see exactly which API call is failing and why. Also, fetchMore is calling the same API twice (nextToken and testToken lines) - that’s wasteful and might trigger rate limits.

4

You’re overcomplicating this with manual pagination logic that’s a pain to debug and maintain. Power Query M works well for lots of things, but API pagination gets messy fast.

I hit the same wall building data pipelines for our analytics team. Wasted hours debugging M code that should’ve been simple.

What fixed it for me was moving the HubSpot API calls to Latenode. You can set up pagination logic visually, handle errors properly, then just pull clean data into Power BI. No more cryptic M errors.

Latenode has built-in HubSpot connectors that handle pagination automatically. Just configure your endpoint, set batch size, and it manages all the after tokens. Takes 10 minutes to set up vs hours debugging M code.

Then push data to a database or use Latenode’s webhook to feed directly into Power BI. Much cleaner.