![\"Query](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/query-folding-in-power-bi.jpg\")
<\/a><\/div>\n<\/a> <\/a> <\/a><\/div>\nIn this guide, you’ll discover what query folding in Power BI is, understand how it works, and why it is important.<\/p>\n
Table of Contents:<\/h3>\n\n- What is Query Folding?<\/a><\/li>\n
- Understanding Power BI Query Folding<\/a><\/li>\n
- Why is Query Collapsing in Power BI Important?<\/a><\/li>\n
- How to Enable Query Folding in Power BI?<\/a><\/li>\n
- What are the Steps to Set Up Query Folding?<\/a><\/li>\n
- How to Visualize Query Folding in Power BI?<\/a><\/li>\n
- Wrap Up<\/a><\/li>\n<\/ol>\n
First…<\/p>\n
What is Query Folding?<\/h2>\n
Query Folding is the feature that enables the Power Query to transform and execute large volumes of data from the data source. With query folding, Power Query can efficiently handle vast volumes of data.<\/p>\n
Understanding Power BI Query Folding<\/h2>\n
Query folding is synonymous with writing down detailed instructions in SQL code, and running it through the database for enforcement. Simply put, the heavy lifting is done by the data source, and that will, in turn, reduce the volume of data that is transferred and processed by the Power BI.<\/p>\n
Before Power Query executes the query folding, the following conditions have to be met:<\/p>\n
\n- The data source should be able to accept a query request.<\/li>\n
- The configuration steps must be query-folding friendly. If a single action cannot be folded, you cannot proceed with query folding the entire query.<\/li>\n
- Don’t start the query with your SQL code. Starting the query with your SQL code will make it impossible to fold the query in the steps.<\/li>\n<\/ol>\n
Why is Query Collapsing in Power BI Important?<\/h2>\n
Here’s the overview of how query folding is important in Power BI.<\/p>\n
\n- \n
Enhanced Performance<\/h3>\n<\/li>\n<\/ul>\n
With query folding, data transformation tasks are delegated to the data source. That means operations like aggregating, joining data, and filtering are performed by the server \u2014 a more efficient and powerful way of processing data.<\/p>\n
Along with features like conditional formatting in Power BI<\/a>, this approach ensures optimized performance and cleaner visual outputs. Since queries are processed by the data source, only the needed data is transferred to Power BI, minimizing the volume moved across the network. This, in turn, speeds up data retrieval and reduces latency.<\/p>\n\n- \n
Scalability and Consistency<\/h3>\n<\/li>\n<\/ul>\n
Since the heavy lifting is handled by the data source, it becomes easy to work with complex and large datasets. Furthermore, the risk of discrepancies is reduced since the operations are handled within the data source.<\/p>\n
\n- \n
Resource Management<\/h3>\n<\/li>\n<\/ul>\n
Query folding helps in optimizing data workflows. Since data transformations<\/a> are performed at the data source level, local resources are freed up – and that gives room for more efficient data visualization<\/a> and modeling tasks.<\/p>\nHow to Enable Query Folding in Power BI?<\/h2>\n
Here are steps to help you enable query folding in Power BI.<\/p>\n
\n- Right-click on the Report View, and select \u201cEdit Query.\u201d After that, the \u201cPower Query Editor\u201d will be opened.<\/li>\n<\/ol>\n
![\"Select](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/select-edit-query-for-query-folding-in-power-bi.jpg\")
<\/a><\/div>\n\n- Several transformations will be displayed on the right. Right-click on your desired option, and choose “View Native Query.”<\/li>\n<\/ol>\n
![\"View](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/view-native-query-for-query-folding-in-power-bi.jpg\")
<\/a><\/div>\n\n- From the dialogue box below, you’ll notice that the Query Folding has been performed for the column.<\/li>\n<\/ol>\n
![\"Perform](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/perform-query-folding-in-power-bi.jpg\")
<\/a><\/div>\nThere are cases where the \u201cView Native Query\u201d option is grayed out – and that shows that query fold cannot be applied in the operation.<\/p>\n
\n- \n
Query Folding Indicators<\/h3>\n<\/li>\n<\/ul>\n
Query folding indicators use an underlying query plan to determine if a query will fold or not. Here is the list of the various indicator icons and their meaning.<\/p>\n
![\"Query](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/query-folding-indicators-for-query-folding-in-power-bi.jpg\")
<\/a><\/div>\n\n- \n
Folding<\/h3>\n<\/li>\n<\/ul>\n
The folding indicator shows that the query will be executed by the data source up to this step.<\/p>\n
\n- \n
Not Folding<\/h3>\n<\/li>\n<\/ul>\n
This indicator shows that certain parts of the query will not be processed by the data source. You need to rearrange the query to enable the data source to execute as many transformations as possible.<\/p>\n
\n- \n
Might Fold<\/h3>\n<\/li>\n<\/ul>\n
The indicators show that the folding status will be determined during the runtime. It cannot be determined beforehand. Might Fold only appears when you’re using ODBC connections or OData.<\/p>\n
\n- \n
Opaque<\/h3>\n<\/li>\n<\/ul>\n
This indicator shows that the Power BI connector<\/a> does not support the query plan indicator and tool. As a result, the query plan remains indeterminate.<\/p>\n\n- \n
Unknown<\/h3>\n<\/li>\n<\/ul>\n
This indicator shows that the query plan is absent. It could be due to an error. It could also happen when you try running the query plan evaluation, or when you try running something that’s not the query table.<\/p>\n
What are the Steps to Set Up Query Folding?<\/h2>\n
Here are easy steps to help you set up a query folding.<\/p>\n
\n- \n
Connect Power BI to a data source<\/h3>\n<\/li>\n<\/ul>\n
The first step is to connect to a data source that supports query folding. In this illustration, the Power BI will be connected to the Microsoft Access Database. Connecting to the data source is easy. You’ll have to click on \u201cGet Data\u201d and choose your preferred data source.<\/p>\n
![\"Click](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/click-get-data-for-query-folding-in-power-bi.jpg\")
<\/a><\/div>\nEdits are made in the \u201cQuery Edit.\u201d To go to the \u201cQuery Editor,\u201d click on \u201cTransform Data.\u201d<\/p>\n
![\"Transform](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/transform-data-for-query-folding-in-power-bi.jpg\")
<\/a><\/div>\nAfter opening the \u201cQuery Editor,\u201d perform transformations. And that can be done by pushing them into a data source. Transformations could be sorting rows of a column in ascending order.<\/p>\n
![\"Perform](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/perform-transformations-for-query-folding-in-power-bi.jpg\")
<\/a><\/div>\nThere is also the option of performing complex transformations like adding a conditional column. To do that, navigate to \u201cAdd column,\u201d and click the \u201cConditional column\u201d icon.<\/p>\n
![\"Click](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/07\/click-conditional-column-for-query-folding-in-power-bi.jpg\")
<\/a><\/div>\nTo load your data onto the Power BI, search for the “Close & Apply” button on the home tab and click on it. Repeat the process for all the columns that require query folding.<\/p>\n
How to Visualize Query Folding in Power BI?<\/h2>\n
You can use the Sankey graph in Power BI<\/a> to visualize query folding and understand how data flows through different query steps.<\/p>\nStage 1: Logging in to Power BI<\/strong><\/p>\n\n- Log in to Power BI.<\/li>\n
- Enter your email address and click the \u201cSubmit<\/strong>\u201d button.<\/li>\n<\/ul>\n
![\"Enter](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2023\/05\/enter-email-to-login-to-power-bi.jpg\")
<\/a><\/div>\n\n- You are redirected to your Microsoft account.<\/li>\n
- Enter your password and click \u201cSign in<\/strong>\u201c.<\/li>\n<\/ul>\n
![\"Enter](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2023\/05\/enter-password-to-login-to-power-bi.jpg\")
<\/a><\/div>\n\n- You can choose whether to stay signed in.<\/li>\n<\/ul>\n
![\"Click](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2023\/05\/click-on-stay-signed-in.jpg\")
<\/a><\/div>\n\n- Once done, the Power BI home screen will open.<\/li>\n<\/ul>\n
Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart<\/h4>\n\n- Go to the left-side menu and click the \u201cCreate<\/strong>\u201d button.<\/li>\n
- Select \u201cPaste or manually enter data<\/strong>\u201c.<\/li>\n<\/ul>\n
![\"select](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2024\/02\/select-paste-or-manually-enter-data-in-power-bi-ce487.jpg\")
<\/a><\/div>\n![\"\"](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2023\/04\/CTA-in-power-bi.jpg\")
<\/a> ![\"\"](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2023\/04\/CTA-in-google-sheets.jpg\")
<\/a> ![\"\"](\"https:\/\/chartexpo.com\/blog\/wp-content\/uploads\/2023\/04\/CTA-in-microsoft-excel.jpg\")
<\/a><\/div>\n\n- We’ll use the sample data<\/a> below for this example.<\/li>\n<\/ul>\n
First…<\/p>\n
What is Query Folding?<\/h2>\n
Query Folding is the feature that enables the Power Query to transform and execute large volumes of data from the data source. With query folding, Power Query can efficiently handle vast volumes of data.<\/p>\n
Understanding Power BI Query Folding<\/h2>\n
Query folding is synonymous with writing down detailed instructions in SQL code, and running it through the database for enforcement. Simply put, the heavy lifting is done by the data source, and that will, in turn, reduce the volume of data that is transferred and processed by the Power BI.<\/p>\n
Before Power Query executes the query folding, the following conditions have to be met:<\/p>\n
- \n
- The data source should be able to accept a query request.<\/li>\n
- The configuration steps must be query-folding friendly. If a single action cannot be folded, you cannot proceed with query folding the entire query.<\/li>\n
- Don’t start the query with your SQL code. Starting the query with your SQL code will make it impossible to fold the query in the steps.<\/li>\n<\/ol>\n
Why is Query Collapsing in Power BI Important?<\/h2>\n
Here’s the overview of how query folding is important in Power BI.<\/p>\n
- \n
- \n
Enhanced Performance<\/h3>\n<\/li>\n<\/ul>\n
With query folding, data transformation tasks are delegated to the data source. That means operations like aggregating, joining data, and filtering are performed by the server \u2014 a more efficient and powerful way of processing data.<\/p>\n
Along with features like conditional formatting in Power BI<\/a>, this approach ensures optimized performance and cleaner visual outputs. Since queries are processed by the data source, only the needed data is transferred to Power BI, minimizing the volume moved across the network. This, in turn, speeds up data retrieval and reduces latency.<\/p>\n
- \n
- \n
Scalability and Consistency<\/h3>\n<\/li>\n<\/ul>\n
Since the heavy lifting is handled by the data source, it becomes easy to work with complex and large datasets. Furthermore, the risk of discrepancies is reduced since the operations are handled within the data source.<\/p>\n
- \n
- \n
Resource Management<\/h3>\n<\/li>\n<\/ul>\n
Query folding helps in optimizing data workflows. Since data transformations<\/a> are performed at the data source level, local resources are freed up – and that gives room for more efficient data visualization<\/a> and modeling tasks.<\/p>\n
How to Enable Query Folding in Power BI?<\/h2>\n
Here are steps to help you enable query folding in Power BI.<\/p>\n
- \n
- Right-click on the Report View, and select \u201cEdit Query.\u201d After that, the \u201cPower Query Editor\u201d will be opened.<\/li>\n<\/ol>\n<\/a><\/div>\n
- \n
- Several transformations will be displayed on the right. Right-click on your desired option, and choose “View Native Query.”<\/li>\n<\/ol>\n<\/a><\/div>\n
- \n
- From the dialogue box below, you’ll notice that the Query Folding has been performed for the column.<\/li>\n<\/ol>\n<\/a><\/div>\n
There are cases where the \u201cView Native Query\u201d option is grayed out – and that shows that query fold cannot be applied in the operation.<\/p>\n
- \n
- \n
Query Folding Indicators<\/h3>\n<\/li>\n<\/ul>\n
Query folding indicators use an underlying query plan to determine if a query will fold or not. Here is the list of the various indicator icons and their meaning.<\/p>\n
<\/a><\/div>\n- \n
- \n
Folding<\/h3>\n<\/li>\n<\/ul>\n
The folding indicator shows that the query will be executed by the data source up to this step.<\/p>\n
- \n
- \n
Not Folding<\/h3>\n<\/li>\n<\/ul>\n
This indicator shows that certain parts of the query will not be processed by the data source. You need to rearrange the query to enable the data source to execute as many transformations as possible.<\/p>\n
- \n
- \n
Might Fold<\/h3>\n<\/li>\n<\/ul>\n
The indicators show that the folding status will be determined during the runtime. It cannot be determined beforehand. Might Fold only appears when you’re using ODBC connections or OData.<\/p>\n
- \n
- \n
Opaque<\/h3>\n<\/li>\n<\/ul>\n
This indicator shows that the Power BI connector<\/a> does not support the query plan indicator and tool. As a result, the query plan remains indeterminate.<\/p>\n
- \n
- \n
Unknown<\/h3>\n<\/li>\n<\/ul>\n
This indicator shows that the query plan is absent. It could be due to an error. It could also happen when you try running the query plan evaluation, or when you try running something that’s not the query table.<\/p>\n
What are the Steps to Set Up Query Folding?<\/h2>\n
Here are easy steps to help you set up a query folding.<\/p>\n
- \n
- \n
Connect Power BI to a data source<\/h3>\n<\/li>\n<\/ul>\n
The first step is to connect to a data source that supports query folding. In this illustration, the Power BI will be connected to the Microsoft Access Database. Connecting to the data source is easy. You’ll have to click on \u201cGet Data\u201d and choose your preferred data source.<\/p>\n
<\/a><\/div>\nEdits are made in the \u201cQuery Edit.\u201d To go to the \u201cQuery Editor,\u201d click on \u201cTransform Data.\u201d<\/p>\n
<\/a><\/div>\nAfter opening the \u201cQuery Editor,\u201d perform transformations. And that can be done by pushing them into a data source. Transformations could be sorting rows of a column in ascending order.<\/p>\n
<\/a><\/div>\nThere is also the option of performing complex transformations like adding a conditional column. To do that, navigate to \u201cAdd column,\u201d and click the \u201cConditional column\u201d icon.<\/p>\n
<\/a><\/div>\nTo load your data onto the Power BI, search for the “Close & Apply” button on the home tab and click on it. Repeat the process for all the columns that require query folding.<\/p>\n
How to Visualize Query Folding in Power BI?<\/h2>\n
You can use the Sankey graph in Power BI<\/a> to visualize query folding and understand how data flows through different query steps.<\/p>\n
Stage 1: Logging in to Power BI<\/strong><\/p>\n
- \n
- Log in to Power BI.<\/li>\n
- Enter your email address and click the \u201cSubmit<\/strong>\u201d button.<\/li>\n<\/ul>\n<\/a><\/div>\n
- \n
- You are redirected to your Microsoft account.<\/li>\n
- Enter your password and click \u201cSign in<\/strong>\u201c.<\/li>\n<\/ul>\n<\/a><\/div>\n
- \n
- You can choose whether to stay signed in.<\/li>\n<\/ul>\n<\/a><\/div>\n
- \n
- Once done, the Power BI home screen will open.<\/li>\n<\/ul>\n
Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart<\/h4>\n
- \n
- Go to the left-side menu and click the \u201cCreate<\/strong>\u201d button.<\/li>\n
- Select \u201cPaste or manually enter data<\/strong>\u201c.<\/li>\n<\/ul>\n
<\/a><\/div>\n<\/a> <\/a> <\/a><\/div>\n- \n
- We’ll use the sample data<\/a> below for this example.<\/li>\n<\/ul>\n
- Select \u201cPaste or manually enter data<\/strong>\u201c.<\/li>\n<\/ul>\n
- Go to the left-side menu and click the \u201cCreate<\/strong>\u201d button.<\/li>\n
- Once done, the Power BI home screen will open.<\/li>\n<\/ul>\n
- You can choose whether to stay signed in.<\/li>\n<\/ul>\n
- \n
- \n
- \n
- \n
- \n
- \n
- \n
- From the dialogue box below, you’ll notice that the Query Folding has been performed for the column.<\/li>\n<\/ol>\n
- Several transformations will be displayed on the right. Right-click on your desired option, and choose “View Native Query.”<\/li>\n<\/ol>\n
- Right-click on the Report View, and select \u201cEdit Query.\u201d After that, the \u201cPower Query Editor\u201d will be opened.<\/li>\n<\/ol>\n
- \n
- \n
- \n