Performance, Query Folding and Refresh Architecture

Introduction

A Power BI solution is only as useful as its ability to deliver trustworthy data within an acceptable time and cost. A report that takes several minutes to open, fails during scheduled refresh, or consumes excessive gateway and source-system capacity is not a successful business-intelligence solution. Performance is therefore not merely a technical concern: it affects adoption, operational cost, decision speed, and confidence in analytics.

This chapter examines performance from the perspective of Power Query and the Power BI refresh architecture. It focuses on the order of transformations, query folding, data types, source filtering, buffering, gateways, scheduled refresh, and incremental refresh. The central design principle is to push appropriate work toward the source system, reduce the volume of data transported and processed, and refresh only what is necessary while preserving correctness.

Learning objectives

By the end of this chapter, you should be able to:

• explain how Power Query evaluates transformations and why step order matters;
• distinguish query folding from local evaluation;
• identify transformations that commonly fold and those that commonly prevent folding;
• optimize source selection, filtering, data types, joins, and buffering;
• diagnose performance with query diagnostics and source-side tools;
• distinguish import, DirectQuery, gateway, scheduled-refresh, and incremental-refresh concerns;
• design a refresh architecture that balances freshness, cost, reliability, and governance.

1. A performance model for Power Query

Power Query uses the M language to describe a sequence of transformations. A query is conceptually a directed chain (or graph) of expressions. The interface displays applied steps, but the engine does not necessarily materialize a complete intermediate table after every step. Evaluation is generally lazy: an expression is evaluated when its result is required.

A useful conceptual model is:

Source → Select required columns → Filter rows → Join/aggregate → Type/shape → Load
             (reduce data early and push operations to the source where possible)

Actual performance depends on several components:

• source-system execution time;
• network transfer volume and latency;
• Power Query Mashup Engine processing time;
• memory consumption and temporary storage;
• destination model processing time;
• gateway capacity and concurrency;
• refresh scheduling and contention.

A simple planning approximation is:

Here, each term is an elapsed-time component, and the approximation assumes that the components are sufficiently separable for planning. In practice, some activities overlap, so this is not an exact physical law. The equation is useful because it shows why improving only M-code may not solve a source bottleneck, and why reducing transferred rows can improve both transfer and local processing.

Likewise, a rough data-transfer relationship is:

where N_rows is the number of transferred rows and B_row is average transferred bytes per row. Compression, protocol overhead, selected columns, and data types affect the actual amount, so this is an engineering estimate rather than a precise billing formula.

Step order and data reduction

The most generally useful ordering principle is to reduce data as early as possible:

1. connect directly to the appropriate table, view, file, or API endpoint;
2. select only required columns;
3. filter rows using source-compatible predicates;
4. perform source-side aggregations or joins where appropriate;
5. apply transformations that cannot fold;
6. set final data types and load only the required result.

This is not an absolute rule. A filter may depend on a calculated value created later, and an early transformation may be necessary to make a later filter fold. Therefore, the correct goal is not simply “move every step upward”; it is “reduce data early without breaking correctness or folding.”

For example, filtering a fact table to the last five years before expanding a large dimension can substantially reduce join input. However, if the filter is applied to a non-folding custom function before the filter, the source may still send the entire table to Power Query. The visible order alone is not enough; the folding result must be tested.

2. Query folding

Query folding is the process by which Power Query translates compatible M transformations into operations executed by the source system, such as SQL predicates, projections, joins, and aggregations. The source does not execute M itself; rather, the connector produces an equivalent native request when it can do so.

For a relational source, a query such as:

let
    Source = Sql.Database("SalesServer", "SalesDW"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    Selected = Table.SelectColumns(Sales, {"SaleDate", "CustomerKey", "Amount"}),
    Filtered = Table.SelectRows(Selected, each [SaleDate] >= #date(2024, 1, 1))
in
    Filtered

will often fold into a SQL request resembling a projection and a WHERE clause. The exact generated SQL is connector-dependent and should not be assumed from the M code alone.

Folding is usually beneficial because database engines can use indexes, statistics, parallel execution, partition elimination, and optimized storage. It also reduces data sent over the network. Folding is not inherently required, however. A small CSV file, a local lookup table, or a source with no folding capability may perform perfectly well without it.

Commonly foldable operations

Depending on the connector and source, these often fold:

• selecting and removing columns;
• filtering rows with simple predicates;
• changing compatible data types;
• sorting;
• grouping and standard aggregation;
• joining compatible tables;
• replacing values with simple expressions;
• taking a fixed number of rows.

These operations are not guaranteed to fold in every connector. Data type conversions, null semantics, text comparisons, privacy settings, and connector implementation can change the result.

Operations that frequently stop or limit folding

Common causes include:

• invoking a custom row-by-row M function;
• using complex list or record operations;
• adding an index that the source cannot express equivalently;
• converting a table to a list and reconstructing it;
• combining data from incompatible sources;
• using an unsupported connector function;
• referencing a prior step whose result is already locally materialized;
• applying a transformation whose semantics cannot be represented by the source.

A non-folding step is not automatically incorrect. It becomes a performance concern when it occurs before a large-volume filter or when it causes the entire source dataset to be imported locally.

Folding boundaries

Once Power Query must evaluate a transformation locally, later steps may also be local, although some connectors and query plans can push compatible operations beyond apparent boundaries. Treat the point where folding stops as a diagnostic hypothesis, not as an assumption. Test the actual query.

Useful verification techniques include:

• View Native Query, where supported, to inspect the source request;
• query diagnostics to observe evaluation time, requests, and data movement;
• source-side query logs, execution plans, and monitoring tools;
• comparing refresh duration and transferred volume before and after a change.

“View Native Query” is not available for every connector or every step. Its absence does not prove that no optimization occurs, and its availability does not guarantee that the entire workload is optimal.

3. Data types, filtering, and source design

Data types

Correct data types are essential for both performance and correctness. A date should be a date, a monetary value should use an appropriate fixed decimal type, and identifiers that may contain leading zeros should usually remain text. Incorrect typing can cause failed joins, inaccurate comparisons, unexpected nulls, and inefficient storage.

Type operations should be considered in relation to folding. A conversion from a source numeric column to a compatible numeric type may fold. A conversion involving locale-dependent text parsing may require local evaluation. For example, parsing 31/12/2024 depends on culture and should be handled deliberately rather than relying on the environment.

Use the smallest semantically safe type, but do not sacrifice meaning. A customer identifier stored as an integer may save space, whereas an account code such as 00127 must remain text. Currency should not be converted to binary floating-point merely to save a small amount of storage when exact decimal semantics are required.

Early filtering

Filters should be:

• selective enough to reduce volume;
• expressed in a form the connector can translate;
• applied before expensive local transformations;
• based on a stable business rule;
• tested for time-zone and boundary correctness.

Date-time filtering deserves special attention. A “day” in the business context may refer to local time, whereas the source may store UTC. A refresh that uses a local clock against UTC values can omit or duplicate records around midnight or daylight-saving transitions. Define the time-zone rule and apply it consistently.

Avoid filtering a table with a list generated by an expensive local process when a source-side join or parameterized predicate is possible. For large lists, a staging table or source view may be more efficient than constructing an in clause or repeatedly calling an API.

Source-side objects

A source view or stored procedure can centralize joins, security, and business logic, but it is not automatically faster. A view may hide an inefficient execution plan, and a stored procedure may not support folding of later Power Query steps. Collaborate with database administrators to inspect indexes, partitioning, statistics, and execution plans.

For files and APIs, performance depends on endpoint design. Prefer APIs that support server-side date filters, pagination, field selection, and incremental extraction. Avoid downloading a complete historical JSON response when the endpoint can return only changed records.

4. Joins, references, functions, and buffering

Joins and expansion

Join keys must have matching data types and compatible values. Before joining, remove unused columns and, where appropriate, deduplicate the lookup side. A many-to-many join can multiply rows and create both a correctness error and a performance problem.

Expanding a nested table introduces columns from the related result. Expand only the required fields. If a merge is performed repeatedly inside a custom function, the source may be queried repeatedly. A more efficient design may stage the lookup once, use a source-side join, or create a controlled reference query.

Duplicate queries and references

A referenced query expresses dependency, not necessarily guaranteed physical caching. If several downstream queries independently evaluate a large upstream query, the source may be accessed repeatedly. Where appropriate, consolidate extraction into a staging query and create carefully designed transformations from it. Monitor actual requests rather than assuming that a reference always means one source read.

Table.Buffer and List.Buffer

Buffering forces a value to be evaluated and held in memory for the current evaluation. It can help when a small, stable lookup table is repeatedly re-evaluated or when isolation from subsequent source evaluation is required. It can also destroy folding, increase memory pressure, and slow refresh by materializing data unnecessarily.

Buffering is therefore a targeted tool, not a general performance switch. Do not insert Table.Buffer at the beginning of every query. First establish a measured problem, confirm that repeated evaluation is occurring, estimate the lookup size, and compare alternatives. Buffering is also evaluation-scoped; it should not be interpreted as a permanent cache across refreshes.

5. Measuring and diagnosing performance

Optimization should follow measurement. A practical diagnostic sequence is:

1. record baseline refresh duration, source duration, rows, columns, and failure rate;
2. identify the largest queries and the slowest steps;
3. check whether filters and projections fold;
4. inspect source execution plans and indexes;
5. test one change at a time;
6. compare correctness as well as elapsed time;
7. document the final design and its assumptions.

Query Diagnostics can reveal time spent in evaluation, data-source requests, and other activities. The Power Query and Power BI interfaces evolve, so labels and available diagnostics can vary by product version. Source-side monitoring remains essential because a fast-looking client step may conceal an expensive database query.

Useful performance indicators include:

Averages alone can conceal problems. Record peak duration and variability, especially when refreshes compete with transactional workloads.

6. Refresh architecture

Import, DirectQuery, and composite designs

In Import mode, data is extracted during refresh and stored in the Power BI model. Report queries are usually fast, but freshness depends on refresh completion and model size. DirectQuery leaves data in the source and sends queries at report interaction time. It can provide more current data but makes source performance, network latency, concurrency, and security central concerns. Composite models combine storage modes and require careful treatment of relationships and aggregation behavior.

A decision should consider:

• required freshness;
• source workload tolerance;
• report-interaction latency;
• data volume and history;
• gateway and licensing capacity;
• security and regulatory requirements;
• operational support capability.

Gateway architecture

An on-premises data gateway provides a secure bridge between Power BI and sources that are not directly reachable from the Power BI service. The gateway does not replace source optimization. It adds a potential bottleneck involving network path, credentials, encryption, CPU, memory, and concurrent refreshes.

Professional practices include:

• install the standard (enterprise) gateway for shared production workloads;
• place it close to the source and on a reliable network path;
• use a service account or managed operational identity according to organizational policy;
• keep gateway software current;
• monitor resource use and refresh queues;
• use a gateway cluster for availability and planned maintenance;
• ensure that server names, database names, and connection definitions match the dataset configuration;
• separate development and production credentials and environments.

A gateway cluster improves availability, but it does not automatically multiply performance. Capacity, source speed, and concurrency still determine throughput.

Scheduled refresh

Scheduled refresh is appropriate when data can be updated at defined intervals. A schedule should account for source load, gateway capacity, dependencies, and business deadlines. Avoid scheduling many large refreshes at the same time. If a dataset depends on another process, schedule it after the upstream process has completed and allow for delay.

Refresh failures should be treated as operational events. Record error details, identify transient versus structural failures, and define escalation and retry procedures. Repeated retries cannot correct a broken credential, changed schema, or invalid transformation.

Incremental refresh

Incremental refresh partitions a table by a date/time column using a historical policy and a refresh policy. In Power BI implementations, RangeStart and RangeEnd parameters are commonly used to filter the source query. During service refresh, partitions can be created and refreshed according to the policy.

A typical design retains several years of history but refreshes only recent months or days. The filter must be applied as early as possible and should fold to the source. The boundaries must be designed carefully to avoid overlap. A common half-open interval is:

The interval includes the lower boundary and excludes the upper boundary. If adjacent partitions use this convention, a record at the boundary belongs to exactly one interval, assuming the source values and parameter types are consistent.

Incremental refresh reduces repeated work but does not eliminate all costs. The initial publication may be expensive; recent partitions may be large; schema changes can affect all partitions; and a late-arriving historical correction may require a wider refresh window. Change detection can reduce processing when a source modification timestamp is reliable, but it depends on the quality and completeness of that timestamp.

7. Balancing performance, cost, and freshness

These objectives often conflict. More frequent refresh improves freshness but may increase source load, gateway use, capacity consumption, and failure exposure. Import mode may produce excellent report performance but requires scheduled processing. DirectQuery may reduce refresh work but transfer latency and source concurrency to report users.

A useful decision framework asks:

1. What is the maximum acceptable data age?
2. What is the maximum acceptable report-response time?
3. Which source workloads are tolerable during business hours?
4. Which data must be historical, and which data must be current?
5. What is the cost of stale, missing, or incorrect data?
6. What monitoring and support can the organization sustain?

The correct architecture is often mixed: imported historical data, a frequently refreshed recent partition, aggregated tables, or a composite design for carefully selected operational use cases. Technical optimization must remain subordinate to business semantics. A fast model that omits late transactions is not an improvement.

8. Applied case: regional retail sales model

The following is an illustrative case.

Background

A retailer maintains 180 million sales rows in a cloud SQL warehouse and publishes a Power BI model for regional managers. The original import refresh took 95 minutes, occasionally failed at the gateway, and placed substantial load on the warehouse. Managers required data no older than 60 minutes for current-day sales, while historical analysis changed relatively infrequently.

Problem analysis

Investigation found that the query selected all columns, performed a custom M text-cleaning function before filtering dates, expanded a customer table with unnecessary attributes, and used a local list to filter regions. The date filter therefore did not reach the source early. Gateway monitoring showed high memory use during local evaluation. The model also refreshed the complete five-year history every time.

Approach

The redesign:

• created a source view exposing only required sales columns;
• standardized the sales timestamp and keys in the warehouse;
• applied the date predicate immediately after navigation;
• moved region validation to a source-side dimension join;
• removed unused expansion columns;
• replaced the row-by-row cleaning function with a source expression;
• configured incremental refresh with five years retained and a seven-day refresh window;
• added a separate current-day operational table refreshed hourly;
• scheduled the historical model outside the warehouse’s peak transaction period;
• monitored source execution plans, gateway memory, refresh duration, and row counts.

Outcome

The historical refresh fell to 18 minutes after the initial partitioning process, while the current-day table completed in approximately 6 minutes. Gateway memory became stable because the large local intermediate table was eliminated. The organization accepted a slightly more complex model in exchange for meeting the freshness target without forcing all five years of data through every refresh.

Learning points

The largest improvement did not come from a single M-language trick. It came from aligning source design, folding, partition policy, gateway capacity, and business freshness requirements. The case also illustrates that a separate current-data pathway can be preferable to repeatedly refreshing a complete historical model.

9. Limitations, governance, and contemporary practice

Performance changes can alter results. Removing a column may remove a required audit attribute; changing time zones may change daily totals; folding a text comparison may use source collation rules different from local M semantics; and incremental refresh may hide late corrections outside the refresh window.

Governance controls should include:

• documented data owners and refresh-service owners;
• source-to-model lineage;
• credentials managed through approved identity controls;
• least-privilege source access;
• protection of personal and confidential data in gateway logs and diagnostics;
• validation of row counts, dates, totals, and null rates after refresh;
• change management for schema, gateway, and policy changes;
• retention and deletion rules consistent with legal requirements.

AI-assisted coding and automated optimization suggestions can accelerate development, but generated M expressions must be reviewed for folding, privacy, data leakage, type correctness, and reproducibility. An AI tool cannot establish that a refresh result is complete or that a source-side query is safe. Human review, testing, and lineage remain necessary.

Best-practice checklist

• Start with a performance baseline.
• Select only required columns and rows.
• Filter early, but verify that the filter folds.
• Preserve semantically correct data types.
• Keep expensive local operations after data reduction.
• Inspect joins for cardinality and duplicate keys.
• Use buffering only after measurement demonstrates a justified case.
• Monitor source, gateway, mashup, and model components separately.
• Use incremental refresh for large time-partitioned tables where the source supports reliable filtering.
• Design refresh schedules around dependencies and source capacity.
• Test boundary dates, time zones, late-arriving records, and schema changes.
• Treat correctness, security, and maintainability as performance requirements rather than afterthoughts.

Chapter Summary

Power Query performance depends on the interaction of source execution, network transfer, local transformation, model processing, gateways, and refresh scheduling. Query folding allows compatible transformations to execute at the source, often reducing transferred data and local work. Selecting columns, filtering rows, preserving appropriate data types, and reducing join inputs are fundamental optimization practices.

Folding must be measured rather than assumed. View Native Query, query diagnostics, source execution plans, and gateway monitoring provide complementary evidence. Buffering can solve narrowly defined repeated-evaluation problems but can also consume memory and prevent folding. Import, DirectQuery, composite, scheduled, and incremental-refresh designs provide different balances of latency, freshness, source load, and operational complexity.

A robust architecture aligns technical behavior with business requirements. It uses reliable time boundaries, carefully designed partitions, secure and monitored gateways, validation controls, and documented ownership. The best solution is not the one with the most transformations or the shortest isolated query; it is the one that delivers correct, governed data at an acceptable cost and freshness level.

Key Terms

Query folding: Translation of compatible Power Query operations into source-system operations.

Mashup Engine: The Power Query runtime that evaluates M expressions and transformations.

Lazy evaluation: Evaluation of an expression when its result is required rather than immediately at definition.

Folding boundary: A point after which one or more transformations are evaluated locally rather than pushed to the source.

Source predicate: A condition used to select rows at the data source.

Projection: Selection of required columns from a source relation.

Gateway: A service that securely connects the Power BI service to reachable organizational data sources.

Gateway cluster: Multiple gateway members configured to provide availability and workload distribution.

Import mode: Storage mode in which data is loaded into the Power BI model during refresh.

DirectQuery: Storage mode in which report queries are sent to the source at interaction time.

Composite model: A model combining storage modes or imported and DirectQuery data.

Scheduled refresh: Automated model refresh executed at configured times or intervals.

Incremental refresh: Refresh strategy that processes selected recent partitions instead of the complete history.

Partition: A separately managed portion of a table, often defined by a time range.

RangeStart: A parameter representing the lower boundary of an incremental-refresh interval.

RangeEnd: A parameter representing the upper boundary of an incremental-refresh interval.

Buffering: Materializing a value in memory during an evaluation.

Data lineage: Documentation of how data moves from source through transformations to a published result.

Freshness: The age or recency of data available to report users.

Change detection: A method that identifies whether a partition or source segment requires processing.

Review Questions

5 Short Answer Questions

1. What is query folding, and why can it improve refresh performance?
2. Give two examples of transformations that commonly fold.
3. Why can Table.Buffer reduce performance rather than improve it?
4. What is the purpose of a gateway cluster?
5. Explain the difference between scheduled refresh and incremental refresh.

5 Long Answer Questions

1. Explain how step order affects data volume, folding, and local memory use in a Power Query solution.
2. Compare Import mode, DirectQuery, and composite models for a business requiring both historical analysis and near-current operational data.
3. Describe a systematic method for diagnosing a slow refresh, including the evidence that should be collected.
4. Explain how data types, time zones, and boundary conditions affect the correctness of incremental refresh.
5. Discuss the governance risks associated with optimizing a query and propose controls to manage them.

3 Analytical / Application Questions

1. A query filters a 100-million-row table only after a custom M function and a large expansion. Design a revised step order and explain how you would prove that it improved performance without changing results.
2. An organization needs data less than one hour old but cannot increase warehouse load during business hours. Evaluate two possible refresh architectures and justify your recommendation using freshness, cost, reliability, and source-load criteria.
3. Adjacent incremental-refresh partitions use inclusive lower and inclusive upper date boundaries. Analyze the risk and propose a boundary convention that prevents duplicate records, including the assumptions required for your solution.

Multiple Choice Questions with Answers

Question 1

Which statement best describes query folding?

A. Power Query permanently caches every intermediate table in memory.

B. Compatible Power Query transformations are translated into operations executed by the source.

C. All transformations are converted into DAX before loading.

D. The gateway compresses every M expression into a database index.

Correct Answer: B. Compatible Power Query transformations are translated into operations executed by the source.

Question 2

Which sequence is generally most effective for a large relational source?

A. Invoke a custom function, expand all columns, then filter rows.

B. Load all rows, buffer the table, then select columns.

C. Select required columns, apply a source-compatible filter, then perform expensive local work.

D. Convert the table to a list before selecting columns.

Correct Answer: C. Select required columns, apply a source-compatible filter, then perform expensive local work.

Question 3

What is a principal risk of using Table.Buffer unnecessarily?

A. It always deletes source records.

B. It can increase memory use and prevent useful folding.

C. It changes Import mode into DirectQuery.

D. It guarantees repeated source queries.

Correct Answer: B. It can increase memory use and prevent useful folding.

Question 4

In an incremental-refresh interval using a half-open convention, which expression is appropriate?

A. RangeStart < DateTime ≤ RangeEnd

B. RangeStart ≤ DateTime < RangeEnd

C. RangeStart < DateTime < RangeEnd, excluding both boundaries

D. RangeStart ≤ DateTime ≤ RangeEnd, including both boundaries

Correct Answer: B. RangeStart ≤ DateTime < RangeEnd

Question 5

What is the main purpose of an on-premises data gateway?

A. To replace all database indexes

B. To store the semantic model permanently

C. To provide a secure connection between the Power BI service and organizational data sources

D. To convert DirectQuery reports into spreadsheets

Correct Answer: C. To provide a secure connection between the Power BI service and organizational data sources

Question 6

Why should identifiers with leading zeros usually be stored as text?

A. Text values always fold faster than numbers.

B. Numeric conversion can remove meaningful leading zeros.

C. Text values cannot contain nulls.

D. Power BI cannot join numeric columns.

Correct Answer: B. Numeric conversion can remove meaningful leading zeros.

Question 7

Which evidence is most useful for determining whether a relational query is executing efficiently at the source?

A. Only the number of applied steps displayed in Power Query

B. The source execution plan and query-monitoring information

C. The report theme configuration

D. The number of visuals on a page alone

Correct Answer: B. The source execution plan and query-monitoring information

Question 8

A model retains five years but refreshes only the most recent seven days. Which feature is being used?

A. Incremental refresh

B. Gateway clustering

C. Data profiling

D. Column renaming

Correct Answer: A. Incremental refresh

Question 9

What is a likely consequence of applying a local, non-folding transformation before a large date filter?

A. The source necessarily receives fewer rows.

B. The entire source table may be transferred for local processing.

C. The model automatically creates partitions.

D. The gateway is bypassed in every architecture.

Correct Answer: B. The entire source table may be transferred for local processing.

Question 10

Which factor is least appropriate as the sole basis for choosing a refresh architecture?

A. Required data freshness

B. Source-system capacity

C. Security and governance requirements

D. The number of colors in the report theme

Correct Answer: D. The number of colors in the report theme