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

[cccarv82]

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...

[artemus]

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]

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 ?