Query folding is a powerful feature in Power BI 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.

What is Query Folding?

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.

Understanding Query Folding

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. 

Power Query will automatically execute query folding under the following conditions:

1. The data source is a database that can accept a query request. 

2. All 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.

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.

How to Find If Your Power BI Data Source Supports Query Folding?

To find if a data source supports the feature in Power BI, follow the steps given below:

1. Right-click your sheet on the Report View in the field pane and select Edit Query. You will be redirected to the Power Query Editor.

2.Here, on the right, you will be given several transformations. Right-click on any one of them and select View Native Query.

3. The dialogue box below shows that Query Folding was performed for this column.

If the View Native Query option was grayed out, it would mean that query folding could not be applied to this step.

Query Folding Indicators

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.

Now let’s see them one by one.

Folding

This indicator indicates that the data source will execute the query up to this step.

Not Folding

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.

Might fold

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.

Opaque

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.

Unknown

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. 

The image below shows each indicator with its respective icon:

Steps to Setup Query Folding

To perform query folding in Power BI, follow the steps below:

Connect Power BI to a data source:

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. To connect to a data source, click on Get Data and choose the relevant data source. After that, guide the interface to the location of the source.

2. Load Data: You can now use the Query Editor to perform any edits you want. Click Transform Data, and you will be redirected to the Query Editor.

3. Perform Transformations: 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:

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

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

Transformations that Support Query Folding

Some of the most common transformations that support query folding are:

• Removing or renaming columns
• Merging foldable queries that are based on the same source.
• Appending foldable queries based on the same source.
• Numeric calculations
• Joins
• Pivot and unpivot

These are only a few examples, and there are numerous additional transformations that support query folding. However, it’s important to note that even though the SQL source is capable of supporting query folding, this does not guarantee that your query will actually fold.

The transformations that do not support query folding include:

• Merging or appending queries that are based on different sources. 
• Using some functions while adding custom columns that do not have a counterpart in SQL. 
• Adding columns with complex logic. These refer to functions that do not have equivalent functions in the data source.

Types of Query Folding

There are three ways a query folding statement could go:

1. Full query folding: All your transformations are pushed to the source, and processing occurs at the Query engine accordingly. 

2. Partial query folding: This happens when only a part of your transformations is pushed back to the source. Therefore, a part of the processing occurs in the query engine. This is sometimes used when performing some steps that prevent query folding.

3. No query folding: This might happen when the Query has transformations that cannot be translated to the language of the data source. This could be because the connector cannot support query folding or the transformations are not supported. Therefore, the Power Query, in this case, gets the raw data and uses the Power query engine for processing.

Query folding compatible sources

Some of the data sources that support query folding are given below:

• OData feeds
• SharePoint Lists 
• Web services
• DirectQuery capable sources like Azure Synapse Analytics, Azure Data Lake Storage, and Azure SQL Data Warehouse
• Exchange
• HDFS, Folder.Contents, Folder.Files

Query folding non-compatible sources

• Flat files (such as CSV or Excel)
• SharePoint Excel files
• Power Query connections
• Some NoSQL databases like MongoDB or Cassandra

Importance of Query Folding

Following are some of the importance of Query Folding:

Increased Efficiency

Query folding is extremely useful as it makes the whole process very efficient. It sends the data transformation and filter commands to the data source, where it is executed. This reduces the amount of data transferred and processed within Power BI. This results in faster transformation performance in Power BI.

Optimization of CPU Usage

Query folding optimizes the use of resources such as CPU usage and memory. By reducing the amount of data processed within Power BI, query folding reduces the pressure on the computer’s resources, leading to minimized risks of crashes and overall better performance.

Improved Data Security

In some cases, Query Folding might improve data security as sensitive data is not transferred to Power BI for transformations. It remains secure and protected on the data sources, and all the transformations are applied there.

Downsides of Query Folding

Following are some of the downsides of using Query Folding in Power BI:

Minimal Support

Query folding is only supported in specific data sources such as SQL servers and Oracle. Others, such as Excel Flat file, would not be able to take advantage of its benefits. This is also true if the said data source has certain restrictions on certain data types or functions. It limits the transformations that can be performed.

Complexity

Query folding can sometimes be very complex when dealing with a complex data source with several relationships. In these cases, performing transformations in PowerBI is always a better idea. Debugging Query folding issues can be complex as the data filtering and transformations occur at the source, which makes it very difficult to identify the root cause of the problem.

Conclusion

Query Folding is a robust feature of Power BI to create a single statement that can transform and execute tasks on the data source side. With Query Folding, Power Query can efficiently handle vast volume of data and visualize in interactive dashboards. Three types of Query Folding is available in the form of Full Query, Partial Query, and No Query folding. Users can utilize Power Query and Power BI engine for advanced data visualization.

Are you an Oracle user working with Oracle Fusion and Analytics data? Searching for the simplest way to connect Power BI to Oracle apps? Try BI Connector. We help users directly connect Power BI to Oracle database and create dashboards in minutes. You dont have to spend nights extracting data into Excel sheets. We offer a 30-day trial to experience BI Connector’s magic. No credit card required.

Get started for free now.