Power Query Performance Playbook (Power BI & Excel)

Want faster refreshes and snappier previews? Here’s a practical, no-nonsense guide to making Power Query load much quicker using only generic patterns and examples.

Quick Wins

• Fold early, fold often: Filter and select columns as the first steps.

• Prune columns: Load only what you use.

• Reference, don’t duplicate: Reuse staged queries.

• Avoid row-by-row custom columns: Prefer joins, grouping, and native operations.

• Use buffering sparingly: Only when you know why.

• Parameterize filters: Push ranges and “Top N” upstream.

• Type once, in one place: And keep it foldable.

• Turn off loads for staging queries.

  
  

1) Structure Your Queries (Staging → Transform → Output)

Create three layers:

• Staging: Raw connections, minimal shaping (filters + columns). Disable load.

• Transform: Joins, calculated columns, grouping. Disable load if it feeds others.

• Output: Final tables that load to model/worksheet.

Why it’s fast: fewer queries hit the source multiple times; transformations share the same staged result.

2) Fold Early, Fold Often

Query folding pushes work (filtering/aggregation) to the source. Keep early steps foldable:

• Good early steps: Table.SelectRows, Table.SelectColumns, Table.RenameColumns, Table.TransformColumnTypes.

• Folding killers (use later if needed): Add Index, complex custom columns, certain text/regex ops, row-by-row functions.

Generic example — foldable first steps

3) Reduce Data Early (Columns & Rows)

3.1 Column pruning

Only load columns you truly need.

3.2 Parameterized filtering (works for any foldable source)

Create RangeStart and RangeEnd as type date (or datetime) parameters and filter with them.

Benefits: fast previews during dev (use small windows) and a direct path to incremental refresh patterns later.

4) Prefer Joins & Groups over Row-by-Row

Row-by-row custom columns (each with heavy logic) are slow. Instead, join lookup tables or group then merge.

5) Buffering: Powerful… and Dangerous

Table.Buffer (or List.Buffer) materializes data in memory to prevent re-evaluation. Use it:

• Before a non-foldable Sort that is reused multiple times.

• When a function gets called repeatedly on the same small table.

Avoid buffering large tables; it can slow things down or exhaust memory.

6) Set Data Types Once (Foldable If Possible)

Multiple “Changed Type” steps slow refresh and can break folding. Do it once, early, and specify culture when needed.

7) Combine Files the Smart Way

When ingesting a folder of files:

• Build a sample query once (remove auto-generated, non-essential steps).

• Extract/transform columns in the sample; avoid row-by-row operations inside Add Custom for each file.

• Keep the outer loop light (just invoke the sample function).

  
  

8) Diagnostics & Observability

• Step folding indicator (Power BI Desktop): green means folding; gray means no folding.

• Query Diagnostics (Power Query): find slow steps/sources.

• Table.Profile to understand cardinality and decide on indexing or aggregations upstream.

9) “Anti-Patterns” Table (and Fixes)

Anti-pattern	Symptom	Do Instead
Add Index before filter	Huge scans, slow	Filter first (fold), then index if needed
Many “Changed Type” steps	Extra refresh time	One type step, foldable
Custom column per row with heavy logic	CPU spikes, slow	Use joins/grouping/native ops
Duplicating source queries	Multiple hits to source	Reference a staged query
Buffer everything	Memory bloat	Buffer only small, reused results
Expanding massive nested tables early	Big intermediate size	Prune columns first, then expand

10) A Reusable Staging Pattern (Generic)

Optimizing data processing with query folding: key techniques for efficient staging, transformation, and output to achieve faster refresh rates.

Extra & Hidden Tips

• Rename early (folds) to avoid repeated column lookups later.

• Avoid sorting unless required; sorts often break folding or force full scans.

• Remove errors as late as possible if it breaks folding; otherwise filter erroneous rows upstream at the source.

• Keep previews small (narrow parameter ranges) during development.

• One output = one purpose: Don’t overload a single query with multiple outputs—split via references.