Query folding in Power Query

When your source is a capable database (SQL Server, a warehouse, an OData feed), Power Query tries to translate your Applied Steps back into the source's native language, e.g. a single SELECT … WHERE … GROUP BY. The source then does the filtering, grouping and joining, and returns only the small result. That's query folding. It's the difference between a query that returns 200 summarised rows in a second and one that drags 5 million detail rows across the network to filter them in Excel. From first principles, the closer computation happens to the data, the less data has to move, and the faster everything is.

To check whether a step folds, right-click it in Applied Steps and look for View Native Query. If it's available and shows SQL, everything up to that step folded. Folding breaks at the first step the source can't express. Many custom-column and complex text operations fall into that group. Once broken, it stays broken for the rest of the query. So do the foldable, data-reducing work first. Filter rows, remove columns, run Group By, all before any step that breaks folding, so the source shrinks the data before Excel touches it. File sources (CSV, a folder, a workbook) don't fold at all, because there's no engine to push work to. The concept still governs how you order steps, though. It becomes decisive the moment a database is involved.

Why does refreshing a query matter?

Refreshing matters because a query is a pipeline, and its output is only a snapshot. What's on your sheet is frozen at the last refresh. Edit the source CSV, add a file to the folder or fix a typo in the workbook, and nothing changes until you refresh and the query re-runs its recipe against the current source. That's the tool's greatest strength, and also its most common footgun. People edit the source, glance at the stale output and trust a number that's a month old.

• To refresh everything, use Data ▸ Refresh All, or Ctrl+Alt+F5.
• To refresh one query, right-click it in the Queries & Connections pane ▸ Refresh, or use the refresh icon beside it. Data ▸ Queries & Connections shows the pane if it's hidden.
• To refresh automatically, right-click the query ▸ Properties ▸ tick "Refresh data when opening the file" and/or "Refresh every N minutes", for connections that should stay live.

Where should a query load its result?

When the recipe is done, choose its destination deliberately. Home ▸ Close & Load loads to a new worksheet table by default, but you almost always want Home ▸ Close & Load ▸ Close & Load To…. That opens the Import Data dialog, with the choices that actually matter:

• Table puts the result on a worksheet as a Table. That's fine for a few thousand rows you want to see.
• Only Create Connection loads nothing to any sheet. The query exists as a connection, with its results held in memory. It's the right choice for a large query, or for a staging query that only feeds other queries. The folder-combine helpers are staging queries, and so is an intermediate you'll append or merge. Loading a 2-million-row query to a sheet is slow, bloats the file and hits the ~1,048,576-row worksheet limit. Connection-only sidesteps all of it.
• Add this data to the Data Model sends the result to Power Pivot for PivotTables and DAX, which is where the Data Model work later in the track picks up. Combine it with "Only Create Connection" to keep it off the grid entirely.

For anything non-trivial, the professional default is Only Create Connection (+ Data Model). Load to a sheet only what a human needs to read. Loading a huge query straight to a worksheet is the performance mistake that makes people wrongly conclude "Power Query is slow."