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

    • markholland's avatar
      markholland
      Icon for Helper I rankHelper I
      8 years ago

      Hi,

       

      I'm having real difficulties following the steps in this post with my API. I've watched the video, which differs from the code given at the bottom of this post. Neither approach has worked for me. I'll go through what I've done and hopefully someone can help me:

       

      Following the video I've added the following code:

       

      GetData

       

      (page as number) as table =>
let
    Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries?access_token=********&account_id=********&page=" & Number.ToText(page))),
    Data1 = Source{1}[Data],
    RemoveBottom = Table.RemoveLastN(Data1,3)
in
    RemoveBottom

       

      I've then copied the code to create the list of pages:

       

      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

       

      But when I do this I get nothing, just a column header with List but no list of pages.

       

      When I try the amended version in this post I get nothing either. Here's the code I'm using:

       

      let
 Source = Json.Document(Web.Contents(url, [Headers=[Authorization="********"]])),
 iterations = Source[total_pages],          // get the information within the response
 url = "https://api.harvestapp.com/v2/time_entries?access_token=********&account_id=********", // here goes your URL
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization="********"]])),
    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]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

      I really have no idea where I'm going wrong. Can anyone help? Paull ImkeF

       

       

      Thanks,

      Mark

    • 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