how to create a query that paginates?

Hi Anonymous 

please try:

... Record.Field( [WebCall] [metadata][paging], "next_page" ) ....

Hi, ImkeF  I have gone through the entire post and successfully able to paginate the report. But I am facing this strange issue where my query keeps on running even if it hits the last records.

Below is my query what am I doing wrong here?

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://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Page])]])),
Page = [Page]+1,
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", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"}, {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"})
in
#"Expanded result1"

Hi Anonymous 

this could be due to the result list not being actually empty. Could it be that you need to navigate further down to it?
Otherwise please paste screenshot of the empty list in the allegedly empty items. (add "or [Counter] < YourNumber  to stop the execution at a feasable number)

Hi ImkeF  Thanks for your reply

My query keeps on running even if I change the counter parameter. There are close to 6000 records in the table but query runs beyond these.

let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0], // Start Value
each List.Count([WebCall][result])>0 or [Counter]<3, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Page])]])),
Page = [Page]+1,
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", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded WebCall", {"result", each Text.Combine(List.Transform(_, Text.From)), type text}),
result = #"Extracted Values"{0}[result]
in
result

Also not sure how to add images here

Hi Anonymous 

you have to adjust the condition like so for debugging purposes:

each List.Count([WebCall][result])>0 and [Counter]<3, // Condition under which the next execution will happen

click the camera-icon to add pictures:

Hi ImkeF ,

I was able to run the query successfully suing the condition each List.Count([WebCall][result])>0 and [Counter]<3.

Now my question is how I make sure that it runs till the last record in the table. Here are the images 

Hi Anonymous 

Question is how this looks like for the first item that shall cause the iteration to stop:

Will we see a 0 in Page for example? Or a null?

Then you'd adjust your condition like so:

each [WebCall][result] {0}[Page]>0 and [Counter]<3

This retrieves the first element from the list (a record) and gets the figure in field "Page".

For some reason, your API doesn't return an empty list, you have to find out what else there is to build the condition on.

Thanks, ImkeF  and for others, this might help. I made some changes to the query and now it is running as expected to return the total number of records from the table.

Below is my query

let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0],
each List.Count([WebCall][result])>0 or [Counter]=0,
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Counter])]])), Page = [Page]+1, Counter = [Counter]+100]
) ,1
),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"}, {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"})
in
#"Expanded result1"