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
Community Champion
8 years ago

Yes. I'm currently working on a blogpost to describe exactly this. Will post linke once finished.

nerd_in_NE's avatar
nerd_in_NE
Frequent Visitor
8 years ago

lmkeF,

 

Thank you so much for being such a great resource - it is truly appreciated!!!

 

I am in a bit of a predicament... I put together a project for a client that pulls data from Airtable via an API into Excel. I am completely finished with the project, except I just discovered that Airtable is only able to pull 100 records at a time! I have been reading through these posts, but I still can't seem to resolve the pagination issue. There are currently almost a 1,000 records and the number of records will grow. I apologize, I'm a bit of a novice writing code in this language!

 

I copied and pasted your code directly into my query, but I'm getting an error (see below). What am I doing wrong? I'm supposed to send this in to the client tomorrow morning so I'm hoping you see this soon (crossing fingers)! Thank you again for your expertise!

 

ERROR: Expression.SyntaxError: Token Comma expected. (See code in red below)

 

let
LINK = "https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE",
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0 , // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents(LINK],Query=[page=Text.From([Pages])]])),
// retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = try Table.FromRecords(WebCall[events]) otherwise Table.FromList({}) // steps of your further query
                      ]   ,
                each [Table] 		
), 1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"
  • 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