Query Folding / Native Query methodology

[jakaihammuda]

Hi there,

Im just looking for abit of clarification before i start applying what i believe to have understood into my reports, as the research im doing makese sense but does really provide a clear reposinse to what im about to ask:

*import storage mode report for example*

Does the order of applied steps matter when you are aiming to achieve query folding through native query applied steps? For example what i mean by this is, say i have some applied steps, the first 5 support native query and thus become a query fold (things like removing columns, filtering etc). But then the nery next step does not support native query. This then breaks the query fold chain i understand ,but if after this step i have another 5 applied steps that in theory do support native query (such as filtering etc) will they not become a query fold due to the chain being broken by the 1 applied step in between them that is not supported by it? its not a case of those 5 steps after the break will also be query folded and send back to the db. I am thinking it doesnt, because as soon as it gets back to pbi side to then go through the applied steps with the refreshed data, its not going to then go back to the DB again.

Also, can anyone help on if there is a definitve or quick list as to what steps do not support native query? 

Thank you

[AfterRain]

Hiya! I'm currently learning Power BI and came across such question as well! So I did a "so called" experiment with my own test database on MySQL. And the result are no, Query Folding doesn't continue after you did a "non" query fold. Here are the screenshot

As you can see after Promoted Headers and Changed Type steps, the "Expanded Column8" is a "non" query fold. And on regards of "a definitve or quick list as to what steps do not support native query", you can find it in https://learn.microsoft.com/en-us/training/modules/get-data/8-performance-issues where it is said

Native queries aren't possible for the following transformations:

• Adding an index column
• Merging and appending columns of different tables with two different sources
• Changing the data type of a column

A good guideline to remember is that if you can translate a transformation into a Select SQL statement, which includes operators and clauses such as GROUP BY, SORT BY, WHERE, UNION ALL, and JOIN, you can use query folding.

Hope this helps! (For whoever, because the comment is a month old and you probably already figured it out 😆)

[DataInsights]

@jakaihammuda,

This screenshot from the first article below indicates that query folding can resume after encountering steps that cannot be folded:

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

https://learn.microsoft.com/en-us/power-query/power-query-folding#transformations-that-can-achieve-f... 

[jakaihammuda]

Actually i dont quite think thats 100% true, the only reason that the native query "remove columns 1" has become native/part of the query fold is due to the fact that the step of removing the column, was in fact removing a column of which did not support native query (capitalisation) so in removing that column entirely (not needing to capitalise) the query fold can resume.

[jakaihammuda]

Would that then group it the first query fold "navigation", "removed columns"? to then include all 3?