Query Design, Performance, and Troubleshooting

Introduction

Power Query is both a data-preparation language and an execution engine. In Power BI, a query is not simply a list of visible transformations: it is a sequence of expressions that may be translated, partly translated, or not translated to the source system. Effective query design therefore requires more than obtaining the correct result. A well-designed query should also minimise unnecessary data movement, preserve source-system performance, remain understandable, and fail in a controlled and diagnosable manner.

This chapter examines query folding, step ordering, early filtering, buffering, diagnostics, privacy levels, and systematic error resolution. The emphasis is practical: the aim is to help analysts build queries that are reliable in development and continue to perform acceptably during scheduled refresh and organisational use.

Learning Objectives

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

• explain query folding and identify when it is beneficial;
• design transformation steps in an efficient order;
• apply filters and column selection early without changing business meaning;
• distinguish useful buffering from unnecessary buffering;
• use query diagnostics to investigate slow or failing queries;
• explain how privacy levels affect combining data sources;
• resolve common Power Query errors through a repeatable method; and
• document performance, governance, and reliability decisions.

1. The Power Query Execution Model

A Power Query query is represented by M expressions. The Applied Steps pane provides a graphical representation, but every step is an expression whose output becomes the input to the next step. A simplified query may contain the following logical sequence:

1. connect to a source;
2. select a table or file;
3. filter rows;
4. select columns;
5. change data types;
6. join or group data; and
7. load the result.

The logical sequence does not always describe the physical work performed. If the source supports translation, Power Query can send one or more operations back to the source. If an operation cannot be translated, Power Query may retrieve data and perform that operation in its own engine. This distinction is central to performance.

For a source containing N rows, a useful conceptual model is:

Here, T_total is elapsed refresh time; T_source is source-side execution time; T_transfer is time to move data; T_engine is local Power Query processing time; and T_load is time to load the result into the destination. This is not an exact predictive equation: the terms may overlap, and caching, concurrency, and network conditions affect them. Its value is diagnostic. Early filtering often reduces transfer and local processing, whereas an indexed source filter may increase source work slightly while reducing total work substantially.

2. Query Folding

Meaning and importance

Query folding occurs when Power Query translates compatible transformations into operations understood by the data source. For a relational database, a filter may become a SQL WHERE clause, column selection may become a restricted SELECT list, and a supported join may become a database join.

Folding is usually advantageous because database engines are designed for set-based operations, indexing, statistics, parallel execution, and efficient storage access. It can reduce the volume of data transferred to Power BI and may allow existing source governance and security controls to operate close to the data.

Folding is not an all-or-nothing characteristic of an entire query. It is possible for early steps to fold and a later step to execute locally. Once a non-foldable operation is introduced, subsequent steps may or may not fold, depending on the connector and operation. The exact behaviour must be tested rather than assumed.

Viewing folding

In current Power Query environments, right-clicking a step may provide View Native Query when the connector can show a source query for that step. Its absence does not prove that no work has folded: some connectors do not expose native text even when they push operations to the source. Query diagnostics and source-side monitoring provide additional evidence.

Common factors that affect folding include:

• connector capability;
• source data type and function support;
• whether a transformation has a source equivalent;
• use of custom M functions or row-by-row logic;
• privacy isolation;
• joins between incompatible sources; and
• the point at which a value is materialised locally.

Folding-friendly and potentially non-folding operations

Filtering on a source column, selecting columns, removing duplicates, and straightforward aggregations commonly fold for relational sources. Complex custom functions, invoking web services for every row, arbitrary list manipulation, and some text or date operations may not fold. The classification is connector-specific, so it should be verified in the actual environment.

A folding-friendly design does not mean that every transformation must be forced into the source. A source may be overloaded, a database view may be more appropriate, or a local transformation may be necessary because the business rule has no source equivalent. The professional decision balances speed, maintainability, security, and operational ownership.

3. Efficient Step Ordering

Step order matters because many transformations change the amount of data subsequent steps must process. A practical default is:

1. connect to the narrowest appropriate source object;
2. filter rows using selective, source-supported conditions;
3. remove unnecessary columns;
4. apply data types and standardise names;
5. perform joins, expansions, grouping, and sorting;
6. apply complex custom logic; and
7. buffer only when a measured design requires it.

This is a heuristic, not an inflexible law. A type conversion may need to occur before a filter if the source column is text but the intended comparison is numeric. Similarly, a filter based on a calculated value cannot be moved before its calculation without changing meaning.

Early filtering

Suppose a transaction table contains ten million rows but the report needs only the current financial year. Applying the date filter at the source can reduce network transfer, memory use, and downstream processing. A filter should be placed early when:

• it is semantically safe;
• the predicate is selective enough to reduce data;
• the source can execute it efficiently; and
• the filter does not depend on later transformations.

A filter on a non-indexed column may still reduce transferred data, although source execution may be slower. Source administrators should be involved when refresh performance depends on indexes, partitions, views, or query plans.

Early column selection

Unused columns consume storage and may increase transfer and transformation cost. Selecting only required columns is especially valuable for wide tables containing descriptions, documents, or high-cardinality attributes. However, retain columns needed for later joins, validation, auditing, and error investigation until those tasks are complete.

Expensive operations

Joins, grouping, sorting, distinct operations, and custom row logic can be expensive. They should generally be performed after obvious row and column reduction. A sort performed only for visual appearance is usually unnecessary because tabular models do not preserve arbitrary row order for reporting purposes.

4. M Code and Maintainable Query Design

The following illustrative M pattern filters a source table and selects required columns:

let
    Source = Sql.Database("Server01", "SalesDW"),
    FactSales = Source{[Schema="dbo", Item="FactSales"]}[Data],
    FilteredRows = Table.SelectRows(
        FactSales,
        each [SaleDate] >= #date(2025, 1, 1)
    ),
    SelectedColumns = Table.SelectColumns(
        FilteredRows,
        {"SaleDate", "CustomerKey", "ProductKey", "NetAmount"}
    ),
    TypedColumns = Table.TransformColumnTypes(
        SelectedColumns,
        {{"SaleDate", type date}, {"NetAmount", Currency.Type}}
    )
in
    TypedColumns

This pattern is readable because each step has a meaningful name and one primary purpose. In production, the date boundary might be a parameter rather than a hard-coded value. The order should be validated: if the source column is already a date or the connector can translate the comparison, the filter may fold. If conversion is required before comparison, the type step may need to move earlier.

Good design practices include:

• use descriptive step names;
• avoid unnecessary repeated conversions;
• preserve a clean staging query when several downstream queries use the same source;
• separate connection, shaping, and business-rule stages where practical;
• use parameters for environments, dates, and controlled settings;
• avoid embedding credentials or secrets in M code; and
• document non-obvious decisions, especially deliberate non-folding steps.

5. Buffering Awareness

Table.Buffer, List.Buffer, and Binary.Buffer request that a value be materialised in memory. Buffering can be useful when a stable value is repeatedly evaluated, or when the analyst has confirmed that repeated source evaluation is the bottleneck. It can also establish a local snapshot for a particular evaluation.

Buffering is not a general performance accelerator. It may:

• consume substantial memory;
• prevent subsequent operations from folding;
• force data to be read earlier than necessary;
• increase refresh time; or
• make a query more fragile when source data is large.

For example, buffering a million-row table before a filter is usually poor design: the entire table may be loaded locally before the filter can reduce it. Buffering a small lookup table used repeatedly may be reasonable, but this should be tested with diagnostics.

A useful rule is: do not buffer to guess; buffer to address a demonstrated evaluation problem. Consider whether the real issue is a repeated function call, an inefficient join, an unstable source, or a missing source-side optimisation.

6. Diagnostics and Performance Investigation

Performance improvement should be evidence-based. Query Diagnostics can help identify time spent in evaluation, data source access, and other activities. Source-side tools, such as database query plans or monitoring dashboards, may reveal missing indexes, scans, blocking, or resource contention.

A disciplined investigation follows this sequence:

1. define the symptom, such as a 30-minute refresh or intermittent timeout;
2. reproduce it with a controlled refresh;
3. record baseline duration and data volumes;
4. isolate the slow query or step;
5. inspect folding at successive steps;
6. compare source time with local processing time;
7. change one design element;
8. rerun the same test; and
9. document the result and remaining limitation.

Do not infer performance from the preview alone. Previews may use limited data, cached results, or different evaluation behaviour from a full refresh. Test representative volumes and, where possible, the actual refresh environment.

Measuring improvement

A simple percentage reduction in elapsed time can be calculated as:

T_before and T_after are measured durations in the same units, such as minutes. The formula assumes comparable workload, source state, and environment. For example, reducing refresh time from 20 minutes to 12 minutes gives ((20 − 12) ÷ 20) × 100 = 40%. This result should not be attributed solely to a query change if source load or network conditions also changed.

7. Privacy Levels and the Firewall

Power Query privacy levels communicate how data from a source may be used when sources are combined. The principal classifications are:

Privacy settings are governance controls, not performance labels. When Power Query combines sources with different privacy boundaries, the Formula.Firewall may block a query or isolate evaluations to prevent unintended data leakage. A query that works after disabling privacy checks may be insecure or unsuitable for deployment.

Correct responses include reviewing source classifications, separating staging queries, using a governed data platform, and designing a privacy-compatible combination. Disabling privacy protections should not be treated as the default troubleshooting step. Administrators must consider whether a source contains personal data, confidential commercial information, credentials, or regulated records.

Privacy levels do not replace access control. Database permissions, workspace security, row-level security, encryption, retention rules, and organisational policy remain necessary.

8. Systematic Error Resolution

Troubleshooting is more reliable when symptoms are separated from causes. A repeatable method is:

Step 1: Read the complete error

Record the error type, message, query, step, source, and refresh context. The first visible error may be a consequence of an earlier type or schema problem.

Step 2: Identify the failing step

Select Applied Steps from left to right. Determine the first step that fails, not merely the final step displaying an error.

Step 3: Inspect the value and type

Many failures result from nulls, unexpected text, changed column names, mixed types, or records where tables are expected. Confirm the actual structure using profiling, sample values, and type information.

Step 4: Reproduce with a small sample

Reduce the problem to a limited set of rows or a single file. A minimal reproducible query makes it easier to distinguish data-quality problems from connector or logic problems.

Step 5: Test the source and folding boundary

Check credentials, permissions, network availability, source schema, and whether a recent step stopped folding. A source timeout and an M expression error require different remedies.

Step 6: Apply a controlled correction

Examples include explicitly handling nulls, using try ... otherwise, correcting a type, protecting against missing fields, or revising a join key. Error handling should not silently convert serious data-quality problems into plausible but incorrect results.

Step 7: Validate and document

Test normal, boundary, empty, duplicate, and malformed cases. Record the cause, correction, business impact, and monitoring requirement.

Common errors include:

9. Applied Case: Retail Sales Refresh

The following is an illustrative case. A retailer has a Power BI model containing sales, product, and store data. The sales query reads a database table with 18 million rows. The report uses only the last two financial years, four sales measures, and selected product attributes. Refresh takes 42 minutes and occasionally fails with a timeout.

Background and problem

An initial inspection shows that the query imports the complete fact table, expands several columns, performs a custom text-cleaning function, sorts the table, and then filters dates. A separate product query is merged after several local transformations. The query also buffers the fact table before the merge.

Analysis

The date filter is late, so unnecessary rows are transferred and processed. The sort has no reporting requirement. The buffer materialises a large table and blocks later folding. Diagnostics indicate substantial source transfer and local processing. The database team confirms that the date column is indexed.

Approach and solution

The analyst makes one change at a time:

1. moves the financial-year filter immediately after navigation to the source table;
2. selects only required fact columns before the merge;
3. removes the unnecessary sort;
4. postpones text cleaning until after row reduction;
5. removes the fact-table buffer;
6. retains a small buffered product lookup only after testing repeated evaluation; and
7. checks the folding boundary and validates source query performance.

The analyst also adds a schema check for required columns and documents the financial-year parameter. The product and fact sources are classified consistently under organisational policy, and credentials are configured in the deployment environment rather than embedded in the query.

Outcome

The revised query transfers far fewer rows, keeps the date filter and column projection close to the source, and performs custom logic on a smaller dataset. The final refresh takes 15 minutes in comparable conditions. The improvement is approximately:

The result is not credited simply to “using folding”; it follows from several verified changes, including early reduction, removal of unnecessary work, and appropriate buffering decisions.

Learning points

The case demonstrates that performance is an architectural property of the whole query. A technically correct query may still be operationally poor. Reliable improvement requires measurement, controlled changes, source collaboration, and validation against business requirements.

10. Limitations, Governance, and Contemporary Practice

Query folding depends on connector implementation and may change when a source, gateway, or Power BI version changes. A query that folds today should not be considered permanently guaranteed to fold. Critical refreshes should be monitored after upgrades and schema changes.

Cloud data platforms increasingly support query pushdown, lakehouse tables, incremental refresh, and central transformation layers. These capabilities can reduce repeated desktop work, but they do not eliminate the need for sound query design. Incremental refresh, for example, depends on correctly parameterised date filters and compatible source behaviour; it is not a substitute for validating folding.

AI-assisted development can suggest M expressions, identify likely type errors, or explain transformations. However, generated code must be reviewed for privacy, credentials, data leakage, semantic correctness, and performance. An AI suggestion that adds a row-by-row web request or buffers a large table may be syntactically valid but operationally unsuitable.

Professional governance includes:

• least-privilege source access;
• documented privacy classifications;
• separation of development, test, and production settings;
• controlled parameters and secrets;
• refresh monitoring and alerting;
• data-quality checks; and
• clear ownership of source and transformation logic.

Best-Practice Checklist

Before publishing a query, ask:

• Is the narrowest suitable source object being used?
• Are selective filters and required columns applied early?
• Which steps fold, and where does folding stop?
• Are joins and custom operations performed on a reduced dataset?
• Has buffering been justified by measurement?
• Have full-refresh conditions been tested rather than only previews?
• Are privacy levels compatible with the intended data combination?
• Are nulls, schema changes, duplicate keys, and malformed values handled?
• Are credentials and parameters managed securely?
• Is the design documented sufficiently for another analyst to maintain?

Chapter Summary

Efficient Power Query design requires attention to both logical correctness and physical execution. Query folding can push compatible transformations to a source system, reducing transfer and local work, but folding is connector- and operation-dependent and must be verified. Early filtering and column selection usually reduce downstream cost, while joins, sorting, grouping, and custom logic should be placed thoughtfully. Buffering materialises data and can help in specific measured situations, but it may consume memory and prevent folding.

Diagnostics provide evidence about source access and local evaluation. Privacy levels protect information boundaries when sources are combined, and the Formula.Firewall should be resolved through sound architecture rather than routinely disabled. A systematic troubleshooting process identifies the first failing step, inspects types and values, isolates the problem, applies a controlled correction, and validates both ordinary and exceptional cases. Performance work should be measured, documented, and reviewed as part of data governance.

Key Terms

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

Folding boundary: The point at which a transformation is no longer executed by the source.

Connector: Software interface that allows Power Query to access a data source.

Predicate: A condition used to select rows, such as a date or status filter.

Projection: Selection of required columns from a dataset.

Materialisation: Loading an intermediate value into memory for local evaluation.

Buffering: Explicitly materialising a table, list, or binary value using a buffer function.

Query Diagnostics: Tools that provide evidence about query evaluation and source activity.

Privacy level: A classification governing how data may be combined with other sources.

Formula.Firewall: Power Query protection that restricts unsafe or incompatible source combinations.

Data type: The interpretation of a value, such as date, number, text, or logical.

Schema: The structural definition of fields, types, and relationships in a source.

Staging query: A reusable query that prepares source data for downstream queries.

Native query: Source-language text or operation generated or submitted by a connector.

Refresh: Re-execution of data-access and transformation logic to obtain current results.

Parameter: A controlled value used to make query logic configurable.

Data-quality rule: A condition used to identify invalid, missing, inconsistent, or unexpected data.

Incremental refresh: A strategy that refreshes only selected partitions or periods rather than all historical data.

Review Questions

5 Short Answer Questions

1. What is query folding, and why can it improve refresh performance?
2. Why should unnecessary columns usually be removed early?
3. Give one situation in which buffering might be useful and one risk of using it.
4. What is the purpose of privacy levels in Power Query?
5. Why should the first failing Applied Step be identified during troubleshooting?

5 Long Answer Questions

1. Explain how query folding changes the physical execution of a Power Query transformation sequence.
2. Discuss the principles of efficient step ordering, including early filtering, column selection, joins, and custom logic.
3. Compare query diagnostics with source-side database monitoring as performance-investigation techniques.
4. Explain how privacy levels and the Formula.Firewall support data governance when combining sources.
5. Describe a systematic method for resolving a refresh failure from initial symptom through final validation.

3 Analytical / Application Questions

1. A query imports 12 million rows, sorts them, applies a custom function, and then filters to three months. Analyse the design and propose a revised step order, explaining the expected effects.
2. A small lookup table is repeatedly evaluated during a merge, but a large fact table is buffered before filtering. Recommend where buffering should and should not be used, and justify your decision.
3. A refresh became slower after a connector update, although the output remains correct. Design an investigation using folding checks, diagnostics, source monitoring, and controlled testing.

Multiple Choice Questions with Answers

Question 1

Which statement best describes query folding?

A. It permanently stores a query result in the Power BI model.

B. It translates compatible transformations into operations performed by the source.

C. It converts every M expression into SQL regardless of the connector.

D. It prevents Power Query from using local transformations.

Correct Answer: B. It translates compatible transformations into operations performed by the source.

Question 2

Which step will usually provide the greatest performance benefit when placed early for a large fact table?

A. A sort used only for visual appearance

B. A selective source-supported date filter

C. A complex row-by-row text function

D. A buffer of the complete table

Correct Answer: B. A selective source-supported date filter

Question 3

What is a significant risk of applying Table.Buffer to a large table before filtering?

A. It guarantees query folding.

B. It removes all null values.

C. It may materialise excessive data and prevent later folding.

D. It automatically creates a source index.

Correct Answer: C. It may materialise excessive data and prevent later folding.

Question 4

Which tool or approach provides evidence about evaluation activity and source access during a refresh?

A. Query Diagnostics

B. Changing a column name

C. Adding a visual to a report

D. Sorting the final table

Correct Answer: A. Query Diagnostics

Question 5

What is the primary governance purpose of privacy levels?

A. To increase the compression ratio of the model

B. To classify how data may be combined with other sources

C. To replace database permissions

D. To guarantee that every transformation folds

Correct Answer: B. To classify how data may be combined with other sources

Question 6

A query fails because a text value cannot be converted to a number. Which investigation is most directly relevant?

A. Inspecting malformed values, nulls, and locale assumptions

B. Adding an unnecessary sort

C. Disabling all privacy settings immediately

D. Buffering the entire source table

Correct Answer: A. Inspecting malformed values, nulls, and locale assumptions

Question 7

Why is the preview alone insufficient for judging refresh performance?

A. Previews may use limited or cached evaluation unlike a full refresh.

B. Previews always contain more rows than the source.

C. Previews never execute any transformations.

D. Previews automatically disable query folding.

Correct Answer: A. Previews may use limited or cached evaluation unlike a full refresh.

Question 8

A refresh decreases from 20 minutes to 12 minutes. What is the percentage reduction?

A. 20%

B. 40%

C. 60%

D. 67%

Correct Answer: B. 40%

Question 9

Which response is most appropriate when the Formula.Firewall blocks a source combination?

A. Review privacy classifications and redesign the combination if necessary.

B. Ignore the error because it only affects formatting.

C. Embed credentials in the M code.

D. Buffer every table without investigating.

Correct Answer: A. Review privacy classifications and redesign the combination if necessary.

Question 10

Which sequence best represents systematic troubleshooting?

A. Change many steps, refresh once, and select the fastest result.

B. Read the error, identify the first failing step, isolate, test a controlled correction, and validate.

C. Disable privacy protections, delete the query, and recreate it.

D. Sort the data, add a buffer, and assume the source is responsible.

Correct Answer: B. Read the error, identify the first failing step, isolate, test a controlled correction, and validate.