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

FPackermanGTA's avatar
FPackermanGTA
Advocate I
4 years ago

OK I have a similar issue trying to get this to work.

 

I have an api which will only return 200 records but rather than the next url it gives me the lastid which I think I can tag onto the url to then give me the next page of results. So the simple code below return one page of data and not sure what the best way would be to go about doing this.

 

 

let

     apiUrl = "myUrl",

     options = [Headers =[
					#"Accept"="application/json",
					#"Authorization"="myKey"]],

     result = Web.Contents(apiUrl , options),
    #"Imported JSON" = Json.Document(result,65001)
in
    #"Imported JSON"

 

The url would then look like this to search the next set and so on. I think thats how it would work anyway.

 

https://myURL?after_id=1494

 

The API says

Pass the last id received, to receive records after it. Null can be passed to start at the beginning.

 

Any help much appreiciated and I know this is an old thread but here goes.....

 

Cheers

 

Paul.

  • TerriblyVexed's avatar
    TerriblyVexed
    Frequent Visitor
    4 years ago
    FPackermanGTA wrote:

    OK I have a similar issue trying to get this to work.

     

    I have an api which will only return 200 records but rather than the next url it gives me the lastid which I think I can tag onto the url to then give me the next page of results. So the simple code below return one page of data and not sure what the best way would be to go about doing this.

     

     

     

    let

     apiUrl = "myUrl",

     options = [Headers =[
					#"Accept"="application/json",
					#"Authorization"="myKey"]],

     result = Web.Contents(apiUrl , options),
    #"Imported JSON" = Json.Document(result,65001)
in
    #"Imported JSON"

     

     

    The url would then look like this to search the next set and so on. I think thats how it would work anyway.

     

     

    https://myURL?after_id=1494

     

     

    The API says

    Pass the last id received, to receive records after it. Null can be passed to start at the beginning.

     

    Any help much appreiciated and I know this is an old thread but here goes.....

     

    Cheers

     

    Paul.

    And does it?

    • FPackermanGTA's avatar
      FPackermanGTA
      Advocate I
      4 years ago

      Yes I just need a way to recursively enter the function with the previous 'last_id' appended to the base url until 'additional_pages' is False and then join it all together but struggling to do it. Any ideas?

      • TerriblyVexed's avatar
        TerriblyVexed
        Frequent Visitor
        4 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.