JSON Web limited to 5000 records

[kriswijnants]

Hi,

We have the following issue at a customer.

When we connect to Dynamics 365 using a Web Connection we get limited to 5000 records we can get in a download.  After that the connection stops working.

let
    Source = Json.Document(Web.Contents("https://xxxxxxxxxxxxxxxx.api.crm4.dynamics.com/api/data/v8.2/opportunities?$select=opportunityid,createdon,sec_initialclosedate")),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"@odata.etag", "opportunityid", "createdon", "sec_initialclosedate"}, {"@odata.etag", "opportunityid", "createdon", "sec_initialclosedate"})
in
    #"Expanded Column1"

When I perform the same download (same query), using OData I can get all 12000 records out of the CRM table

let
    Source = OData.Feed("https://xxxxxxxxxxxxxxxxx.api.crm4.dynamics.com/api/data/v8.2/opportunities?$select=opportunityid,createdon,sec_initialclosedate", null, [Implementation="2.0"])
in
    Source

Is this a known bug in PowerBI, or am I doing something wrong in my Web query?  I prefer working with Web as the performance is better than using OData.

Regards,

Kris

[Anonymous]

@kriswijnants,

It is not a bug of Power BI Desktop, but a limitation from Dynamics 365 side. You can check the following bligs about this issue.

https://crmchartguy.com/2017/09/30/use-fetchxml-in-power-bi-with-dynamics-365-customer-engagement/
https://www.inogic.com/blog/2015/07/querying-more-than-5000-records-in-dynamics-crm/

Regards,
Lydia

[kriswijnants]

Hi v-yuezhe-msft,

Thanks for your reply.  Do you have an example or blogs post you can share on how to implement the solution you proposed in PowerBI (https://www.inogic.com/blog/2015/07/querying-more-than-5000-records-in-dynamics-crm/)?

I hope the PowerBI and Dynamics teams can find a solution on this, as it is a showstopper for a PowerBI project we have ongoing now.

Regards,

Kris

[Anonymous]

@kriswijnants,

The above method is implemented in Dynamics 365, I would recommend you post the question in Dynamics forum to get better support.

Regards,
Lydia

[kriswijnants]

Hi Lydia,

I'll do so.  Thanks for the advise.

Regards,

Kris