Forum Discussion
Handling a Paginated API with Prebuilt Next Page URI in Response [EDITED]
- Anonymous5 years ago
Solved it using a custom recursive function which grabs the offset, builds the next page's uri, appends the data to a rolling total of the data, then makes another call with the new uri, provided a next page exists to be called upon.
(baseuri as text) => let headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer APIKEY"]], initReq = Json.Document(Web.Contents(baseuri, headers)), initData = initReq[data], //We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records, //then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then //create a table from those records gather = (data as list, uri) => let //get new offset from active uri newOffset = Json.Document(Web.Contents(uri, headers))[next_page][offset], //build new uri using the original uri so we dont append offsests newUri = baseuri & "&offset=" & newOffset, //get new req & data newReq = Json.Document(Web.Contents(newUri, headers)), newdata = newReq[data], //add that data to rolling aggregate data = List.Combine({data, newdata}), //if theres no next page of data, return. if there is, call @gather again to get more data check = if newReq[next_page] = null then data else @gather(data, newUri) in check, //before we call gather(), we want see if its even necesarry. First request returns only one page? Return. outputList = if initReq[next_page] = null then initData else gather(initData, baseuri), //then place records into a table. This will expand all columns available in the record. expand = Table.FromRecords(outputList) in expandThis returns a fully expanded table of records from from all pages of data.
Extensions of functionality or efficieny modifications are more than welcome!
Please see this video for one way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Regards,
Pat
- Anonymous5 years agoNot applicable
Great video! Unfortunately, what you explain only works if you can predetermine counts and offsets. I am not given a "total count" on which I can base successive requests. Each request returns a next_page, which gives me the next random offset token:
"next_page": { "offset": "yJ0eXAiOiJKV1QiLCJhbGciOiJIRzI1NiJ9", "path": "/projects?limit=5&workspace=xxxxxxxx&offset=yJ0eXAiOiJKV1QiLCJhbGciOiJIRzI1NiJ9", "uri": "https://app.asana.com/api/1.0/projects?limit=5&workspace=xxxxxxxx&offset=yJ0eXAiOiJKV1QiLCJhbGciOiJIRzI1NiJ9" }So for each request, I need to pull the actual data into a table as well as grab the offset (or more easily, the fully constructed uri), and append the returned data to the same table.
In python, I'd just have a loop to run through each json return, grab the offset and make the next call. Thats the functionality I'd like here, but I'm having trouble translting that to a functional language like M/PQ.
- mahoneypat5 years ago
Microsoft Employee
If you know how many to expect, you can hard code it in to the List.Numbers function (if you can't dynamically use a $count to get it). You could also go past it and see which calls have error or are empty.
Pat
- Anonymous5 years agoNot applicable
Like I said above, I dont have the ability to premptively find out a count of exdisting records. I just need to continually make the call, check for an offset in next_page, then append that offset to my uri and repeat until next_page = null in the returned json.
Ive made some edits to my original question. Could you take a look at that? I'm relying on List.Generate and having some trouble.