Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.a...
  • Anonymous's avatar
    Anonymous
    5 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
        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!