500 row limit when getting data from Dataverse REST API

[imranamikhan]

Hi everyone,

I am accessing the API connection into a Dataverse table (authorisation done via an organisational account) by wrapping the Web API endpoint URL in a simple Web.Contents function. E.g.:

let

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

in

    Source

Unfortunately, the result is limited to 500 rows. I have tried the pagination and looping suggestions online, but none seem to provide a workable solution. The solutions fail at points I do not understand because I am not knowledgeable with JSON or running API calls in general.

For example:

let
    Source = Json.Document(Web.Contents(https://xxx-prod-xxx.api.crm11.dynamics.com/api/data/v9.2/audits)),
    BaseUrl = https://xxx-prod-xxx.api.crm11.dynamics.com,
    EntitiesPerPage = 50,

    GetJson = (Url) =>
        let RawData = Web.Contents(Url),
            Json = Json.Document(RawData)
        in  Json,
    
    GetTotalEntities = () =>
        let Json = GetJson(BaseUrl),
            Total = Json[total]
        in  Total,

    GetPage = (Index) =>
        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),
            Url   = BaseUrl & "&" & Skip,
            Json  = GetJson(Url),
            Value = Json[data]
        in  Value,

    GetUrl = (Index) =>
        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),
            Url   = BaseUrl & "&" & Skip
        in  Url,
  
    EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
   
 URLs  = List.Transform(PageIndices, each GetUrl(_)),
Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    #"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

I am not equipped to know if the error is because the above M-Code is not workable for Dataverse or if I made a mistake somewhere.

Could anyone advise?

My base URL: https://xxx-prod-uksa.api.crm11.dynamics.com/

My full URL: https://xxx-prod-uksa.api.crm11.dynamics.com/api/data/v9.2/audits

References:

https://stackoverflow.com/questions/65631024/json-query-in-power-bi-only-returning-first-1000-rows-h...

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

https://community.powerbi.com/t5/Service/Json-file-will-load-only-500-rows/m-p/2252958/highlight/tru...

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

[Testy]

any news for this question?