Can we enable folding for custom query

[Dhirus]

Hi,

Can we enable query folding for Custom SQL script.

I used a simple Query in advanced option under the database connectivity -  "Select * from Table1" 

and than add basic transformation in power query like filtering data - but the step is not query folded(native query greyed out)

-------------------------

So I added one step just prior to tranformation(Filter data) by using Mcode

=Value.NativeQuery(Source,"Select * from Table1",null,[EnableFolding=true])

but this M code throws error - 

---------------------------------

but when I removed the query from the advanced option under the database connectivity -  "Select * from Table1"

and simply pull the table by passing server and database name, and applied the same logic with Mcode it's working.

So here my question is if we pass the custom query - How we can enable the folding for custom query?

[ppm1 · Accepted solution]

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

[jennratten · Accepted solution]

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 

[ppm1 · Accepted solution]

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

[Dhirus]

@ppm1 , 

Thank you !

I thought even if I wrote custom query in Advanced option than it should also be query folded.

[jennratten]

You're welcome!