Forum Discussion

Paull's avatar
Paull
Frequent Visitor
9 years ago
Solved

Rest API _ Json _ several pages _ automatically call the next_page_URL

Hi Everyone, I am a newbie on powerBi and would need your help to figure out how to automate a rest-API call with a Json response having several pages. The initial URL is the following: https://ap...
  • Paull's avatar
    Paull
    9 years ago

    Hi ImkeF,

     

    Thanks a lot for your reply. It helps a lot.
    I generate a script that do pretty much the same than the video. 
    It looks like this:

    let
 Source = Json.Document(Web.Contents(url, [Headers=[Authorization="your token"]])),
 iterations = Source[total_pages],          // get the information within the response
 url = "you URL", // here goes your URL
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization="yourtoken"]])),
    data = try Source[connections] otherwise null, //get the data of the first page
    next = try Source[next_page_url] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,
 
 GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data]),
  #"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  #"Converti en table"

    It works perfectly.

    Have a good day,

     

    Paul

TerriblyVexed's avatar
TerriblyVexed
Frequent Visitor
5 years ago

So the bit of code that loops is this section

 

    // define the function
    FnGetOnePage =
        (url) as record =>
        let
            Source = Json.Document(Web.Contents(url)),
            data = try Source[entry] otherwise null,
            // link is list of records
            link = try Source[link] otherwise null,
            // link{0} could be "self", "next"
            next = if link{0}[relation] = "next"
                    then link{0}[url]
                    else null,
            res = [Data=data, Next=next]
        in
            res,

 

 

I think the GeneratedList code can stay the same for you, but the FnGetOnePage portion needs to change. Use the Json.Document function to convert your url. This will serve as the "top level" - the line that starts with Source = in the FnGetOnePage. data is the variable that holds your results - so something like data = Source[Data]. next is the variable that holds your iterating logic - so something like next = data[lastid].

This is psuedo code of course so you will have to experiment to find out what your specific needs are.