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
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. (

spocx's avatar
spocx
Icon for Helper I rankHelper I
4 years ago

Hi ImkeF

Would it be possible to create the same kind of error handling for the following code you helped me with a couple of years ago?

let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0], // Start Value
   		each List.Count([WebCall][result])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://service-now.com/api/now/table/incident?sysparm_limit=500&sysparm_offset=1", 
                                    [Query=[sysparm_offset =Text.From([Page])]])), // retrieve results per call
     			Page = [Page]+500,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall"}, {"WebCall"}),
    #"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),

in
    #"Expanded result"