Can we enable folding for custom query

Are you saying you are putting your "SELECT * ..." in both places? (the Advanced Options area of the connector popup window and within Value.NativeQuery) You should only put it in Value.NativeQuery, to get the desired folding behavior. You can add a step in your query with your SQL statement as text and use that within Value.NativeQuery, to simplify your code. The advanced options box should be empty.

Pat

Hello - query folding happens automatically when the data source and transformations support folding.  Queries can fully folded, partially folded or not folded at all.  There are many sources and transformations that do not support folding.  Value.NativeQuery simply represents what Power Query generates and submits to the source to fulfull the query.  The use of custom functions does not necessarily break or prevent folding.  If the source and all of the steps in the custom function support folding, the result will be folded.  One way to get as much of your query to fold as possible is to use simple transformation steps and make sure you apply foldable steps before non-foldable steps, if possible.  Here are some good resources related to query folding.

https://learn.microsoft.com/en-us/power-query/power-query-folding 

https://learn.microsoft.com/en-us/power-query/best-practices 

https://blog.crossjoin.co.uk/2018/11/21/query-folding-web-service-power-bi/ 

http://petcu40.blogspot.com/2019/06/m-folding-in-enhanced-engine-of-power.html