Forum Discussion

gotmike's avatar
gotmike
Frequent Visitor
10 years ago

how to create a query that paginates?

I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the resu...
Need Help
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

you can check after each step whether a new should be made by checking if the "after"-value isn't null: 

[Result][meta][cursors][after] <> null 

For the development/testing phase , I've added another condition as well, that prevents running this query into an an infinite loop: 

[Counter] < 200. You should delete that condition once the query runs as you expect it.: 

let
    Source = "    {#(cr)#(lf)    ""events"": [#(cr)#(lf)        {#(cr)#(lf)            ""id"": ""X0001"",#(cr)#(lf)            ""created_at"": ""2020-03-24T08:04:27.593Z"",#(cr)#(lf)            ""resource_type"": ""mandates"",#(cr)#(lf)            ""action"": ""cancelled"",#(cr)#(lf)            ""links"": {#(cr)#(lf)                ""mandate"": ""MD0005CS4YKG3P""#(cr)#(lf)            },#(cr)#(lf)            ""details"": {#(cr)#(lf)                ""origin"": ""bank"",#(cr)#(lf)                ""cause"": ""mandate_cancelled"",#(cr)#(lf)                ""scheme"": ""bacs"",#(cr)#(lf)                ""reason_code"": ""ADDACS-1"",#(cr)#(lf)                ""description"": ""The mandate was cancelled at a bank branch.""#(cr)#(lf)            },#(cr)#(lf)            ""metadata"": {}#(cr)#(lf)        },#(cr)#(lf)#(tab)],#(cr)#(lf)    ""meta"": {#(cr)#(lf)        ""cursors"": {#(cr)#(lf)            ""before"": null,#(cr)#(lf)            ""after"": ""X0001""#(cr)#(lf)        },#(cr)#(lf)        ""limit"": 500#(cr)#(lf)    }#(cr)#(lf)}",
    #"Parsed JSON" = Json.Document(Source),
    Custom2 = List.Generate( () =>
        [Result = #"Parsed JSON", Counter = 0],
        each [Result][meta][cursors][after] <> null and [Counter] < 1000,
        each [
            Result = Json.Document(Web.Contents("https://www.blah.com/events?after=" & [NextPage])),
            NextPage = Result[meta][cursors][after],
            Counter = [Counter] + 1
        ]
    )
in
    Custom2

 

You can also check out this video to learn more about the technique: https://www.youtube.com/watch?v=vhr4w5G8bRA

 

 

dekissmokton's avatar
dekissmokton
Regular Visitor
4 years ago

Thank you very much ImkeF, your answer greatly helped me get the list of pagination tokens for Twitter API v2!

If I may, for your code to work and based on what you wrote it should look like this :

 

    #"Parsed JSON" = Json.Document(Source),
    Custom2 = List.Generate( () =>
        [Result = #"Parsed JSON", Counter = 0],
        each [Result][meta][cursors][after] <> null and [Counter] < 1000,
        each [
            Result = Json.Document(Web.Contents("https://www.blah.com/events?after=" & Result[meta][cursors][after])),
            Counter = [Counter] + 1
        ]
    )
in
    Custom2

 

Personnally, my code looks like this :

    Source = 
        List.Generate( 
        () =>
        [Result = Fx_GetData(P_URL)],
        each [Result]<>null,
        each [Result = try Fx_GetData(P_URL&"&pagination_token="&[Result][meta][next_token]) otherwise null]
    ),

This way it handles error cases.

Thank you again 🙏