6.10 Query Folding
Query folding in Power Query with example — in 30 seconds
What: Query folding means Power Query turns your steps into one SQL query, so the database does the heavy work.
Why: When folding breaks, Power BI downloads every row and filters on your laptop. Refresh becomes slow.
Tiny example: Orders has 5,00,000 rows. Pune has 60,000. With folding, only the 60,000 Pune rows travel to Power BI.
Query folding in Power Query with example — 30 सेकंदात
काय: Query folding म्हणजे Power Query तुमचे steps एका SQL query मध्ये बदलतो, म्हणजे जड काम database करतो.
का: Folding तुटलं की Power BI सगळ्या rows download करतो आणि तुमच्या laptop वर filter करतो. Refresh slow होतो.
छोटं उदाहरण: Orders मध्ये 5,00,000 rows आहेत. Pune च्या 60,000. Folding असेल तर फक्त Pune च्या 60,000 rows Power BI कडे येतात.
Query folding in Power Query with example — 30 सेकंड में
क्या: Query folding का मतलब है Power Query आपके steps को एक SQL query में बदल देता है, ताकि भारी काम database करे.
क्यों: Folding टूटा तो Power BI सारी rows download करके आपके laptop पर filter करता है. Refresh slow हो जाता है.
छोटा उदाहरण: Orders में 5,00,000 rows हैं. Pune की 60,000. Folding हो तो सिर्फ़ Pune की 60,000 rows Power BI तक आती हैं.
First look at the data (before → after):
Same before / after tables as text
Before — Applied Steps — Index added too early (all 5,00,000 rows travel)
| # | Step | View Native Query |
|---|---|---|
| 1 | Source + Navigation | On |
| 2 | Added Index | Greyed out |
| 3 | Filtered Rows (City = Pune) | Greyed out |
| 4 | Removed Other Columns | Greyed out |
About this data: This table uses columns: Step, View Native Query. This table uses columns: Step, Folds on SQL Server?, Notes. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Formula / click path
Drag "Added Index" to the bottom of Applied Steps
SQL sent: select [Order ID], [City], [Amount] from Orders where [City] = 'Pune'
After — Applied Steps — filter first (only 60,000 rows travel)
| # | Step | View Native Query |
|---|---|---|
| 1 | Source + Navigation | On |
| 2 | Filtered Rows (City = Pune) | On |
| 3 | Removed Other Columns | On |
| 4 | Added Index | Greyed out (runs on your PC, on 60,000 rows) |
Do it step by step:
- Connect to the SQL Server lab database. Pick Orders. Click Transform Data.
- Filter City = Pune first, using the filter arrow on the column.
- Click Home › Choose Columns. Keep only the columns you need.
- Right-click the last step. Click View Native Query. You should see SQL with a WHERE clause.
- If you really need an Index Column, add it as the very last step.
- Right-click the Index step. View Native Query is greyed out. That is fine: only this last step runs on your PC.
एक-एक step करून बघा:
- SQL Server lab database ला connect करा. Orders निवडा. Transform Data वर click करा.
- आधी column वरच्या filter arrow ने City = Pune filter करा.
- Home › Choose Columns वर click करा. फक्त लागणारे columns ठेवा.
- शेवटच्या step वर right-click करा. View Native Query वर click करा. WHERE clause असलेली SQL दिसली पाहिजे.
- खरंच Index Column हवा असेल तर तो अगदी शेवटची step म्हणून टाका.
- Index step वर right-click करा. View Native Query grey दिसतो. ठीक आहे: फक्त ही शेवटची step तुमच्या PC वर चालते.
एक-एक step करके देखिए:
- SQL Server lab database से connect कीजिए. Orders चुनिए. Transform Data पर click कीजिए.
- पहले column के filter arrow से City = Pune filter कीजिए.
- Home › Choose Columns पर click कीजिए. सिर्फ़ ज़रूरी columns रखिए.
- आख़िरी step पर right-click कीजिए. View Native Query पर click कीजिए. WHERE clause वाली SQL दिखनी चाहिए.
- सच में Index Column चाहिए तो उसे बिल्कुल आख़िरी step के रूप में जोड़िए.
- Index step पर right-click कीजिए. View Native Query grey दिखता है. ठीक है: सिर्फ़ यह आख़िरी step आपके PC पर चलता है.
What you should see on screen after the steps (sample layout — fictional data, not a Microsoft screenshot).
Start with a problem
Zoya connects a fictional Kolhapur warehouse SQL Server database (training lab) to Power BI. Refresh takes 20 minutes. She filtered to last 30 days in Power Query, but she also added an Index column near the top of Applied Steps. The database still seems to send almost every row. She has broken query folding — Power Query stopped translating steps into SQL, so the PC did the work.
Explain simply
Query folding means Power Query translates your steps into a single query in the source's own language (for example SQL) so the source database does the work, instead of Power BI downloading all rows and processing them locally.
- Folding happens only with sources that understand queries — SQL Server, Oracle, many OData feeds. Flat files such as Excel and CSV do not fold.
- Steps such as filter rows, remove columns, rename, simple joins, group by usually fold.
- Some steps break folding (Index column, some custom M). Once folding breaks, later steps run locally.
- How to check: right-click a step › View Native Query (enabled = that step folds). Newer versions show step folding indicators.
Show the data
Checkpoint table (also src/practice/query_folding_checkpoint.csv):
| Step | Folds on SQL Server? | Notes |
|---|---|---|
| Filter City = Pune | Yes | Push filter early |
| Remove unused columns | Yes | Smaller payload |
| Rename columns | Usually yes | Keep early |
| Add Index column | No | Breaks folding from here |
| Custom M with List.Generate | Often no | Check View Native Query |
Demonstrate
- Get data from SQL Server (lab) › choose Orders › Transform Data.
- Home › Reduce Rows › Filter Rows › City equals Pune (or date ≥ start). Why: less data at the source.
- Remove columns you do not need.
- Right-click the filter step › View Native Query — confirm SQL shows a WHERE clause.
- Add Index column on purpose, then right-click a later step — Native Query is often disabled. Delete the Index or move it to the end only if you truly need it.
- For CSV sources, explain to the class: View Native Query will not apply; folding is not available.
Show the result
Your result should look like this: early steps show View Native Query; after Index, later steps do not.
Explain what happened
While folding holds, Power BI sends one SQL batch. After a non-foldable step, Power BI must pull the intermediate result and continue in the mashup engine on your machine (or gateway). Incremental refresh and DirectQuery depend on folding staying healthy for the critical filters.
Explore further
- Prefer native SQL / Query in "SQL statement" only when you must; document it.
- Merge/Append of two foldable SQL queries may fold; mixing SQL + Excel breaks folding at the combine.
- Dual / DirectQuery models: folding failures surface as slow visuals, not only slow refresh.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Refresh still slow after filter | Index / custom M earlier broke fold | Move breaking steps last; re-check Native Query |
| "CSV will fold if I filter first" | Files have no query engine | Expect local processing; reduce columns anyway |
| Incremental refresh reads all history | Filter on date not folding | Fix folding before enabling incremental |
Practise independently
Practice task
On any SQL lab source, write down three steps that fold and one that breaks. Screenshot or note View Native Query on/off (do not upload secrets).
Hints
- Filter and remove columns first.
- Index column is the reliable "break" demo.
Solution
See src/practice/query_folding_checkpoint.csv and ANSWERS.md. Filter early = fold; Index = break.
Think like a professional
Business: overnight refresh windows fail when folding breaks before a large fact filter. Maintenance: name steps (Filter_Last30Days_IST) so the next developer sees intent. Interview: define folding in one sentence and name two steps that break it.
Why folding matters
Folding makes refresh much faster, it is how DirectQuery works, and it is essential for incremental refresh to be effective (without folding, Power Query still has to read all rows from the source). Do filtering and column removal early in your steps, and place steps that break folding as late as possible.
Ravindra Bagale's Tip
The mistake seen most often with folding is assuming everything folds. Steps after a native SQL query, an index column or a custom function often don't. Right-click the steps to check View Native Query, especially before setting up incremental refresh, which depends on folding. Try it once more, then move on.
Ravindra Bagale's Tip – मराठी
Folding मध्ये सगळ्यात जास्त दिसणारी चूक म्हणजे सगळं fold होतं असं गृहीत धरणं. Native SQL query, index column किंवा custom function नंतरच्या steps बऱ्याचदा fold होत नाहीत. Steps वर right-click करून View Native Query चेक करा, विशेषतः incremental refresh set करण्याआधी, कारण तो folding वर अवलंबून असतो. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
Folding में सबसे ज़्यादा दिखने वाली गलती है यह मान लेना कि सब कुछ fold होता है. Native SQL query, index column या custom function के बाद वाले steps अक्सर fold नहीं होते. Steps पर right-click करके View Native Query check करो, ख़ासकर incremental refresh set करने से पहले, क्योंकि वह folding पर निर्भर है. एक बार फिर करके देखो, फिर आगे बढ़ो.
Revision card