Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Loop through API response with a next page url

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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?