Power Query — query folding

When you want to speed up your Power Query transformations and queries. You can use a Query Folding mechanism. You need to fulfill two conditions. Your data must originate from a supported data source, e.g. most of relational databases. And your data transformations and queries have to be supported by the Query Folding. You can assume that if you can attain something by using SQL then this operation will be supported by the QF. You can check if the QF is applied in a Power Query editor.

For example, we want to download, filter and make a simple data transformation from a SQL SERVER source.

M language code:

As you see, there are filtering and two transformations. And they support the Query Folding.

If a View Native Query position is active, then a transformation supports the QF.

As you see, here is a native query code, the query is folded and it is processed directly in a SQL SERVER database.

At this stage the query is folded. Now it is the time to break something. In order to do this, I added next step: Capitalize each word. As you know, a SQL engine doesn’t support such an operation. As a result of this operation the query isn’t folded anymore. The View Native Query position is inactive.

Worth mentioning is a fact, that every step before the last one is still folded, only last and every next step is performed outside the source server.

Why you should use it? Because of performance. Entire work is done directly in a database. When it comes to small datasets, there is no visible difference. But, if your deal with a large amount of data the difference is noticeable.