how to create a query that paginates?

Hi Anonymous 

please try:

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

Hi ImkeF,

Many thanks for your solution. I tried the following code and able to pull 245 records but there are 246 records and the last record is being ignored.

let
Pagination = List.Skip(List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each try Record.Field([WebCall],"next")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&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"

 Could you please suggest a way around for this.

Thanks in advance.

I thought your problem was an error-message in the last item.

The code you're using skips the first element of the list. So this would return the full list:

let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each try Record.Field([WebCall],"next")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
)),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

If that doesn't work and still the last element of your items is missing, you have to check the condition under which the next iteration happens: 

each try Record.Field([WebCall],"next")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		

That has to match your specific API 

Thanks a lot ImkeF.Working perfect now.

Hi ImkeF,

I have the same issue. I try to use the code I found on page 1 but it doesn't work for me since my source is JSON but the sample code in page 1 was XML. My source looks like this: https://shopname.myshopify.com/admin/api/2019-10/products.json?limit=250

Do you know what I should do to create a query that paginates?

let
    /*
      Purpose: Request all timesheet records from Dovico      
    */

    /*
      Generate a list of XML objects based on the Dovico's pagination of its return set
      The initial URL is:  https://api.dovico.com/TimeEntries/?version=5
      Pagination is controlled by return randomly-generated values in URI attributes PrevPageURI and NextPageURI
      The limit in each direction is denoted by the value "N/A"
      Date filtering can be introduced by using:  https://api.dovico.com/TimeEntries/?daterange=2016-04-01%202016-04-15&version=5
      See the Dovico API for more information:  http://apideveloper.dovico.com/Time+Entries
    */
    DataList = List.Generate(
                    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?version=5",ImportedXML=""],
                    each Text.PositionOf([SourceURI],"N/A") = -1,
                    each [
        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=CLIENT_TOKEN&user_token=USER_TOKEN"""]]),
        ImportedXML = Xml.Tables(Source,null,1252),
        ChangeType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text}, {"NextPageURI", type text}}),
        SourceURI = Record.Field(Table.First(ChangeType),"NextPageURI"),
        TimeEntries = ChangeType{0}[TimeEntries],
        TimeEntry = TimeEntries{0}[TimeEntry]
        ],
    each [[SourceURI],[ImportedXML]]
    ),

    /*
      Now expand and shape the list of XML objects into a single table of data
    */
    ConvertToTable = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"ImportedXML"}, {"ImportedXML"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([ImportedXML] <> "")),
    #"Expanded ImportedXML" = Table.ExpandTableColumn(#"Filtered Rows", "ImportedXML", {"TimeEntries"}, {"TimeEntries"}),
    #"Expanded TimeEntries" = Table.ExpandTableColumn(#"Expanded ImportedXML", "TimeEntries", {"TimeEntry"}, {"TimeEntry"}),
    #"Expanded TimeEntry" = Table.ExpandTableColumn(#"Expanded TimeEntries", "TimeEntry", {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}, {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded TimeEntry",{{"Date", type date}, {"TotalHours", type number}, {"Description", type text}}),
    #"Expanded Project" = Table.ExpandTableColumn(#"Changed Type1", "Project", {"Name"}, {"Project.Name"}),
    #"Expanded Task" = Table.ExpandTableColumn(#"Expanded Project", "Task", {"Name"}, {"Task.Name"}),
    #"Expanded Employee" = Table.ExpandTableColumn(#"Expanded Task", "Employee", {"Name"}, {"Employee.Name"}),
    #"Expanded Client" = Table.ExpandTableColumn(#"Expanded Employee", "Client", {"Name"}, {"Client.Name"}),
    #"Expanded Sheet" = Table.ExpandTableColumn(#"Expanded Client", "Sheet", {"Status"}, {"Sheet.Status"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Sheet",{{"Client.Name", "Customer"}, {"Sheet.Status", "Approval status"}, {"Project.Name", "Project"}, {"Task.Name", "Task"}, {"Employee.Name", "Raw Name"}, {"TotalHours", "Effort (hrs)"}}),
    #"Added Week Ending" = Table.AddColumn(#"Renamed Columns", "Week ending", each Date.EndOfWeek([Date],Day.Saturday), type date),
    #"Added Name" = Table.AddColumn(#"Added Week Ending", "Name", each Text.Combine({List.Last(Text.Split([Raw Name],",")), List.First(Text.Split([Raw Name],","))}, " "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Name",{"Raw Name"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Name", Text.Trim}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Trimmed Text",{"Name", "Date", "Week ending", "Customer", "Project", "Task", "Effort (hrs)", "Description", "Approval status"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Description", "Approval status"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Date", Order.Descending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Customer", type text}, {"Project", type text}, {"Task", type text}})
in
    #"Changed Type"

Hi xxyb720 

the technique of pagination is irrespective of the returned format. 
If you have problems with parsing out the needed values from your JSON, you would have to post sample data here and let me know which information you need from it to further pass into the pagination logic.

ImkeF - I have been following your guidance throughout this thread, your help has been really insightful (and a huge benefit to the Power BI community).  However, I am still struggling with how to isolate the value that gives me the next page for the API call, here is the response from the API:

{
"metadata": {
"filtering": {},
"paging": {
"per_page": 100,
"current_page": 2,
"next_page": 3, <-- Need this value
"prev_page": 1,
"total_pages": 81,
"total_count": 8001
},
"sorting": {
"sort_by": "updated_at",
"sort_direction": "DESC NULLS LAST"
}
},
"data": [
{...}]

In your code from the prior entries, you suggested doing this:

let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
each try Record.Field([WebCall],"???next_page???")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api.sample.com/v2/activities/actions.json?per_page=100&include_paging_counts=true&page="&Text.From([Page])&"",[Headers=[Authorization="<APIKEY>"]])), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
)),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"Column1.WebCall", "Column1.Page", "Column1.Counter"})
in
#"Expanded Column1"

My question is the "try Record.Field([WebCall]."next_page") step, how do I grab the value under metadata/paging/next_page in the json?  My Power M game is not that strong.

Hi Anonymous ,

thanks and happy to help. But the JSON you've posted isn't valid.

Please paste valid JSON and I will follow this up.

Thanks.

ImkeF - Sorry about that, forgot the closing brackets.

{
    "metadata": {
        "filtering": {},
        "paging": {
            "per_page": 100,
            "current_page": 2,
            "next_page": 3,
            "prev_page": 1,
            "total_pages": 81,
            "total_count": 8034
        },
        "sorting": {
            "sort_by": "updated_at",
            "sort_direction": "DESC NULLS LAST"
        }
    },
    "data": [
        {
            "id": 4926173,
            "to": "XXXXXXXXXXX",
            "duration": 106,
            "sentiment": "5-Referred to Other Contact",
            "disposition": "1-Talked to Contact",
            "created_at": "2020-02-26T16:44:37.875353-05:00",
            "updated_at": "2020-02-26T16:44:38.109183-05:00",
            "recordings": [
                {
                    "url": "https://sample.com",
                    "status": "completed",
                    "recording_status": "completed"
                }
            ],
            "user": {
                "_href": "https://sample.com",
                "id": 1555
            },
            "action": null,
            "called_person": {
                "_href": "https://sample.com",
                "id": 699379
            },
            "crm_activity": {
                "_href": "https://sample.com",
                "id": 23514150
            },
            "note": {
                "_href": "https://sample.com",
                "id": 1784343
            },
            "cadence": null,
            "step": null
        }
    ]
}

  Thanks! 

Hi Anonymous  

you can navigate JSON records fields with square brackets like so:

#"Parsed JSON"[metadata][paging][next_page]

 please see attached file

ImkeFI knew it was something simple, thank you! 

ImkeF - I spoke too soon.  I am having trouble with the following line from the code you gave in previous posts:

let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value

   		each try Record.Field([WebCall],)<>null otherwise false or [Counter]=0,  //how do I reference the [metadata][paging][next_page] value in this expression?


   		each [ WebCall = Json.Document(Web.Contents("https://api.sample.com/v2/activities/stuff.json?per_page=100&include_paging_counts=true&page="&Text.From([Page])&"",[Headers=[Authorization=""]])), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
))
in
    Pagination

Currently, when I run this it fails to return even the first page of data.  Sorry, I had to remove the API key and real URL, but the structure is similar to the post above.

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"