cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dhirus
Regular 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

4 REPLIES 4
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
Regular 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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors