cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kriswijnants Frequent Visitor
Frequent Visitor

JSON Web limited to 5000 records

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.

2018-07-11 12_14_04-Microsoft Edge.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Regards,

 

Kris

4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: JSON Web limited to 5000 records

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
kriswijnants Frequent Visitor
Frequent Visitor

Re: JSON Web limited to 5000 records

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

Moderator v-yuezhe-msft
Moderator

Re: JSON Web limited to 5000 records

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kriswijnants Frequent Visitor
Frequent Visitor

Re: JSON Web limited to 5000 records

Hi Lydia,

 

I'll do so.  Thanks for the advise.

 

Regards,

 

Kris