Return all rows from JSON Query

Hi everyone,

I am currently using the below to API call to a Dataverse table. Unfortunately, this results in only the first 500 rows being returned.

let
    Source = Web.Contents(https://xxx-prod-xxx.api.crm11.dynamics.com/api/data/v9.2/audits)
                in
    Source

I have found a potential solution below:

https://stackoverflow.com/questions/65631024/json-query-in-power-bi-only-returning-first-1000-rows-how-to-return-all-rows 

The solution first involves making a function to access the API:

let API = (relPath as text, optional queries as nullable record) =>
    let
        Source = Json.Document(Web.Contents(https://xxxx.api.crm4.dynamics.com/, 
            [ Query = queries, 
              RelativePath=relPath]))
    in
        Source
in 
    API

And then running another query using List.Generate to perform multiple calls to the API as required:

let
    Source = API("path/to/records", [rows_per_page="1000"]),
    pages = Source[total_pages],
    records = 
        if pages = 1 then Source[records] 
        else List.Combine(List.Generate(
            () => [page = 1, records = Source[records]], 
            each [page] <= pages, 
            (x) => [page = x[page] + 1, records = API("path/to/records", [page = Text.From(x[page] + 1), rows_per_page = "1000"])[records]], 
            each [records]))
in
    records

However, I am stuck at this part because I do not know how to modify the above for my scenario (my knowledge of this area is limited).

Could anyone advise or alternatively suggest a method for returning all rows?

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan || Power Platform Super User: Community Profile