Forum Discussion
Detect Query Folding in model
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.
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-foldingIt 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.
5 Replies
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.
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-foldingIt 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.
