Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Pagination with Rest API query and JSON file

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

  

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

 

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Super User
Super User

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.