1

I am currently using the below 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:

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?

Imran-Ami Khan

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Ami Khan
  • 21
  • 1
  • 5

1 Answers1

0

I am not a Power BI expert, but my tool Dataverse REST Builder has the Power Query (M) syntax for the Retrieve Multiple message.

The generated syntax is able to retrieve more than 5000 records (the maximum for each call) because it checks for the @odata.nextLink property.

Guido Preite
  • 14,905
  • 4
  • 36
  • 65
  • Thank you Guido and for all you support you provide for the community (especially in the dynamics forums!). However I was hoping for an out of the box solution because it seems this is possible but I just lack the knowledge to implement this. – Ami Khan Aug 21 '23 at 13:58
  • you already wrote a Power Query that is missing the logic to fetch the next 5000 records (and the 5000 records after and go on). The Dataverse API returns max 5000 records so there isn't a way to return more records in a single call (you can eventually check the TDS endpoint https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query but I didn't use inside Power BI) – Guido Preite Aug 21 '23 at 17:56
  • Hi Guido - the power query written above is not my code. I included the reference to where I obtained it from in my first post. I understand how to implement the first part of the code by applying the code to a Power Query function. However, I do not know what to do with the second piece of code. I assume I need to also apply that code to a separate function, but I do not know what to do with it/what modifications I need to make. For example, I do not know what to enter for the "path/to/records" part in the code. – Ami Khan Aug 21 '23 at 21:12
  • you can follow this youtube tutorial on how to add the Power Query functions https://www.youtube.com/watch?v=82bd3mz7W5E it uses another tool to generate the M code, but you can use my tool for the M code as well – Guido Preite Aug 22 '23 at 09:40
  • thank you Guido I will look into this and your tool. – Ami Khan Aug 25 '23 at 02:55