Ravindra BagaleCourses & study guides मराठी Track your progress

4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection

4.4 Query Folding with Databases and View Native Query

By Last updated:

Start with a problem

Shahrukh's query on a large table is slow. He added an Index column as step 2, and everything after it runs on his laptop.

Explain simply

Query folding turns your Power Query steps into one SQL query that the database runs. Right-click a step › View Native Query: if it is enabled, the step folds. Do folding steps first and non-folding steps last.

Show the data

order_id order_date store_id platform category amount delivery_mins status
301 2026-04-06 S01 Blinkit Dairy 400 8 Delivered
302 2026-04-06 S02 Amazon Now Snacks 200 12 Delivered
303 2026-04-07 S03 Blinkit Dairy 300 14 Delivered
304 2026-05-04 S04 Blinkit Fruits 150 9 Delivered
305 2026-05-04 S05 Amazon Now Snacks 450 7 Delivered
306 2026-05-05 S01 Blinkit Fruits 250 16 Delivered
307 2026-05-05 S04 Amazon Now Dairy 350 11 Delivered
308 2026-05-05 S05 Blinkit Dairy 0 NULL Cancelled

About this data: This table uses columns: order_id, order_date, store_id, platform, category, amount, delivery_mins, status. This table uses columns: View Native Query. This table uses columns: Rows. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: quickcommerce_practice.sql (source: src/practice/).

Demonstrate

  1. Navigator › dbo.orders › Transform Data › Choose Columns order_id, store_id, amount.

    Your result should look like this:

    View Native Query
    Enabled: select order_id, store_id, amount from dbo.orders

  2. Filter store_id = S01 or S02 (Pune stores).

    Your result should look like this:

    Rows
    301, 302, 306 · native query has a WHERE clause

  3. Group By store_id, Sum of amount.

    Your result should look like this:

    store_id amount
    S01 650
    S02 200

  4. Add Column › Index Column.

    Your result should look like this:

    View Native Query
    Greyed out: folding stopped here

  5. Move the Index step to the end (or remove it) and check the folding indicators again.

Show the result

Query folding: three steps fold into one SQL statement, the Index step stops folding, sample layout with fictional data

Your result should look like this (fictional sample):

Step Folds?
Choose columns Yes
Filter rows Yes
Group By Yes
Index column No (and every step after it runs locally)

Explain what happened

The first three steps became one SQL statement with SELECT, WHERE and GROUP BY, so only 2 rows travelled to Power BI. The Index step cannot be translated, so folding stopped there.

Explore further

  • Incremental refresh (10.8) depends on folding of the date filter.

Detailed reference for this lesson:

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

  1. In the Power Query Editor, right-click the last step in Applied Steps.
  2. If View Native Query is enabled, click it to see the SQL Power Query generated. The step folds.
  3. 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.
  4. 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.

Got it? Folding means giving the work to the database. If not, read this section once more – incremental refresh needs it.

Ravindra Bagale's Tip

Friends, many 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. Try it once more, then move on.

Fix common mistakes

Wrong result Reason Correction
Whole table downloaded A non-folding step placed early Put Index, custom functions and Excel merges last
View Native Query greyed out on step 1 Source is a native SQL statement Use the Navigator or a view
Merge with Excel is slow Merging across sources cannot fold Bring the lookup into the same database

Practise independently

Practice task

Your 5 steps are: choose columns, filter Pune, group by store, add index, sort by amount. Which steps fold, and how many rows does the database return?

Hints
  • Folding stops at the index step.
Solution

Choose, filter and group fold; index and sort run locally. The database returns 2 rows (S01 ₹650, S02 ₹200).

Think like a professional

Check folding on the last step before you publish a database query; it is the cheapest performance test you have.

Ravi Sir's Cards for this lesson

Query folding with databases. Refresh can take minutes or seconds. Folding is often the difference.Revision cardWhat is query folding? Query folding means Power Query turns your steps into a query in the source's own language, like SQL, so the source does the work and sends back only the result.Interview card

मराठी cards