Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |