Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
73 | |
72 | |
39 | |
25 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
43 | |
42 |