how to create a query that paginates?

I'm posting the code that we ended up here, because it includes the syntax that will also work in PBI service. This might be useful for other readers as well. The previous syntax in this thread will probably only work in Desktop. Please check this blogpost to find out why: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

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/incident?sysparm_limit=1&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")
in
    #"Expanded result"

That's probably because the starting value has to be adjusted to record-format as well :)

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>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_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

This is amazing ImkeF and is working perfectly:-) Thank you so much.

I have another system I need to do the same with, but the last "automatic" code does not work.

It works perfectly fine with the code where I must know the amount of rows:

let
Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value
   		each  [Counter]<30, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+10,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

But not with the code where it automatically stops when there is no more rows:

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count([WebCall])>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_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

I get the following error:

Pleased to hear :-)

In your new case, the WebCall doesn't return its results in the format of a list, but in a record instead. So we can modify the looping-conditions that it counts the number of fields from the last record: If there are any, then continue else stop like this:

let
Pagination = List.Skip(List.Generate( () => [WebCall={}, Page = 1, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>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_sla?sysparm_limit=10&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

That sounds very good, but I still recieve almost identical error with the new code:

You are the very best ImkeF. It works perfectly. Thank you so much for helping me out. It means the world

Oh one thing is unfortunately wrong:-( It keeps loading rows, eventhough it has reached the end.

What does that mean exactly? That the query never stops?

For debugging could you please do the following?:

1) Evaluate the number of exections needed and adjust the value in the looping condition accordingly:

each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]<=YourNumber+1

2) Send a screenshot of the last result so that we can see what kind of record is returned. If it's not an empty record, but a record with fields but emty values, we have to find a different logic for the stop-condition in the loop-statement.

Hi ImkeF

Sorry for the late reply.

I chose another to table to fetch data from that has 67 rows and adjusted the code accordingly:

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]<=68, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/u_subcategory?sysparm_limit=1&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			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")
in
    #"Expanded result"

Here is a screenshot of the result that shows that the code keeps running eventhough I udjusted the [Counter]:

And a screenshot where I have expanded the result:

So how about this then?:

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each [WebCall]<>null or [Counter]<=68, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/u_subcategory?sysparm_limit=1&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			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")
in
    #"Expanded result"

Hi ImkeF

It is still the same issue. Keeps loading rows with "NULL" values after the last row with data.

Oh my, this is a bit tedius without the actual data... ;)

How about this?:

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each [WebCall][result]<>null or [Counter]<=68, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/u_subcategory?sysparm_limit=1&sysparm_offset="&Text.From([Page])&"")), // retrieve results per call
     			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")
in
    #"Expanded result"

hi Imke,

I am working on similar req like the ones in this post. i am new to coding. I need to pull data from web api which has limit of 2000 rows. but I need help to pull all the records.

this is what the query looks like

let
Source = Json.Document(Web.Contents("https://infotech.attask-ondemand.com/attask/api/v9.0/user/search?fields=*&apiKey=t95jkkcqimdgkkchkjsnwh&method=get")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"latestUpdateNoteID", "layoutTemplateID", "licenseType", "locale", "logTimeInDays", "loginCount", "managerID", "mobilePhoneNumber", "myInfo", "passwordDate", "persona", "phoneExtension", "phoneNumber", "Column1.hasDocuments", "Column1.hasNotes", "Column1.hasProofLicense", "Column1.hasReservedTimes", "Column1.homeGroupID", "Column1.homeTeamID", "Column1.isActive"})
in
#"Expanded Column1"

In general you do this by dynamically creating the URLs with page parameters who will then be called.

Therefor you need a URL-structure where you can integrate the row numbers to pull. 

Is this returned in the first result by any chance?

yes, with above url gives 100 rows by default.It is  a workfront application url and I need to pull all rows from all pages.

So you are saying, that url needs to have some page information like parameters? i only have that url right now. Can you suggest what the next steps be?

You have to look it up in the API-documentation. Try a search for "pagination" there.

I created a query with pagination to retrieve issues from GitHub, following Mark Tiedemann's helpful post here. I thought I'd share for anyone else who may be needing to report on GitHub issues.

Here's my code:

let
 GitHubAPI = "https://api.github.com/search/issues?",
 Repository = "q=repo:OwnerName/RepositoryName+",
 SearchCriteria = "type:issue+state:open&",
 BaseUrl = GitHubAPI & Repository & SearchCriteria,
 
 EntitiesPerPage = 100,
 
 GetJson = (Url) =>
 let RawData = Web.Contents(Url),
 Json = Json.Document(RawData) 
 in Json,

 GetEntityCount = () =>
 let Url = BaseUrl,
 Json = GetJson(Url),
 Count = Json[#"total_count"]
 in Count,

 GetPage = (Index) =>
 let PerPage = "per_page=" & Text.From(EntitiesPerPage),
 Page = "page=" & Text.From(Index + 1),
 Url = BaseUrl & PerPage & "&" & Page,
 Json = GetJson(Url),
 Value = Json[#"items"]
 in Value,
 
 EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
 PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
 PageIndices = { 0 .. PageCount -1 },
 Pages = List.Transform(PageIndices, each GetPage(_)),
 Entities = List.Union(Pages),
 Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
    Table