cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wjones
Frequent Visitor

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

I am trying to access data from the Asana API.

 

When making a call to the api for say, "projects," 

GET /projects?limit=5&workspace=xxxxxxx

Or in PBI, 

Json.Document(Web.Contents("https://app.asana.com/api/1.0" & "/projects?" & "limit=5" & "&workspace=xxxxxxx", [Headers=[#"Content-Type"="application/json", Authorization="Bearer somePAT"]]))

The api will respond with a list of projects and their attributes, along with a "next_page" object like so: 

{
  "data": [
    {
      "gid": "12345678910111212",
      "name": "Project 1",
      "resource_type": "project"
    },
    {
      "gid": "12345678910111213",
      "name": "Project 2",
      "resource_type": "project"
    }

.
.
.
], "next_page": { "offset": "someSequence", "path": "/projects?limit=5&workspace=xxxxxxxx&offset=someSequence", "uri": "https://app.asana.com/api/1.0/projects?limit=5&workspace=xxxxxxxx&offset=someSequence" } }

Obviously I want to extract the list of projects, but since pagination is compulsory with a maximum of 100 records per call, I need a way to successively make calls based on the contents of "next_page." The "uri" is a fully formed api, so theres no need to do any calculations with offsets or limits. I just need to be able to access the projects, pull the next uri, and then make another call using that next uri. This needs to happen until a "next_page" is no longer returned in the response.

 

I've done a decent bit of research and have yet to find a workable solution.

 

What's the best way to go about this in Power BI? Any tips or useful code snippets or references to appropriate documentation would be greatly appreciated.

 

=================================================

EDIT:

I have since played around a bit and am trying to use List.Generate to solve my problem. In pseudocode, this is my intention

records = {set of records returned from first api call}
while that same call hasNextPage():
    build the next api call from the original uri 
    append the returned records to our existing set of records

 

Here's what I've come up with so far

let
    uri = "https://app.asana.com/api/1.0/projects?limit=100&workspace=xxxxxxx",
    headers = [Headers=[#"Content-Type"="application/json", Authorization="somePAT"]],

    //Loop over pages
    alldata = List.Generate(
        () =>  Json.Document(Web.Contents(uri, headers))[data],
        each hasNextPage(uri),
//how do I change the uri here, to apply the new offset each iteration? each getRecords(uri & "&offset=" & getNextOffset(uri & "&offset=" & Json.Document(Web.Contents(uri, headers))[next_page][offset])) ), output = Table.FromList(alldata, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in output

 

Where hasNextPage(uri) makes a call to the uri, then checks if the next_page object is just null, signifying the end of available pages of records. getRecords just returns raw data from source. 

let
    Source = (uri) => let
        Source = Json.Document(Web.Contents(uri, [Headers=[#"Content-Type"="application/json", Authorization="Bearer somePAT"]])),
        data = Source[data]
    in data
in Source

 

This now gives me a single column filled with lists, each of which contains 100 records. The problem is that it runs forever and returns hundreds of thousands of records (far more than SHOULD be returned). It never actually even loads all the rows if I apply changes.

 

Can someone help me debug the List.Generate function I'm running above?

 

1 ACCEPTED SOLUTION
wjones
Frequent Visitor

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
    expand

This returns a fully expanded table of records from from all pages of data.

 

Extensions of functionality or efficieny modifications are more than welcome!

 

View solution in original post

5 REPLIES 5
wjones
Frequent Visitor

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
    expand

This returns a fully expanded table of records from from all pages of data.

 

Extensions of functionality or efficieny modifications are more than welcome!

 

View solution in original post

mahoneypat
Super User IV
Super User IV

Please see this video for one way to do this.

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

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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. 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors