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

Thank you all so much for this!!!

One point of clarification - The GeneratedList had a typo in it about half way through the discussion.

each [res][Next] <> null

as the evaluator function should be

each [res][Data] <> null

it was this way in several posts and then got switched.

 

I would like to share my version for anyone struggling with Fhir data.

let 
    baseUrl = "https://{myurl}.azurewebsites.net",
    resource = "/Questionnaire",
    search = "?&_total=accurate",
    fullUrl = baseUrl & resource & search,

    // 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,
            
    // use the FnGetOnePage function with List.Generate    
    GeneratedList =
        List.Generate( ()=> 
            [result = FnGetOnePage(fullUrl)],
            // do while
            each [result][Data] <> null,
            // each row of list
            each [result = FnGetOnePage([result][Next])],
            // output
            each [result][Data]
            ),
    // convert to table from the output of function GeneratedList
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expand Table" = funcExpand(GeneratedList)
in
    #"Expand Table"

This uses not only the functions listed here but also the funcExpand function which auto expands all columns that have data.

 

ImkeF  - thank you!

 

Vexed