Loop through API response with a next page url

[Anonymous]

I'm working with an endpoint where I am sending a POST request and some parameters and get a JSON response with the data I have requested. However, this only returns the first page of the results along with the "next_page_url" I want to loop trough all the pages and return the data as a single table.

This is what I have come up till now.
However, this returns only the first page with some Error.

Click on Error:

Click on Result:

The "results" is the response from the first page of the request. 

The "next_page_url" gives the URL for the second page of the results.

The Query is as follows:

= let
    Source = List.Generate( () =>
    
        [ 
            URL = "https://website.com/endpoint?script=1111" , 
            headers = [#"Content-Type" = "application/json", #"Method" = "POST", #"User-Agent" = "Mozilla/5.0"],
            postData = Json.FromValue([ #"q1" = "a1"]),
            Result = Json.Document(Web.Contents(
                URL, 
                [
                    Headers = headers,
                    Content = postData
                ]
            ))            
        ],
    
        each [Result][next_page_url] <> null,
    
        each [URL = [Result][next_page_url],
        headers = [#"Content-Type" = "application/json", #"Method" = "POST", #"User-Agent" = "Mozilla/5.0"],
        postData = Json.FromValue([ #"q1" = "a1"]),
        Result = Json.Document(Web.Contents(
            URL, 
            [
                Headers = headers,
                Content = postData
            ]
        )) 
        ]
    
    )
in
    Source

What am I doing wrong here?

I have gone through many similar questions but could not find anything that worked for me. 

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Your current query can only fetch the URL once and not recursively. I think you could write a function that requests your API, then takes the next page url at each result you traverse, then puts that url into the custom function above to access it and then proceeds to take the next page url.
For example:

fx(next page url) = > 
URL = next page url , 
            headers = [#"Content-Type" = "application/json", #"Method" = "POST", #"User-Agent" = "Mozilla/5.0"],
            postData = Json.FromValue([ #"q1" = "a1"]),
            Result = Json.Document(Web.Contents(
                URL, 
                [
                    Headers = headers,
                    Content = postData
                ]
            ))

Then refer to this function in your query.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

Thank you for the response. That was helpful. 
I have created a function that pulls the records and then referencing that function in List.Generate.

The List.Generate throws the following error:

This is the query:

= let
Source = List.Generate(
[Result = Query1("url"),
],
each [Result][next_page_url] <> null,
each [Query1([Result][next_page_url])],
each [Result][results]
)
in
Source

How do I resolve this issue?