Detect Query Folding in model

[groongra]

Hi, 

I am working on a project that utilzes the Best Practice Analyzer standard and custom rules to guide Power Bi development. I am trying to build a rule that detcts Tables which expression breaks query folding. Ive analyzed the PowerBi Project model bim file but I havent found any attribute that signals this. How is it possible to know where Query Folding fails (M code cant be translated into Native Query Language)? 

Thanks in advance, best regards.

[v-sdhruv]

Hi @groongra ,
I hope the above solutions has addressed your query.
If you found the solution helpful, please mark it as Accept as Solution or give Kudos so that other users can benefit from it.
Regards,
Shruti

[v-sdhruv]

Hi @groongra ,
I hope the above solutions has addressed your query.
If you found the solution helpful, please mark it as Accept as Solution or give Kudos so that other users can benefit from it.
Regards,
Shruti

[v-sdhruv]

Hi @groongra ,
I hope the above solutions has addressed your query.
Let us know in case of any issues.
Regards,
Shruti

[v-veshwara-msft · Accepted solution]

Hi @groongra,

Thanks for using Microsoft Fabric Community.

Like @lbendlin  stated, it depends on data source. 
Query folding is supported by some data sources apart from Microsoft SQL server, Odata Feed,etc.
Check data sources here 
https://learn.microsoft.com/en-us/power-query/native-query-folding

It cannot be detected on bim file but some transformations may prevent query folding, please review the list below and check if there is any following transformation step in your query.

1.Merging/ appending queries based on different sources

2.Adding custom columns with complex logic.

3.Adding index columns.

Although this list is not exhaustive you can perform these checks to test where the query fails.

Additionally, you can go to https://learn.microsoft.com/en-us/power-query/query-folding-basics#sources-that-support-folding for more clarity.

Hope this helps.

[lbendlin]

It depends.  It depends on the data source and the  virtuosity of the Power Query driver for that data source.

Earlier things like Text.ToLower() would trip up query folding but are now supported by SQL Server connectors (for example).

Another example - the SFDC Object connector makes no claims for query folding yet sometimes produces custom SOQL.  The SFDC Report connector is incapable of doing the same.

In short - there is no one single reliable attribute for this as far as I am aware.