Error on loading: Number of calculated fields in query exceeded maximum limit of 10

[Anonymous]

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

[Anonymous]

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! 

[v-lili6-msft]

hi, @Anonymous

Here are two similar post for you refer to:

https://community.powerbi.com/t5/Integrations-with-Files-and/New-error-Dynamics-365-Number-of-calculated-fields-in-query/td-p/476348

https://community.powerbi.com/t5/Integrations-with-Files-and/Dynamics-365-OData-error-with-Power-BI-desktop/td-p/344920

Best Regards,

Lin

[Anonymous]

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.