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...
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
8 years ago

You're missing the let-keyword at the beginning:

 

let Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages",[Headers=[Authorizati... xxxxxxxxx", #"Content_Type"="application/json"]])),
iterations = Source[total_pages], // get the information within the response
url = "https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", // here goes your URL

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", [Headers=[Authorization="xxxx", #"Content_Type"="application/json"]])),
Page = 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 = [Page=page, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Page]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [Page][Data]),
#"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"
    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Hi svishwanathan,

      sorry, but I cannot help you any further here.

       

      This is an advanced topic and there is no standard solution for it currently. I you don't have a basic understanding of the M function, it is very difficult for me to tell you what you have to do. Both functions that might have to be adjusted depend on the actual data that comes from your source and this would mean that I would have to guide you through it step-by-step and you would have to share your screen after each step. This is consulting service in my eyes and I don't do it in the forums anymore.

       

      So you might consider opening a new thread on this to raise the chance that someone else picks this up.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        ImkeF 

        Thanks to this thread I was able to replicate a lot and I am very close to finally solve my particular challenge as well :) 

        The only problem I have: I don't know the number of total records, hence my iteration has to run until the return [next_page] from the API runs into a Null.

        My code works, but it seems to be an infinite loop and what I get is a List of Lists (a list of pages), that I need to expand to a full table.

         

        let
 Source = Json.Document(Web.Contents("https://myURL")),
   tickets = Source[tickets],
   url = "https://myURL", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page 
    res = [Data=data, Next=next]
   in
    res,

 GeneratedList =
  List.Generate(
   ()=>[res = FnGetOnePage(url)],
   each Source[next_page]<> null,
   each [res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList

        Can you help me? I think I am very close... 

        Thanks in advance!