Forum Discussion

cccarv82's avatar
cccarv82
Frequent Visitor
4 years ago

How to get all pages when the API returns the "next page" inside the JSON results ?

Ok, so... What do I want to do?

I want to get from an API a list of results, but the API only gives me 25 results per page. The next page is reachable using a field returned in the query with the value to perform the next page search.

 

In postman I have this:

 

 

 

 

So... I have "epics" (I will ignore this) and I have "stories" (that's what's important to me)...

Inside "stories" I have "next" and "data"... the "data" is the data that I'm looking for to use in my reports and I can get only 25 per search.

The "next" is the "key" to perform the search to the next page.

Ok... now in Power BI/Query...

With the code below I am able to get the first page of the results but I can't figure out what I need to do to keep going to the next pages.

 

 

(search as text) =>

let

baseurl = "https://api.xpto.com",

headers = [Headers=[#"Content-Type"="application/json", #"Token"="textsandnumbers"]],

completeSearch = baseurl & search,

iterations = 10,



FGetPage =

(completeSearch) as record =>



let

initReq = Json.Document(Web.Contents(completeSearch, headers)),

initData = initReq[stories][data],

initNext = initReq[stories][next],

nextSearch = Text.Combine({baseurl}, {initNext}),

res = [Data=initData, Next=nextSearch]

in

res,



GeneratedList =

List.Generate(

()=>[i=0, res = FGetPage(completeSearch)],

each [i]<iterations and [res][Data]<>null,

each [i=[i]+1, res = FGetPage([res][Next])],

each [res][Data]

),



expand = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

expand

 

 

When I invoke this code I get this as result:

 

 

 

 

I'm able to use the "List" in the first row to see the results from my search (only the first page by now).

In the second line, I have the "Error" that I don't know how to fix to get the others pages...

 

 

 

 

Finally... to conclude... I need help to get all pages from a search where the next page is determined by a field presented in the JSON returned as an answer...

Need Help

2 Replies

  • artemus's avatar
    artemus
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    I think the issue is that, you are getting a list from the json, and then those are going into another list for each page. So you have a list in a list. You just need to combine the lists before putting them in a table with List.Combine(lists)

    • cccarv82's avatar
      cccarv82
      Frequent Visitor
      4 years ago

      Can you help a bit more? I tried some things with your tip but I guess my knowledge is too little...

       

      (search as text) =>
let
    baseurl = "https://api.xpto.com",
    headers = [Headers=[#"Content-Type"="application/json", #"My-Token"="sometoken"]],
    completeSearch = baseurl & search,
    iterations = 10,

    FGetPage = 
    (completeSearch) as record =>

    let
        initReq = Json.Document(Web.Contents(completeSearch, headers)),
        //initList = List.Combine({{[stories][next]}, {[stories][data]}}),
        initData = initReq[stories][data],
        initNext = initReq[stories][next],
        nextSearch = Text.Combine({baseurl}, {initNext}),
        
        res = [Data=initData, Next=nextSearch]
    in
        res,

    GeneratedList =
      List.Generate(
        ()=>[i=0, res = FGetPage(completeSearch)],
        each [i]<iterations and [res][Data]<>null,
        each [i=[i]+1, res = FGetPage([res][Next])],
        each [res][Data]
        ),

    expand = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    expand

       

       

       

      What would you change in this code ?