Optimization: faster, cheaper Power BI refreshes - incremental refresh, query folding and parallelism

#1. Measure the refresh

• Refresh history: duration trend and failures.
• For detail, run the refresh while tracing with SQL Server Profiler / the Fabric workspace monitoring / Log Analytics integration, or check per-table/partition timings in refresh history (enhanced refresh).
• Source side: query durations during refresh (are queries folding? are they full scans?).

#2. Query folding

Folding pushes Power Query steps back to the source as one query. Without it, Power Query pulls everything and transforms locally.

• In Power Query, right-click a step → View Native Query. If it's greyed out, folding broke at or before that step.
• Keep folding-friendly steps first: filter rows, remove columns, joins on source tables.
• Put non-foldable steps (custom functions, some text operations, index columns) last, or move them to SQL views upstream.
• For SQL sources, a view or stored logic in the database is often simpler than complex M.

#3. Incremental refresh

Parameters:  RangeStart, RangeEnd (DateTime)
Filter:      [OrderDate] >= RangeStart and [OrderDate] < RangeEnd   (must fold!)
Policy:      Archive 5 years, refresh last 10 days
Optional:    Detect data changes (max of LastModified column) and/or get latest data in real time (hybrid tables, Premium)

Only recent partitions refresh. History partitions stay. Requirements: the filter must fold to the source; date column in the source with an index helps.

For corrections in old periods, refresh specific partitions via XMLA/TMSL or the enhanced refresh API instead of a full refresh.

#4. Parallelism and the enhanced refresh API

POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/refreshes
{
  "type": "full",
  "commitMode": "transactional",
  "maxParallelism": 6,
  "retryCount": 1,
  "objects": [ { "table": "Sales", "partition": "2026Q4" }, { "table": "Customer" } ]
}

• maxParallelism controls how many tables/partitions refresh at once. Higher can be faster if the source and gateway can take it; lower protects a fragile source.
• Refresh only the objects that changed (dimensions often change rarely).
• Orchestrate refreshes after upstream pipelines finish (ADF/Fabric pipeline activity or API call) instead of fixed times that guess.

#5. Gateway

• Cluster with 2+ members for HA and load (see the gateway HA post).
• Size CPU/memory for concurrent refreshes; watch gateway performance logs.
• Place gateways close to the data sources (network latency multiplies on chatty refreshes).
• Keep the gateway version current.

#6. Scheduling

• Spread refreshes across the hour/day instead of everything at 06:00.
• Avoid refreshing during peak interactive usage on shared capacities. Background operations consume CU too.
• Reduce frequency where the business doesn't need it (hourly vs every 15 minutes).

#7. Reduce the data

Smaller models refresh faster: remove unused columns and history (see the semantic model performance post). The fastest refresh is the one that loads less.

Warning

Changing an incremental refresh policy or republishing the model from Desktop can trigger a full reload of all partitions. Plan model deployments (ALM Toolkit / Tabular Editor / deployment pipelines) to avoid unplanned full refreshes on large models.

#Checklist

• Refresh durations and source queries measured
• Query folding verified; non-foldable steps moved last or upstream
• Incremental refresh with folding filter
• Enhanced refresh API with parallelism and partial refreshes
• Gateway clustered, sized and close to sources
• Schedules spread and triggered by upstream completion