Query Folding in PowerBI

[Kumail]

Query folding is very effective with large databases when you are looking to improve refresh time or just to load and transform large amount of data quickly.

It is the ability of the power query mashup engine to generate a single query statement to retrieve and transform source data. This feature in the power query editor is designed for query optimization. For example, a task generated in the Power Query UI to identify distinct values from 10 million rows of data and pushing those tasks back to the database for execution.

The query folding may occur for an entire power query or for a subset of its steps. If it cannot be achieved, the power query mashup engine had to compensate by processing data transformation itself. It is recommended to achieve efficiency in the model design by ensuring query folding occurs whenever possible.

Automatic Query Folding in Power Query

Power query mashup engine strives to achieve query folding in order to be more efficient. Query folding will automatically occur in certain conditions

• The source database accepts folding request. Most of the relational data base accepts folding requests. It also includes OData Source, SASS and Google Analytics.

How to check if Query Folding is Active

In the power query editor, right click on the “applied steps panel” and check “view native query” button is active. If the button is active, that means the query is folding up to this step and if in-active query folding may not be happening. 

View Native Query

Native Query

How to enable query folding when you are using Native Query in Power BI desktop

With native query, when you do transformation steps, query folding is not enabled.

QueryFolding disabled

Now in order to activate query folding, go to view tab and check mark formula bar to activate it if it is not already active.

formula bar

Click the fx button in the formula bar. This will add custom step in the applied step and write your native query in value.NativeQuery() function and set Enablefolding property to true.

Custom Step Query Folding

Now when you filter rows and check native query, it is actually doing query folding which is amazing!!

Query Folding Active With Native Query

Native Query

Importance of Query Folding

• Data refresh will take place efficiently for import mode tables in terms of resource utilization and refresh duration.
• Direct query and dual storage mode will work efficiently if query folding is table place.
• Incremental refresh will be efficient in terms of resource utilization and refresh duration. If query folding couldnot be achieved, the objective of incremental refresh is defeated.