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

Paull's avatar
Paull
Frequent Visitor
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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello, Thank you for the code.
I tried the Do-while code for iterations but I do not get any results. The issue could be the Merakki API URL doesn't consists of page numbers instead it has a timespan. The codes are as follows - 

(page as number) as table =>
let
    Source = Json.Document(Web.Contents("https://api.meraki.com/api/v1/networks/(Academy ID)/clients?timespan=2678400&perPage=1000" ,[Headers=[#"(Mearkki Header)"="(Organization API Key)",))),
    Data1 = Source{1}[Data],
    RemoveBottom = Table.RemoveLastN(Data1,3)
in
    RemoveBottom

let
    Source = List.Generate( () =>
  [Result = try GetData(1) otherwise null, Page = 1],
  each [Result] <> null,
  each [Result = try GetData([Page]+1) otherwise null, Page = [Page]+1],
  each [Result])
in
    Source

 Thanks in advance