how to create a query that paginates?

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!

Hi Anonymous 

please try:

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

Thanks! Still not sure how to bypass the 'Error' rows though before getting to expand. 

Please find #2 pics below:

Page 158 works fine, but when I try to load the whole query it gives the above error and doesn't load anything at all. It's only this specific page that errors out for some reason.

Thanks

1) You can either replace errors by a default value. That will keep the rows with errors.

The other option is to remove rows with errors:

Re your pic 2: Wouldn't removing rows with errors works as well here?

1) for all the errors, I actually need all the information from the other columns except the one that is error-ing out (which is actually blank/null). What I am not sure about is why the 'Error' is extending for the whole row, rather than being in only the cell/column with problems 

2) It was a good thought, however when it's a record as in the picture above it doesn't recognize it as an error row and 'Remove errors' or 'Replace errors' basically doesn't do anything. When I expand the column and use 'Remove errors' the query still doesn't load at all :( 

Thanks

Yes, unfortunately there are 2 different types of errors and you seem to have got those who "infect" their neighborhood. Hard to come up with workarounds without actually getting my hands on it. My only suggestion is to reduce the transformations within List.Generate to the minimum (as suggested in one of my previous posts) and trying to circumnavigate the errors after that.

Does your WebCall actually return the data in the table-format that you've shown in your pic or is that the result of a further transformation-step already?

re 2) If you can transform the list to a table (like you've done) it should be possible to remove its last item instead. What does:

List.Range(Pagination, 0, List.Count(Pagination)-1)

return (instead of your current step "Converted to Table")?

1) I think I mentioned it but it was lost in the long comment - the change you previously suggested gives a ''We cannot convert a value of type List to type Table.' error. I've been trying the same idea but I am not sure what else to cut out (also because I'm only at 75% understanding on how the whole query works - I don't understand some of the little details what they do)

2)errors out altogether - I've just noticed now but even though 'converted to table' works, when i click on the 'Pagination' step, the name is 'Error': 

would it work to add an error check statement in this part of the query :

each  Table.RowCount([Table])>0 or [Counter]=0  + or [Table] is error?  I already tried to remove last row from Table = Table.FromRecords(WebCall[events]) but it doesn't do anything at all 

Thanks for the effort!

What does this return? (Removed any non-essential parts)

let
Pagination = 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"&page=1", [Query=[page=Text.From([Pages])]])), // retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = WebCall[customers] // steps of your further query
                      ] 
)
in
    Pagination

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

Hi ImkeF,

Hi All,

I am pulling data from REST API that is paginated with the below M Query,

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

However the last record is a ERROR record and the error message is as follows,

DataSource.Error: Web.Contents failed to get contents from 'http://py-soconnect.fusesport.com/api/members/for-season/250/?page=246' (404): Not Found
Details:
    DataSourceKind=Web
    DataSourcePath=http://py-soconnect.fusesport.com/api/members/for-season/250
    Url=http://py-soconnect.fusesport.com/api/members/for-season/250/?page=246

 I am unable to load the data into the model. Any help with this be highly appreciated.

Thanks in advance.

Hi Anonymous,

there are different ways to handle errors in Power Query (see here for example https://blog.gbrueckl.at/2013/12/error-handling-in-power-query/) 

Skipping the last element of the list (that contains the error) might solve it already.

Or you can integrate a try..otherwise statement in your WebCall:

WebCall = try Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&Text.From([Page])&"")) otherwise #table({""}, {{""}}), // retrieve results per call 

there you might need to replace the empty table with a table that contains the column names of your results.

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