Pagination with Rest API query and JSON file

[mpontrelli]

Hello, and welcome to my first post ever.

With some help, I have stumbled my way through authenticating and pulling data via Rest from our application server.  Now I would like to implement pagination, so that I can download more records than are allowed by our current record limit.  I have found many great examples for doing this when connecting to web content, or using OData, but I am pulling JSON data and our app server does not support OData.  The application server I am hitting only supports the limit and offset parameters for pagination.

This is my current function based on Matt Masson's example at https://www.mattmasson.com/2014/11/iterating-over-an-unknown-number-of-pages-in-power-query/ but modified for my purposes.

This works fine if I provide the offsets manually to the function.

(page as number) as table =>

let

APIQuery = "%28%27Submit+Date%27%3E%2210%2F1%2F2018%22AND%27Case+Type%27%3D%22Incident%22%29",

Source = (Web.Contents((Server)&"/api/arsys/v1/entry/HPD:Help%20Desk?offset=" & Number.ToText(page) &"&q=" & APIQuery & "&limit=100",
[Headers=[Authorization=(Text.Combine({"AR-JWT",#"GetAPIKey"}," ")), ContentType="application/json", CacheControl="no-cache", UserAgent="PowerBI", Cookie="COOKIENumber.00000480.0000", AcceptEncoding="gzip, deflate",TransferEncoding="chunked"]])),
#"Imported JSON" = Json.Document(Source,1252),
entries = #"Imported JSON"[entries],
#"Converted to Table" = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"values"}, {"Column1.values"}),
#"Expanded Column1.values" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.values",{"Incident Number", "Submit Date", "Case Type", "Status"},{"Incident Number", "Submit Date", "Case Type", "Status"})

in
#"Expanded Column1.values"

This is the main query based on the same example

let
PageRange = {1..10000},
Source = List.Transform(PageRange, each try {_, IncidentQuery(_)} otherwise null),
First = List.FirstN(Source, each _ <> null),
Table = Table.FromRows(First, {"Page", "Column1"}),
#"Expanded Column1" = Table.ExpandTableColumn(Table, "Column1",{"Incident Number", "Submit Date", "Case Type"},{"Incident Number", "Submit Date", "Case Type"})

in
#"Expanded Column1"

Obviously this passes back sequential numbers incremented by one because it was designed for pages, not offsets.  I neet to increment the value by 100 for each loop to match the limit parameter.  I think I have been close a few times, but I just do not have a solid grasp of M.  Any help would be greatly appreciated.

Thanks

Mike

[mpontrelli · Accepted solution]

I was able to solve my problem generating the offset with the List.Numbers function instead of a specified list.

    OffsetValue = List.Numbers(0,100000,1000),  this give me a list from 0 to one hundred thousand in increments of 1000 which works perfectly.

[blopez11]

I used the following as a basis for similar to what you are looking

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

Basically, you need to be able to know the total # of records, # of records returned per page, then based on that figure out how many iterations to call the api

The rest api I used would rerturn the total # of records that were in the entity I was querying, and since I could set the page size, I calculated the # of iterations it would take to pull in all of the records

Hope the above referenced link helps, and it did for me

Good luck