Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
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
@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
Hi Lydia,
I'll do so. Thanks for the advise.
Regards,
Kris
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |