Forum Discussion
Handling a Paginated API with Prebuilt Next Page URI in Response [EDITED]
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!
I want to do the exact same thing as you want to do. But with other data (i.e. Portfolios, which are not part of the existing power bi asana connector).
The solution I came up with is the following:
A function called fGetAsanaData:
= (startPageUri as text, nextPageUri as text, accessToken as text) =>
let
targetPage = if nextPageUri = "-" then startPageUri else nextPageUri,
source = Json.Document(Web.Contents(targetPage, [Headers=[authorization="Bearer " & accessToken]]))
in
source it checks if a nextPageUri has been passed (I used - as default because it was easy with typecasting and null etc.). If no nextPageUri ist defined it uses the startPageUri. It also adds the accessToken to the authorization header (PAT).
A second function called fPopulateAsanaData:
= (uri as text, accessToken as text) =>
let
source = List.Generate( () =>
[ result = fGetAsanaData( uri, "-", accessToken) , nextPage = result[next_page][uri] , stop = 0 ],
each [stop] = 0,
each [result = fGetAsanaData("-", [nextPage], accessToken), nextPage = [result][next_page][uri], stop = if [result][next_page] <> null then 0 else 1 ],
each [result]
)
in
sourcethis uses List.Generate and loops trough all the data received. when the result does not have a next_page field it sets the stop switch, so that the condition on the next run is false and it will stop retrieving the results.
you can then easily test it like so:
= fPopulateAsanaData ("https://app.asana.com/api/1.0/projects?limit=5&workspace=123456789","yourfanceprivateaccesstoken")
I hope it will help someone someday 🙂