Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
I am having an issue when trying to connect to D365 to load data. The query window returned an error message as below. Can anyone please help with the issue? Thank you.
Error message:
DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (Number of calculated fields in query exceeded maximum limit of 10.)
Details:
DataSourceKind=OData
DataSourcePath=https://mycompany.api.crm.dynamics.com/api/data/v9.1/opportunities
Url=https://mycompany.api.crm.dynamics.com/api/data/v9.1/opportunities
Hey all,
this issues of the maximum number of calc columns has been plaguing me, but I think I've got a solution that work for me.
When you use the connector (I'm using the old 'Common Data Service (legacy)' it will through the error. However, If you ignore the error, and then jump into the power query editor, and use the 'advanced editor' you can then jump into the query and do something like this :
let
Source = Cds.Entities("https://xxxxx.crm6.dynamics.com/", [ReorderColumns=null, UseFormattedValue=true]),
entities = Source{[Group="entities"]}[Data],
cra1f_initiatives = entities{[EntitySetName="cra1f_initiatives"]}[Data],
// here i manually add the 'Table.SelectColumns' function to limit the number of cols returned
cra1f_initiatives_select_cols_only = Table.SelectColumns(cra1f_initiatives,{"cr2ee_cluster","cra1f_name"})
in
cra1f_initiatives_select_cols_only
by applying the SelectColumns after the connection, somehow the query knows it will only need to pull 2 (or 3 or 7) columns, not the whole lot, and so you no longer get the original "(400) Bad Request." issue.
Hope this helps!
hi, @Anonymous
Here are two similar post for you refer to:
Best Regards,
Lin
Hi @v-lili6-msft,
Thank you for your reply. however, post #1 did not really provide a solution. and post #2 does not relate that much to the issue here.
This issue never happened before until we upgraded D365 to v9. I used to simply pull all fields in an entity from CRM without any issue. Not sure why now PBI throws an error. Thank you.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |