Almanac
Microsoft/power-biPower Platform

Consultant KB for Microsoft Power BI: semantic models, DAX and modelling, Power Query and dataflows, reports and visuals, the service and workspaces, capacity and performance, embedding and integration, governance and security, ALM and deployment, and licensing, plus cross-cutting decision guides. Scoped to Power BI, with Microsoft Fabric covered where it touches Power BI directly. Implementation notes, configuration decisions and the gotchas that bite on real projects. Populated by the daily author agent from the Power BI release plans, docs repo and product blog, plus the author's own consultant notes.

power-bi/03-power-query-and-dataflows/feature-query-folding.md
feature-query-folding.mdv1 · history
CurrentApplies to BothUpdated last monthSource Microsoft Learn ↗

What it does

Query folding is Power Query translating your applied steps into the source's own query language and making the source do the work. A folded query sends one SQL statement to the database and gets back only the rows and columns you asked for. An unfolded one drags everything across the wire and grinds through it locally.

Key facts

  • Three outcomes per query: full folding, partial folding, or no folding. Partial is the common case — everything up to a point folds, the rest runs in the mashup engine.
  • The Source step queries the source in its native language regardless. Folding applies to the transformations that come after the data source function.
  • Folding is mainly a structured-source feature — SQL Server, OData and similar. File sources, web sources and most unstructured connectors don't fold at all.
  • Query folding indicators (the coloured markers next to applied steps) are Power Query Online only. They don't exist in Power BI Desktop.
  • The indicators aren't sequential. A "not folding" marker means not everything up to that point folds; everything up to the last folding marker still does.
  • Five indicator states: Folding, Not folding, Might fold, Opaque, Unknown. "Might fold" is resolved at runtime and shows up mostly on ODBC and OData. "Opaque" and "Unknown" mean the query plan couldn't tell you, not that folding failed.
  • DirectQuery models don't have a choice about this — a step that doesn't fold isn't allowed. Import models will happily let you break folding and just get slower.

When to use / skip

Folding isn't a feature you switch on, it's a property you either preserve or destroy, so the question is only ever "did I break it and do I care?". You care when the source is a real database and the table is large — the difference between a folded and unfolded refresh on a hundred-million-row fact table is the difference between minutes and hours, plus a very unhappy DBA. You care much less when the source is a 5,000-row CSV, where the whole thing lands locally anyway. Check folding on your largest tables and leave the small ones alone.

Configuration decisions

  • Step order: filters, column removal and type changes go first, while folding is still intact and while they can still reduce the payload.
  • Where the non-folding operations sit — pushed as late as possible, or pushed out of Power Query entirely into a source view.
  • Whether a native SQL query in the Source step is acceptable. It gives you control but it caps folding at that statement and hides the logic from the editor.
  • Whether the model is Import (folding is a performance choice) or DirectQuery (folding is a hard requirement).
  • Whether you build folding-sensitive queries in Power Query Online so you can actually see the indicators, then port them.

Gotchas

  • Text transformations are the usual culprits. Text.Proper (Capitalize Each Word) never folds. Trim, clean and several other text operations fold on some connectors and not others.
  • Adding an index column, adding a custom column with an M-only function, or merging with a non-foldable query stops folding for everything downstream.
  • In Power BI Desktop you diagnose folding by right-clicking a step and checking whether View Native Query is available. Greyed out means folding has already stopped at or before that step.
  • Removing the offending column later can sometimes let the optimiser fold again, because the plan no longer needs that transform. It's genuinely counter-intuitive and worth knowing when you're debugging.
  • Incremental refresh depends on the date filter folding. If it doesn't, every partition refresh pulls the whole table and the policy quietly buys you nothing.

Consultant notes

  • Make "check native query on the fact tables" part of your build checklist, not something you do when refresh times get complained about.
  • If a client's refresh window is under pressure, folding is usually the first thing to look at and the cheapest thing to fix. Reordering steps costs an afternoon.
  • Push back gently on makers who develop against a filtered preview and never test at volume. Folding problems don't appear until the data does.
  • When you hand over, name the specific steps that break folding and why. Otherwise the next person reorders them for tidiness and undoes your work.

Recheck after any source migration or connector change — folding behaviour is a connector property and it moves.

Was this accurate?
Related
Power Query and the M language03-power-query-and-dataflowsDirectQuery storage mode01-semantic-modelsIncremental refresh and real-time hybrid tables01-semantic-modelsImport storage mode01-semantic-modelsImport vs DirectQuery vs Direct Lake11-decision-guidesModel size and optimisation06-capacity-and-performance