How query folding will work in Power BI?

[srinudatanalyst]

Hi everyone, Can someone explain query folding in Power BI? How does it work ?

Thanks in advance

[danextian · Accepted solution]

Hi @srinudatanalyst 

The goal of query folding is to offload or push as much of the evaluation of a query to a data source that can compute the transformations of your query.

The query folding mechanism accomplishes this goal by translating your M script to a language that can be interpreted and executed by your data source. It then pushes the evaluation to your data source and sends the result of that evaluation to Power Query.

This operation often provides a faster query execution than extracting all the required data from your data source and running all transforms required in the Power Query engine.

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

[dulan_kavinda · Accepted solution]

Hi @srinudatanalyst 

Query folding is a process in Power BI where the steps of data transformation applied in the Power Query Editor are translated into native query language, which is then executed directly in the data source. This means that instead of pulling all the data into Power BI and then applying transformations, Power BI pushes the transformations back to the data source, allowing the source system to handle the heavy lifting. This can significantly improve performance and efficiency, especially when dealing with large datasets

How Query Folding Works

When you apply transformations in Power Query, such as filtering rows, adding columns, or merging tables, Power BI tries to translate these steps into a single query that the data source can understand and execute. This process is known as query folding. If all the steps can be folded, the data source returns only the transformed data to Power BI, reducing the amount of data that needs to be transferred and processed.

Example

Imagine you have a SQL Server database with a table containing sales data. You want to filter the data to include only sales from the last year and then group the data by product category. In Power Query, you would apply a filter step and a group by step. If query folding is possible, Power BI will generate a SQL query that includes both the filter and group by operations, and the SQL Server will execute this query and return only the aggregated data for the last year.

Benefits of Query Folding

1. Performance: By pushing transformations to the data source, you reduce the amount of data that needs to be transferred and processed in Power BI, leading to faster query execution and data refresh times.
2. Efficiency: Leveraging the processing power of the data source can handle complex transformations more efficiently than Power BI alone.
3. Scalability: Query folding allows you to work with larger datasets without overwhelming Power BI's resources.

Checking Query Folding

You can check if query folding is occurring by right-clicking on a step in the Power Query Editor and selecting "View Native Query." If the option is available, it means that the step is being folded into the native query. If it's grayed out, it means that query folding is not happening for that step.

Limitations

Not all data sources and transformations support query folding. For example, some custom transformations or data sources without native query capabilities may prevent query folding. In such cases, Power BI will handle the transformations locally, which may impact performance.

[danextian · Accepted solution]

Hi @srinudatanalyst 

The goal of query folding is to offload or push as much of the evaluation of a query to a data source that can compute the transformations of your query.

The query folding mechanism accomplishes this goal by translating your M script to a language that can be interpreted and executed by your data source. It then pushes the evaluation to your data source and sends the result of that evaluation to Power Query.

This operation often provides a faster query execution than extracting all the required data from your data source and running all transforms required in the Power Query engine.

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