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

In case someone else has similar problems: The solution for this case looked like so:

 

let
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"

The Table expression contains an error-handler that returns an empty table in case of error. (

DBa's avatar
DBa
Helper I
8 years ago

Thanks Imke

 

Additionally, in the case where some rows were error-ing out, taking out 

each [Table]

Allows the step by step expansion of the columns, which no longer contains error Rows.


I suspect the issue is Table.FromRecords in the below code:

Table.FromRecords(WebCall[events])

When there are record/columns within the API that are blank, Table.FromRecords doesn't know how to treat them and hence creates a whole row of errors.

 

Thanks for all the help!