Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 56 ยท Power Query

Query Folding, Incremental Refresh and Query Performance

Understand query folding, native queries and step order in Power Query, set up incremental refresh, and keep refreshes fast with simple habits.

A Power Query refresh can take seconds or hours, and the difference often has nothing to do with how much data you finally keep. It depends on where the work happens. If the database does the filtering and grouping, only the small result travels to Power BI. If Power BI does it, every row must be downloaded first. The mechanism that decides this is called query folding.

In this lesson you will learn what folding is, which steps usually fold and which break it, how to see what was sent to the source, why step order matters, how incremental refresh avoids reloading old data, and when to switch off loading. It builds on the editor ideas in The Power Query Editor and on Import, DirectQuery and Live Connection.

The Power Query engine and your data source are not available on this website. Everything about Power BI here is described in text, and the M and policy snippets are read-only. The runnable examples are Python stand-ins: a simulated "database" that counts how many rows travel to the client, so you can see why early filtering matters. The numbers they print come from the stand-in, not from a real refresh.

What query folding means

When you connect to a relational source such as SQL Server, each applied step is translated by Power Query. If the translation is possible, the steps are combined into one native query (for example a single SELECT ... WHERE ... GROUP BY) that the database runs. The result of that one query is all that reaches Power BI. This is folding: the steps fold back into the source's own language.

If a step cannot be translated, Power Query stops folding at that point. It downloads the data produced so far, and runs the rest of the steps itself, on your machine or on the service. Everything after the break is not folded, even if each later step could have folded alone.

Folded versus not folded

Folded The database does the work.
Not folded Power BI downloads, then works.

Fewer rows travelling means a faster refresh.

1 / 2

What folds and what does not

Folding depends on the connector, so there is no complete list, but a few patterns are reliable for relational databases:

Usually folds Often breaks folding
Filtering rows by value or date range Adding an index column
Selecting or removing columns Custom columns using M-only functions
Renaming columns Merging with a query from a different source
Sorting Pivoting and unpivoting (depends on the source)
Group By with common aggregations Splitting a column by delimiter in some sources
Merging two tables from the same database Functions with no SQL equivalent, such as Text.Proper
Changing a column's data type (mostly) Using Table.Buffer

Files such as CSV and Excel do not fold at all: there is no engine on the other side that can run a query. Folding is a database (and a few online-service) feature. For files, speed comes from reading less and from keeping steps simple.

Seeing the native query

In the editor, right-click a step and look at View Native Query. If it is available, the step folds, and a window shows the SQL that will be sent. If the option is greyed out, folding stopped at or before that step. Check the steps one by one from the top to find the first greyed-out one; that is your break.

Beyond that, the Query Diagnostics tools and the database's own query log (for example the SQL Server profiler or query store) show what was really sent and how long it took.

A typical folded chain, in M, looks like this:

TEXT
let
    Source = Sql.Database("sql01", "Sales"),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data],
    Recent = Table.SelectRows(Orders, each [OrderDate] >= #date(2025, 1, 1)),
    Slim = Table.SelectColumns(Recent, {"OrderID", "OrderDate", "CustomerID", "Amount"})
in
    Slim

The native query is conceptually SELECT OrderID, OrderDate, CustomerID, Amount FROM dbo.Orders WHERE OrderDate >= '2025-01-01'. The database does the filtering and trimming, and Power BI receives only what remains.

Step order: filter early, break late

Because folding stops at the first step that cannot fold, put foldable steps first and non-foldable ones last. A common mistake is to add an index column or a clever text transformation straight after the source. That one step breaks folding, and the filters that follow run on the full downloaded table.

Good habits:

  • Filter rows and remove columns as early as possible, immediately after the source.
  • Do data type changes and renames next; they usually fold.
  • Leave non-foldable steps (index columns, M-only functions) for the end, when the data is already small.
  • Avoid Table.Buffer unless you really need it: it loads the table into memory and ends folding.

If you must use a step that breaks folding, try to do the heavy filtering before it, or move the logic into the database as a view. A SQL view with the right filter and joins is often the most robust fix, because the work then lives where the data lives.

Stand-in 1: early versus late filtering

The first stand-in simulates a database. Calling query(...) executes filters at the source and counts the rows that are sent back. Doing the same work after downloading shows the cost of a broken fold. The numbers are from the simulation, not a real server.

Python Stand-in: rows transferred, folded vs not folded

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both approaches produce the same table, but the second one moved four times as many rows. With real data and a real network, that difference can be minutes.

Incremental refresh

Even with perfect folding, reloading ten years of sales every night wastes time when only today's rows changed. Incremental refresh keeps old data and reloads only recent partitions.

Setting it up has three parts:

  1. Create two date/time parameters named exactly RangeStart and RangeEnd. Power BI fills them in itself during refresh.
  2. Filter the fact table's date column with them: [OrderDate] >= RangeStart and [OrderDate] < RangeEnd. Use greater or equal on one end and less than on the other, so no row is counted twice.
  3. Right-click the table in Desktop and choose Incremental refresh. Choose how much history to store (for example 5 years) and how much to refresh (for example the last 7 days).
TEXT
let
    Source = Sql.Database("sql01", "Sales"),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data],
    InRange = Table.SelectRows(Orders, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    InRange

After you publish, the service splits the table into partitions (by year, month or day) and on each refresh reloads only the ones inside the refresh window. A few rules matter in practice:

  • The filter on RangeStart and RangeEnd must fold. If it does not, every partition downloads the full table and the benefit disappears. A warning in Desktop tells you when it cannot confirm folding.
  • The date column should be a real date or datetime, and rows should rarely change after they are old. Optional settings let you detect changed rows through a "last modified" column.
  • Incremental refresh policies need a licence tier and a source that supports the feature; check the current requirements for your tenant.

Disable load and other performance habits

Not every query needs to be a table in the model. Helper queries, parameters and staging tables should have Enable load switched off (see Custom Functions and Reusable Queries). A smaller model loads faster, uses less memory and is easier to understand.

Other habits that help refreshes:

  • Remove columns you do not need as early as possible. Fewer columns means fewer bytes and a smaller model.
  • Avoid loading free-text columns with many unique values unless a visual really needs them.
  • Reduce precision where you can: dates instead of date-times, whole numbers instead of long decimals.
  • Prefer one wide fact query with several measures over many near-duplicate queries.
  • Turn off Auto date/time for tables that you replace with a proper date table (covered in Date Tables and Time Intelligence Setup).

Stand-in 2: partitions and an incremental refresh

The second stand-in imitates an incremental policy: monthly partitions are stored, and a refresh reloads only the partitions that fall inside the refresh window. It counts the rows it had to read.

Python Stand-in: reload only recent partitions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Real incremental refresh also creates partitions for you, can archive old ones, and can detect changes; this stand-in keeps only the core idea.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does query folding mean?

    Folding means the steps are translated into the source's own language, such as one SQL statement, so the source does the work and sends back only the result.
  2. 2. How can you check whether a step folds against a SQL database?

    If View Native Query is enabled on a step, the step folds and a window shows the generated query. A greyed-out option means folding stopped at or before that step.
  3. 3. Where should a non-foldable step such as adding an index column normally go?

    Folding stops at the first step that cannot fold. Putting it last keeps the earlier filters and column removals inside the source query.
  4. 4. Which parameters does incremental refresh need?

    Power BI fills in the date/time parameters RangeStart and RangeEnd during refresh, and the table's filter uses them to select each partition's rows.
  5. 5. Do CSV files fold?

    Folding needs a source that can run queries. Plain files cannot, so Power Query processes them locally.

Summary

  • Query folding turns your steps into one native query that the source runs; only the result comes back.
  • Folding stops at the first step that cannot be translated; filter and trim early, break late.
  • Use View Native Query on a step to test whether it folds. Files such as CSV never fold.
  • Incremental refresh uses RangeStart and RangeEnd filters, which must fold, and keeps old partitions while reloading recent ones.
  • Disable load on helper queries, remove unused columns and keep the model small.
  • The Python stand-ins simulate transfer counts and partitions; real numbers depend on your source.

Next, begin the modelling chapter with Star Schema: Facts and Dimensions.