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 va...
Need Help
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 ?