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