{"id":4251,"date":"2023-02-21T14:13:47","date_gmt":"2023-02-21T14:13:47","guid":{"rendered":"https:\/\/www.biconnector.com\/blog\/?p=4251"},"modified":"2023-12-11T14:01:18","modified_gmt":"2023-12-11T14:01:18","slug":"query-folding-power-bi-everything-you-need-to-know","status":"publish","type":"post","link":"https:\/\/www.biconnector.com\/blog\/query-folding-power-bi-everything-you-need-to-know\/","title":{"rendered":"Query Folding in Power BI: Everything you need to know"},"content":{"rendered":"\n

Query folding is a powerful feature in Power BI<\/a> that can significantly enhance your data analysis capabilities. To maximize its benefits, it’s essential to have a clear understanding of what it is and how it works. This article will provide a comprehensive guide to query folding in Power BI, covering everything you need to know.<\/p>\n\n\n\n

What is Query Folding?<\/h2>\n\n\n\n

Query Folding is the capability of a Power Query to create a single statement that can transform and execute tasks (such as filtering a thousand rows of data) on the data source side. Through Query Folding, Power Query can deal with a vast volume of data efficiently.<\/p>\n\n\n\n

Understanding Query Folding<\/h2>\n\n\n\n

You can think of Query folding as writing down a set of instructions in SQL code and passing it over to the database for enforcement. In essence, the data source does the heavy lifting and, in turn, reduces the amount of data that has to be processed and transferred within Power BI. <\/p>\n\n\n\n

Power Query will automatically execute query folding under the following conditions:<\/p>\n\n\n\n

1.\tThe data source is a database that can accept a query request. <\/p>\n\n\n\n

2.\tAll the steps in the configuration must allow query folding. If you perform even a single action that cannot be folded, query folding will stop for the entire query.<\/p>\n\n\n\n

3. The Query must not start with your own SQL code. If you start your query with your own SQL code, no steps in the query will be folded.<\/p>\n\n\n\n

How to Find If Your Power BI Data Source Supports Query Folding?<\/h2>\n\n\n\n

To find if a data source supports the feature in Power BI, follow the steps given below:<\/p>\n\n\n\n

1. Right-click your sheet on the Report View in the field pane and select Edit Query<\/strong>. You will be redirected to the Power Query Editor<\/strong>.<\/p>\n\n\n

\n
\"Fields<\/figure><\/div>\n\n\n

2.Here, on the right, you will be given several transformations. Right-click on any one of them and select View Native Query<\/strong>.<\/p>\n\n\n

\n
\"View<\/figure><\/div>\n\n\n

3. The dialogue box below shows that Query Folding was performed for this column.<\/p>\n\n\n

\n
\"Native<\/figure><\/div>\n\n\n

If the View Native Query<\/strong> option was grayed out, it would mean that query folding could not be applied to this step.<\/p>\n\n\n\n

Query Folding Indicators<\/h2>\n\n\n\n

Query folding indicators provide insights into the behavior of a query by determining whether it will fold or not based on the underlying query plan. Below is an image quickly listing the indicator icon along with its meaning.<\/p>\n\n\n\n

\"Query<\/figure>\n\n\n\n

Now let\u2019s see them one by one.<\/p>\n\n\n\n

Folding<\/h3>\n\n\n\n

This indicator indicates that the data source will execute the query up to this step.<\/p>\n\n\n\n

Not Folding<\/h3>\n\n\n\n

This means that the data source will not process certain parts of the query. To maximize performance, it’s important to rearrange the query so that the data source executes as many transformations as possible.<\/p>\n\n\n\n

Might fold<\/h3>\n\n\n\n

This indicator signifies that the folding status cannot be determined beforehand and will be determined during runtime. It only appears when using OData connections or ODBC.<\/p>\n\n\n\n

Opaque<\/h3>\n\n\n\n

This indicator signifies that the query plan is indeterminate and may indicate that the query plan tool and indicators are not supported by the connector.<\/p>\n\n\n\n

Unknown<\/h3>\n\n\n\n

This indicates that the underlying query plan is absent. This could occur due to an error or when you attempt to run the query plan evaluation on something other than a query table. <\/p>\n\n\n\n

The image below shows each indicator with its respective icon:<\/p>\n\n\n\n

Steps to Setup Query Folding
<\/h2>\n\n\n\n

To perform query folding in Power BI, follow the steps below:<\/p>\n\n\n\n

Connect Power BI to a data source:<\/strong> <\/p>\n\n\n\n

1.To perform query folding in Power BI, you must connect to a Power BI data source that supports Query Folding. In our demonstration, we will use Microsoft Access Database. To follow along with this article, download the dataset by clicking here<\/a>. To connect to a data source, click on Get Data<\/strong> and choose the relevant data source. After that, guide the interface to the location of the source.<\/p>\n\n\n\n

\"Connecting<\/figure>\n\n\n\n

2. Load Data:<\/strong> You can now use the Query Editor to perform any edits you want. Click Transform Data,<\/strong> and you will be redirected to the Query Editor.<\/p>\n\n\n\n

\"Transform<\/figure>\n\n\n\n

3. Perform Transformations:<\/strong> Once the Query Editor opens up, you can perform transformations and push them into the data source. It could be as simple as sorting the rows of a particular column in ascending order, as shown below:<\/p>\n\n\n\n

\"Simple<\/figure>\n\n\n\n

You can also perform a more complex transformation, such as adding a conditional column. Go to Add column<\/strong> and click on Conditional column<\/strong>. After that, add a condition as shown below:<\/p>\n\n\n\n

\"Conditional<\/figure>\n\n\n\n

4. Apply the Transformations: <\/strong>On the Home <\/strong>tab, click Close & Apply<\/strong> to load the data onto Power BI. You can also repeat the process for any column on which you want to perform query folding.<\/p>\n\n\n\n

Transformations that Support Query Folding<\/h2>\n\n\n\n

Some of the most common transformations that support query folding are: <\/p>\n\n\n\n