Power BI has a feature named Query Folding at the backend that can significantly improve your analysis. Thus, when introducing the concept of the digital business ecosystem, it is crucial to understand what it is and how it functions to harness its full potential. This article will discuss Query Folding and enrich it with examples so you do not have to look for it again.
What is Query Folding?
Query Folding is another feature in the Power Query that allows it to generate a single statement to manipulate or perform tasks on the data source side, such as filtering or performing a few others on a thousand records. Query Folding helps Power Query handle large amounts of data quickly. Power Query can easily handle large amounts of data with the help of Query Folding.
Understanding Query Folding in Power BI
Query Folding may be considered as having your instructions written in structured query language form; whereafter, it is handed over to the base of the database to be put into practice. In other words, it acted as an input data source, taking much of the work on data processing and transferring within Power BI.
Power Query will automatically execute Query Folding under the following conditions:
- A data source is an object that can process query requests, just like a database used in most cases.
- All the steps in the configuration must support the folding of the Query into the context. Gosh, you bring up an interesting point. If you do even one operation that cannot be folded, Query Folding will be halted for the entire Query.
- The Query must start with something other than an SQL statement of your own. Specifically, if you begin a Google query with your SQL command, none of the steps in the Query will be rewritten.
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:
- Go to the field pane and navigate to the Report View part where your particular sheet is situated; then right-click on your sheet and choose Edit Query from the options available. The result will appear on the query table, and Power Query Editor will open as a new tab or window.
- In what follows, on the right, you will be given several transformations. This is seen in the following ways: Right-click on any of them and select View Native Query.
- The screenshot below outlines that information querying for this column was folded.
If there is a checkmark beside the View Native Query option or it has been grayed out, it should mean that applying the Query Folding process was impossible at that step.
Query Folding Indicators
Query Folding indicators help understand the various aspects of a query. This means that when forming a query, one can ascertain whether or not the formed Query will fold depending on the formulated query plan. Below is the image for the indication that appears on the snapshot, indicating something with the snapshot.
However, specifically, to answer the initial question, they can be listed as follows:
Power BI Query Folding
This indicator means the data source will perform the Query up to this step.
Not Folding
This means that the data source will not execute some parts of the Query if they are included, namely projection and ordering instructions. To get closer to optimal performance, it is better to adjust the order of operations so that most operations are the data source.
Might Fold
This indicator shows that the ability to fold or create a control flow that anticipates whether a block of code should be folded cannot be predicted at compile-time but would be dynamic at run-time. When connected to OData or ODBC, it is observed to be available and not seen in other situations.
Opaque
This indicates that the status of the query plan is uncertain and may suggest that the query plan tool and other indicators have yet to be incorporated into the connector.
Unknown
This is a possible state showing that the underlying query plan is missing. This might happen by mistake or when you try executing the query plan evaluation with stand-alone objects outside the table.
Steps to Enable Folding Power BI
Query Folding in Power BI example:
- Connect Power BI to a data source: To execute a Query Folding in Power BI, one must have a linked data source compatible with the Query Folding feature of Power BI. For this demonstration, I will incorporate the usage of Microsoft Access Database. There are two ways of achieving this: directly with the query designer or clicking on Get Data and selecting the appropriate data connection. Next, inform the interface where the relative source is located.
- Load Data: Any additional edits you wish to carry out can now be done with the Query Editor’s help.
- Record Transformation: Click Transform Data to redirect you to the Query Editor.
- Perform Transformations: As soon as the ‘Query Editor’ pops up, you can start working on transformation patterns and push them into place in 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 do another operation, like control, and select an expression, such as a conditional column.
Go to Add Column Tab and Add Condition Column. After that, add a condition as shown below:
- Apply the Transformations: On the Home tab, click Close & Apply, which loads the data from the Query into Power BI. It is also possible to perform the same procedure for each necessary column in which you want to apply Query Folding.
Transformations that Support Query Folding
Some of the most common transformations that support Query Folding are:
- Removing or renaming columns
- One alternative is merging foldable queries obtained using the same source.
- Foldable queries are added based on the source of information used.
- Numeric calculations
- Joins
- Pivot and unpivot
The transformations that do not support Query Folding include:
- Concatenation or joining of the queries is formed based on different attributes.
- Testing some functions when defining columns not initially included in the SQL matrix.
Types of Query Folding in Power BI
There are three ways a Query Folding statement could go:
- Full Query Folding: All your transformations are pushed to the source, and processing is done professionally at the Query engine.
- Partial Query Folding: This is so because when only part of the transformations are pushed back to the source, the overall system becomes more complex than necessary. So, a part of the processing takes place in the query engine. This is sometimes used when doing some activities that prevent queries from folding.
- No Query Folding: It may occur when the Query has mappings that the language of the data source cannot overwrite. The connector cannot execute a Query Folding on Transformation, the transformations are not supported in this case. Hence, while using the Power Query, the raw data is grabbed, and the work is done using the Power Query engine.
Query Folding Compatible Sources
Some of the data sources that support Query Folding are given below:
- OData feeds
- SharePoint Lists
- Web services
- Other DirectQuery-enabled sources such as Azure Synapse, Azure Data Lake Storage, 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 incredibly helpful because it streamlines the entire process. The data source receives the commands for data transformation and filtering, which are then carried out there. This reduces the amount of data sent and received through Power BI and the amount of information filtered through it. In the case of Power BI, this made it possible to adapt considerably more quickly and with improved performance.
Optimization of CPU Usage
It enhances resource utilization, such as the rate at which the CPU or the memory is used. Query folding, referred to as sub-sampling, is the ability to limit the amount of data passed through Power BI at any one time. This, in turn, reduces the current load on computer hardware resources, thereby reducing the current incidence of bright power BI and improving performance.
Improved Data Security
In some scenarios, the Query Folding approach may improve data security measures as risky data is not sent to Power BI for operations. It remains locked and protected on the data sources, so it is possible to do matrix transformations to the data over there.
Downsides of Query Folding
Following are some of the downsides of using Query Folding in Power BI:
Minimal Support
Query Folding is performed in particular data sources, such as SQL servers and Oracle, but only in some. Only certain data sources, including SQL servers and Oracle, offer query folding. Its advantages would be inaccessible to others, like Excel Flat File. This is also true if the data source has some constraints on data types or functions. It limits the transformation that can be performed.
Complexity
One disadvantage of Query Folding is that this mechanism can be highly complicated when the structure of a data source contains many relations. In these cases, it is always more advisable to do transformation in Power BI. Generally, sorting out Query Folding issues can be intricate because the data selection and transformation occur at the source; therefore, defining the problem’s origin becomes a real challenge.
Conclusion
Query Folding is one of the most impressive parts of Power BI, and it allows for the creation of statements working on the data source side and the transformation into other expressions on the model side. When integrated with Query Folding, Power Query can handle massive data sets and provide visualization in interactive dashboards. The type of Query Folding is in three categories: Complete Query Folding, Partial Query Folding, and No Query Folding. The Integrated Power BI engine and Power Query will allow users to visualize complex data. Enhance Your Data Skills with Our Power BI Course Today and Master Data Modeling for Deep Insights!
FAQ
1. What is the use of Query Folding in Power BI?
In essence, Query Folding in Power BI involves rewriting query transformations probably in the Power Query Editor as native queries to be executed by the source database. This has the advantage of offloading and optimizing processing in the Database itself as it minimizes the amount of data that is moved to Power BI, making the process faster and scalable.
2. Does Query Folding improve performance?
Yes! Query Folding helps in improving the operation of Power BI to a greater extent as it reduces raw data shipment between the two. The possibility of performing transformations within the Database reduces retrieval and transfer of data, hence increasing efficiency since data refresh is faster, especially when dealing with huge amounts of data.
3. What is Query Folding in incremental refresh?
When it comes to incremental refresh scenarios, then Query Folding becomes mandatory. This optimizes the performance by getting only new or updated data and pushing the filtering logic to the source database. This way Power BI does not request and pull large amounts of data in case of refreshes, contributing to better refresh times and less burden on the system.
4. How to disable Query Folding in Power BI?
Although helpful in some situations, it might be necessary to turn off this feature for one or another reason (for instance, when debugging). To do this in Power Query Editor:
- Always create your own SQL statements, though you are free to base them on existing templates.
- Suggest adding more complex transformations that cannot undergo folding.
- Introduce a new step that is not foldable, for example, a column that a car owner should follow when solving a particular task.
This makes sure that the transformations are done inside Power BI not even the Database.