What is Query Folding in Power Query?
Query folding in Power Query refers to the ability of the Power Query to generate a single query statement that can fetch and apply data transformation to the source dataset. Query folding can bring efficiency to query execution by the Power Query mashup engine by pushing parts of a query back to the data source for execution, rather than performing those operations within Power Query itself.
This optimization can significantly improve query performance by delegating tasks to the source database or service, reducing the amount of data transferred to the Power BI or Excel environment.
The query folding process involves the translation of M script to an interpretable and executable language for query execution in a fast and efficient manner. Query folding can produce three possible outcomes based on how the queries are defined. These include Full query folding, Partial query folding, and No query folding.
The table below provides a quick summary of the differences between these three outcomes.
| Full query folding | Partial query folding | No query folding |
|---|---|---|
| • Involves minimal processing at the Power Query engine • All the query transformation is pushed back to the data source | • A subset of query transformation is pushed back to the data source • The remaining query transformations happen in the Power Query engine | • No transformation at the data source level • Power Query processes the entire transformation at the Power Query engine level |
Table of contents
Key Takeaways
- A query in Power Query can generate a single query statement through the Query folding mechanism to retrieve and transform source data.
- Query folding can generate 3 possible outcomes, i.e. No query folding, partial query folding, and full query folding.
- Certain processes prevent Query folding such as merging/appending different queries, adding index columns, or using different data sources with incompatible data source privacy levels.
- Query folding has high importance because it can bring efficiencies in performance improvement, reduces the data transfer and processing time, and optimizes the resources.
How to use query folding in power query?
To use query folding in Power Query, follow these steps:
Step 1: Open Power Query Editor in Power BI or Excel.
Step 2: Write your query steps using the Power Query M language.
Step 3: Check the “Applied Steps” pane to verify if query folding is occurring. Look for a small database icon next to the applied step, indicating that the operation has been pushed to the source.
Step 4: Ensure that the operations you are performing are supported by the data source for query folding.
Step 5: Ensure that the data source for query folding supports the operations you are performing.
Query folding Power BI dataflow: Power BI dataflows, built using Power Query, support query folding when the underlying data source allows it.
Query folding Power BI databricks: Databricks is a big data analytics platform that can be used as a data source in Power BI. The extent to which query folding is supported with Databricks depends on the specific connector and transformations used in Power Query.
In the next section, we will look at some examples to understand query folding in PowerQuery.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
In this section, we will demonstrate two examples of query folding, i.e., Full query folding and No query folding, and understand how to use it in Power query.
Example #1
In this example, we will demonstrate the Full query folding in Power query. For the demo purpose, we will use the Wide World Importers database for Azure Synapse Analytics SQL database. The database contains a fact_Sale table that contains data fields such as Customer Key, Quantity, Description, Sale Key, and Invoice Date Key.
For the Full query folding in Power query, follow the steps highlighted below:
Step 1: Connect to the fact_Sale table in the Power Query window.
Choose the columns you want to keep from the fact_Sale table by clicking on the Choose Columns option.
Step 2: Click on the Sort descending option for the Sale Key field to display the latest sales data.
Step 3: Select the table contextual menu and choose the Keep top rows option.
Step 4: Provide a choice of number in the Keep top rows dialogue box.
Step 5: Navigate to the APPLIED STEPS section in the Query Settings pane. You will notice the query folding indicators in the Power Query window based on the steps you have performed.
Example #2
In this example, we will demonstrate the No query folding in Power query. For the demo purpose, we will use the same database that we have used for Example 1.
For the No query folding in Power query, follow the steps highlighted below:
Step 1: Connect to the database and fact_Sale table. Choose Keep Bottom Rows in the Keep Rows dropdown in the Reduce Rows group of the Home tab.
Step 2: Provide a choice on the number of rows to keep in the Keep bottom rows window.
Step 3: Choose the columns you want to keep from the fact_Sale table by clicking on the Choose Columns option.
Choose the columns.
Step 4: Select the table contextual menu and choose the Keep top rows option. Navigate to the APPLIED STEPS section in the Query Settings pane. You will notice the query folding indicators in the Power Query window based on the steps you have performed.
To view the query plan, you can right-click on the Choose columns and select the View query plan option.
How to prevent query folding in power query?
There are multiple ways to prevent query folding. Here are some ways to prevent query folding in Power Query:
- Merge multiple queries that are based on various sources.
- Append multiple queries, i.e., apply unions that are based on various sources.
- Add customized data fields or columns that are derived using complex logic (especially created using M functions) in Power Query.
- Add index columns
- Power Query comprising of different data sources with incompatible data source privacy levels
Importance
Query folding has high importance for multiple reasons, as highlighted below:
- Importing model tables: Query folding can efficiently refresh data when you import model tables in Power Pivot or Power BI Desktop by optimizing resource utilization and duration of data refresh
- Power Query query must achieve query folding for every DirectQuery and Power BI Dual storage mode tablePower BI Desktop Installation
- Query folding Power BI incremental refresh: Power Query can bring in efficiencies to query folding power bi incremental refresh
- Performance: Query folding can significantly improve performance by offloading operations to the data source.
- Reduced Data Transfer: Query folding reduces the amount of data transferred between the source and Power BI/Excel, leading to more efficient data retrieval.
- Optimized Resource Usage: By leveraging the capabilities of the source database, query folding minimizes resource usage in the Power BI/Excel environment.
Important Things To Note
- Query folding depends on the data source’s ability to process and execute the operations. Not all operations can be folded.
- Query folding may be affected by data privacy settings. Ensure that the privacy levels are appropriately configured.
- Check the “Applied Steps” pane and query execution plans to monitor and verify query folding.
- Power Query query cannot use any common table expressions (CTEs) or a stored procedure for a DirectQuery model table. It should be a SELECT statement.
- You can’t use a native SQL query for query folding power bi incremental refresh as this would enforce the Power Query mashup engine to fetch all records and then filter the dataset to evaluate any incremental changes.
- Using Power Query indicators can be useful to identify the steps that may prevent the query from folding.
- It is recommended to use query diagnostics tools to understand the requests sent to the data source for query folding processing.
- In the event of adding a new step to a fully folded query and the new step folds, the Power query attempts to send a new request to the data source. Power query doesn’t use any cached version of the earlier result.
- A query plan can help you to identify the transformation process at the Power Query engine for any specific step.
Frequently Asked Questions (FAQs)
You can update a query in Power Query through Power BI by following these steps:
• For a new dataset in Power BI, navigate to the “Home” tab, import the data using the Get data option, and Click on “Transform Data” to open the Power Query Editor. For Existing datasets in Power BI, navigate to the Fields pane in Power BI, right-click on a table, and select Edit query option
• In the Power Query Editor, locate the query that you want to update. You can see a list of queries on the left side of the Power Query Editor.
• Make changes to the query steps in the Power Query Editor. You can add, remove, or modify steps based on your requirements.
• Once you have made the necessary changes, click on the “Close & Apply” button in the Home tab.
Click “Apply” to apply changes. Close the Power Query Editor.
• After applying the changes, you may need to refresh your data in Power BI to see the updated results.
Query folding is automatically handled by Power Query for certain operations. You can check if query folding is active by looking at the “Applied Steps” in the Power Query Editor. If you see steps that are folded, it means those steps are being pushed back to the data source.
• Data Source Support: Not all data sources support query folding. Some databases and systems may not be able to fold certain operations.
• Complex Transformations: Complex transformations or custom functions may prevent query folding. Simple operations are more likely to be folded.
• Mixed Data Sources: Combining data from multiple sources in a single query may disable query folding, as it may not be possible to fold operations across different sources.
If query folding is not working as expected, consider the following:
• Verify if your data source supports query folding.
• Simplify your transformations.
• Custom functions or expressions may turn off query folding.
• Ensure that the data types in your query are consistent.
• In Power Query Editor, go to the “View” tab and select “Query Dependencies” to review the query plan.
Recommended Articles
This has been a guide to Query folding in Power Query. Here we learn how to use and prevent query folding in power query, with importance and its points to remember. You can learn more from the following articles –
Leave a Reply