Handling a Paginated API with Prebuilt Next Page URI in Response [EDITED]

Please see this video for one way to do this.

Power BI - Tales From The Front - REST APIs - YouTube

Regards,

Pat

Hi there,

I'm having the same kind of API (https://manual.yesplan.be/en/developers/rest-api/#pagination), but i'm getting stuck with the "loop".

See below my code, which gets stuck on different pages each time. Most of the time on page 2, but sometimes on page 3.

When I try the URL (for example: https://odeon.yesplan.nl/api/events?page=2&book=4758791937&api_key=********) which throws error 404 via the internet, I get more detailed information:

{"contents":{},"message":"Page key \"2\" is not the current page for the book, which is \"3\"."}        

 When I then change the page=2 to page=3, i do get the correct results. If i then refresh the page again, it throws the above error once again stating that the current page should be 4. Then editing the code to page=4 gives me the correct information.

It's alsmost like as if the query makes a double request, which causes the page index to change to 3 where the query still tries to get page 2.

Do you know how to fix this?

See code:

(baseuri as text) =>
let
initReq = Json.Document(Web.Contents(baseuri)),
initData = initReq[data],
gather = (data as list, uri) =>
let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri))[pagination][next],
//build new uri using the original uri so we dont append offsests
newUri = newOffset&"&api_key=**********",
//get new req & data
newReq = Json.Document(Web.Contents(newUri)),
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[pagination][next] = 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[pagination][next] = 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
expand

(baseurl as text)=>
let
initReq = Json.Document(Web.Contents(baseurl)),
nextUrl = initReq[#"@odata.nextLink"],
initValue= initReq[value],
gather=(data as list, url)=>
let
newReq=Json.Document(Web.Contents(url)),
newNextUrl = newReq[#"@odata.nextLink"],
newData= newReq[value],
data=List.Combine({data,newData}),
Converttotable = Record.ToTable(newReq),
Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
Column_Names=Table.ColumnNames(Pivot_Columns),
Contains_Column=List.Contains(Column_Names,"@odata.nextLink"),
check = if Contains_Column = true then @gather(data, newNextUrl) else data
in
check,
Converttotable = Record.ToTable(initReq),
Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
Column_Names=Table.ColumnNames(Pivot_Columns),
Constain_Column=List.Contains(Column_Names,"@odata.nextLink"),
outputList= if Constain_Column= true then @gather(initValue,nextUrl) else initValue,
expand=Table.FromRecords(outputList)

in
expand

Anonymous Your code helped me a lot to get the values. Here is another way to get the data, when the last API page doesn't bring null but has Blank.

For anyone looking for a way to limit the calls, when there are a lot of pages:

you can add a rowcount limit like below:

... SNIP ...
            //add that data to rolling aggregate
            data = List.Combine({data, newdata}),
            datacount = List.Count(data),
            //if theres no next page of data, return. if there is, call @gather again to get more data
            check = if newReq[links] = null or datacount >= 10000 then data else @gather(data, newUri)
... SNIP ...

in this case it will stop when 10000 rows have been fetched...

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
    source

this 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 🙂