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
DBa's avatar
DBa
Helper I
8 years ago

Hi nerd_in_NE

 

At a first glance, I am fairly certain there is something wrong with your link, as the page argument is nowhere in it. 

 

The web contents row should look something like this (highlighted in red) :

 

 

 

each [ WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE&page=1",Query=[page=Text.From([Pages])]])),

 

 

However, please keep in mind that the format is highlighy dependent on the API you are calling. (i.e. how would you call the first page in the api? is 'page' even the argument you would need or is it called somehow else?)

 

Additionally, the following part of the query is also dependent on your API:

 

Table = try Table.FromRecords(WebCall[events])

 More specifically, the [events] name will depend on the name of the record you are extracting (it can be anything, depending on your api)

 

nerd_in_NE's avatar
nerd_in_NE
Frequent Visitor
8 years ago

DBa,

 

Thank you very much for the quick reply!!!

 

I was actually able to resolve my issues by utilizing a solution posted by "Mo_re" in the post below.

 

https://community.airtable.com/t/json-string-only-contains-100-rows-when-connected-to-power-bi-desktop/1500/6

 

For convenience, here's the code:

 

let
    Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1)
in
    Pagination