Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dhirus
Frequent Visitor

Can we enable folding for custom query

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?

2 ACCEPTED SOLUTIONS
ppm1
Solution Sage
Solution Sage

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

 

Microsoft Employee

View solution in original post

jennratten
Super User
Super User

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 

 

View solution in original post

5 REPLIES 5

Hi Dhirus,

Please watch this video: https://youtu.be/_8WUVlYGekk

jennratten
Super User
Super User

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
Solution Sage
Solution Sage

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

 

Microsoft Employee
Dhirus
Frequent Visitor

@ppm1 , 

Thank you !

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

You're welcome!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors