4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
4.4 Query Folding with Databases and View Native Query
Query folding means Power Query translates your steps (filter rows, remove columns, group, merge) into one SQL query that the database runs. Only the result travels to Power BI. With databases this is the biggest performance factor.
Steps in Power BI – check whether a step folds
- In the Power Query Editor, right-click the last step in Applied Steps.
- If View Native Query is enabled, click it to see the SQL Power Query generated. The step folds.
- If it is greyed out, folding has stopped at this step or earlier. Right-click the earlier steps to find the last one that folds.
- Newer versions also show step folding indicators (small icons next to each step) that tell you whether a step is evaluated by the data source.
| Usually folds (for SQL sources) | Usually breaks folding |
|---|---|
| Remove/choose columns, filter rows, sort, rename | Custom functions, Add Index Column |
| Group By, Merge/Append of tables from the same database | Merging with an Excel file or another server |
Simple calculated columns (e.g. [Amount] - [Discount]) |
Many text functions, Column From Examples results that use unsupported functions |
| Changing type (most cases) | A hand-written native SQL statement (later steps) |
Order of steps matters
Do the folding steps (filter, remove columns) first, and non-folding steps (index column, custom functions) last. Once folding breaks, later steps are done on your machine.
Practice task
Build an Orders query from SQL Server with 5 steps: choose columns, filter City = "Pune", group by StoreID, add an index column, sort. Find out which step stops View Native Query.
Samjla ka? Folding mhanje kaam database kade dene. Nasel tar ha section punha ekda vacha – incremental refresh sathi he garjeche aahe.
Ravindra Bagale's Tip
Mitrano, khup students add an Index column or a merge with a local Excel file early in the steps and unknowingly break query folding. Right-click the last step and check whether View Native Query is still available. Keep foldable steps (filters, removing columns, renames) at the top. Punha ekda karun bagha, mag pudhe jaa.