Query Folding Icons in Power Query

[jakaihammuda]

Hello,

Im trying to understand more about Query Folding, Im aware of when it can be used in terms of storage modes etc and how some steps allow for query folding and some dont.

However, when researching, im seeing microsofts documents contain little incons next to their applied steps? like so

Im struggling to understand how to apply these icons to my Power Query Editor. Im seeing lots on things regaridng Query Plan or Power Query online, but not too sure what those both are as i cant find those anywhere.

Thanks

[Syk · Accepted solution]

Unfortuntely, the indicators are only for Power Query Online meaning you won't see it in Power BI Desktop's Power Query Editor.

[Syk · Accepted solution]

Unfortuntely, the indicators are only for Power Query Online meaning you won't see it in Power BI Desktop's Power Query Editor.

[jakaihammuda]

What is Power Query Online? 

I dont fully understand what that is or how to even find it at all?

Thanks! 🙂

[Syk]

Are you familiar with the Power BI Service and publishing reports?

[jakaihammuda]

Yes

[Syk]

When you create something like a data flow in the Power BI Service, it will allow you to transform the data just like in Power BI Desktop. It's using Power Query Online in that case. Essentially the exact same thing, just online. 🙂

[jakaihammuda]

Thank you for this! 🙂 One more thing you may know thr answer to, how can Query Folding occur when the storage mode of data is import?

If the data is being imported thus being locally stored for use, how can query folding be beneficial in terms of perfomance because it isnt relaying back direvtly to the DB?

Thanks! 🙂

[Idrissshatila · Accepted solution]

Hello @jakaihammuda ,

it's called Data flows.

check it out here https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-create

[jakaihammuda]

Thank you for this! 🙂 One more thing you may know the answer to, how can Query Folding occur when the storage mode of data is import?

If the data is being imported thus being locally stored for use, how can query folding be beneficial in terms of perfomance because it isnt relaying back directly to the DB?

Thanks! 🙂

[Idrissshatila]

@jakaihammuda ,

it is relaying back to the db, because when you refresh the power bi report, power bi grab all the data again and go all the applied steps ib power query.

[jakaihammuda]

Thats what i couldnt understand, it refreshes to the db first and then goes through the applied steps right?

So essentially it enables it to just go through the applied steps quicker as some may be native query?

[Idrissshatila]

@jakaihammuda , true, when there's query folding what it does is that it pushes the transformations from the steps into the db, resulting less load on power query.

[jakaihammuda]

So are we saying that even in import mode, if a report has query folding steps, during the report refreh those steps with query folding are being sent to the db and returned all within the refresh, then it finishes?

then those applied steps that are non query folding are applied once the refreshed data has been pulled?

[Idrissshatila]

@jakaihammuda ,

all transformations are done when the data is refreshed, the query folding transformations are pushed to the data source while the non query folding steps are done in power query.

[Syk]

To piggyback on this... Every applied step that does not break the query folded is rolled up into a single query to the database (hence 'query folding' it's just folding those steps into the query!)

However, query folding does not happen if you explicitly state a query in the connection. There are also steps that will break the query fold and you may notice longer processing times.
There's not a great way to see if query folding is occuring in Power Query (desktop) but you can right click an applied step and if you can click 'View Native Query', query folding is working at least until that step!

[jakaihammuda]

Hey,

So during import mode, all data is imported into the report. Then are you saying that if the next 6 sequence of applied steps are Native Query enabled, then they will be rolled into one single query, then the remainder of applied steps will occur one by one?

Is that how it works in speeding up the process?

Or does it mean that when the report is refreshed, that all the Native Query steps are being sent in one query back to the DB during that refresh to gather what is required based on those Native Query steps?

Please bear in mind im only refrencing import mode. 

Thank you 🙂

[Syk]

For import mode, when the report is refreshed PBI will query the DB with the native query that you see and if the query fold breaks after that, will apply the rest of the steps it needs after it brings the data in.
You can test this yourself, if you check the steps after each transformation.
This step is the navigation to the table.

If I want to take out some columns, the next step will show the query that it will run on refresh. In Power BI its a remove columns step but instead of getting everything and then deleting all the additional columns, it just simplifies the sql query.

Similarly, if I want only 2020 data and filter in Power BI - It folds all those steps into 1 query that will execute when refreshed. Which means less load that Power Query has to do!

[jakaihammuda]

Also, does the order of applied steps matter in terms of rolling the native queries into one?

Say if we had the first 5 steps as native queries that are folded
the next 3 are not supported as native query
then we have another 2 queries that are native.

Will PBI send back the 7 native queries rolled into one? Or will it be seperated out as the 5 rolled into one query and sent then the other 2 queries rolled into one and sent back?

[Syk]

It wouldn't fold anything after an action that "breaks" it. Because at that point it needs to be processed in Power Query before it goes to the next step.

[jakaihammuda]

Hi @Syk ,

So essentially, as soon as you hit an applied step that is not supported as Native Query, nothing after that will be identified as a native wuery - even if the step is supported by it such as removing a column?

[Syk]

I think you've got it!