Workflows

Query folding in Power Query: the checklist that keeps refresh cheap

By Adrien Yvin ·

power-queryquery-foldingdataflowrefreshpower-bi

A dataflow that “worked on 200k rows” becomes a 90-minute refresh at 40M because query folding died on step 4 and every later step runs locally.

Prove folding before you ship

In Power Query:

  1. Right-click a step → View Native Query (or Query Diagnostics)
  2. If the option is grey, folding broke at or before that step
  3. Fix that step. Do not add more transforms “for now”

Dataflow Gen2: same idea — inspect the generated query / query plan. If mashup is reading the full table into the engine, you are not folding.

Steps that usually fold

  • Remove columns, rename, simple type changes
  • Filter on column vs literal or parameter (RangeStart / RangeEnd)
  • Select / expand of a SQL-sourced navigation
  • Group by, when the source can GROUP BY

Steps that often break folding

Step Typical outcome
Custom column with arbitrary M Break
Merge of two queries that do not fold Break, plus a local join
Fuzzy merge Break, always treat as expensive
Index column Often break
Some Table.TransformColumnTypes culture tricks Break
Python/R scripts Break

If you need a custom column, push it to SQL or Spark in Silver. Power Query is a staging tool, not a transformation dump.

Incremental refresh depends on folding

RangeStart / RangeEnd filters that do not fold become full scans with a polite filter at the end. Incremental refresh settings will not save you.

PR checklist item

“Native Query captured for the last step before load.” Screenshot or text in the PR. No screenshot, no merge, for anything that hits prod SQL.

Folding broken on a dataflow that used to finish before 7am? Book a 30-minute call.