Power Query Editor and Query Workflow

Introduction

Power Query is the data-preparation and transformation environment integrated into Power BI. It enables analysts to connect to data sources, inspect and reshape data, combine information from multiple locations, and prepare reliable tables for analysis. Although Power Query is commonly introduced through its graphical interface, it is also a programming environment: every transformation is represented by an expression in the Power Query Formula Language, commonly called M.

A sound understanding of the Power Query Editor is essential because the quality of a Power BI report depends heavily on the quality of its queries. A visually attractive dashboard cannot compensate for missing records, incorrect data types, duplicated rows, poorly documented transformations, or an inappropriate loading strategy. The query workflow therefore forms a controlled sequence from source connection to model-ready output.

This chapter examines the Power Query Editor, query organization, applied steps, previews, load settings, the formula bar, the Advanced Editor, and query dependencies. It also presents a practical case involving sales data and discusses performance, governance, maintainability, and common errors.

Learning Objectives

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

1. Describe the main areas of the Power Query Editor.
2. Create, rename, group, duplicate, reference, and document queries.
3. Interpret the preview grid and Applied Steps pane.
4. Explain how Power Query transformations are represented in M.
5. Use the formula bar and Advanced Editor safely.
6. Distinguish between queries that should load to the data model and those used only as staging or reference queries.
7. Interpret a query-dependency diagram.
8. Apply a repeatable workflow for preparing data in Power BI.
9. Identify common quality, performance, security, and governance risks.

1. The Query Workflow in Power BI

A query is a set of instructions that retrieves and transforms data. It is not simply a copy of a source table. When a query is refreshed, Power Query repeats its instructions against the source and produces a new result.

A typical workflow contains the following stages:

1. Connect to a source such as an Excel workbook, text file, relational database, web service, or folder.
2. Inspect the source structure and preview records.
3. Select the required table, sheet, file, or endpoint.
4. Profile the data by examining column quality, distribution, errors, and data types.
5. Transform the data by filtering, splitting, merging, appending, replacing, grouping, or reshaping values.
6. Validate the result against business expectations and source totals.
7. Organize queries and name them clearly.
8. Configure loading so that only appropriate outputs are loaded into the model.
9. Close and apply the queries to make the results available in Power BI.

This workflow is iterative rather than strictly linear. For example, profiling may reveal that a source contains two header rows, requiring the analyst to return to the early transformation steps. Similarly, validation may reveal that a merge has created duplicate records.

2. Navigating the Power Query Editor

The Power Query Editor is opened in Power BI Desktop by selecting Transform data. Its principal areas are described below.

2.1 Queries pane

The Queries pane, usually on the left, lists all queries in the current Power BI file. It provides a structural view of the data-preparation solution. Queries may be renamed, grouped, duplicated, referenced, or deleted from this area.

Query names should communicate purpose. Names such as Sales_Staging, DimCustomer, FactSales, and Calendar are more useful than default names such as Sheet1 or Query2. Consistent naming is especially important when a workbook contains many queries.

Queries may be organized into groups such as:

• 01_Source: direct connections to files, databases, or services;
• 02_Staging: standardized but generally uncombined data;
• 03_Transform: business transformations and combinations;
• 04_Output: final tables loaded into the model.

Groups improve navigation but do not themselves determine execution order or data-model relationships.

2.2 Ribbon

The ribbon contains commands for common operations, including choosing data sources, removing or selecting columns, changing data types, splitting columns, grouping rows, merging queries, appending queries, and managing parameters. Many commands are context-sensitive: the available actions depend on the selected column or table.

A ribbon command is often the safest starting point for a learner because it generates valid M code automatically. However, the generated expression should still be reviewed. An automatic step may use an incorrect data type, select the wrong header row, or create a fragile reference to a column position.

2.3 Preview pane

The central preview pane displays a sample or preview of the current query result. It is used to inspect values, column names, errors, data types, and the effect of each transformation.

The preview is not necessarily the complete dataset. Depending on the source and operation, Power Query may display only a sample or a limited number of rows. Therefore, a clean preview does not prove that every record is correct. Validation should also use source counts, totals, error checks, and, where appropriate, database queries or controlled test extracts.

Important preview features include:

• Column quality, which indicates valid, error, and empty values;
• Column distribution, which helps identify frequent and distinct values;
• Column profile, which provides more detailed statistics for a selected column;
• data-type icons, which indicate whether a field is text, whole number, decimal number, date, time, date/time, logical, or another type.

The profiling commands should be interpreted carefully. If profiling is based on the top rows rather than the entire dataset, unusual values occurring later may not appear in the profile.

2.4 Formula bar

The formula bar displays the M expression for the selected step. It can be used to inspect and edit an individual expression. If it is hidden, it can normally be enabled from the View options in the Power Query Editor.

The formula bar is useful for small, controlled changes, such as altering a filter value, changing a column name, or modifying a function argument. Before editing, identify the step’s inputs and outputs and check whether later steps depend on the affected column.

2.5 Applied Steps pane

The Applied Steps pane, normally on the right, lists transformations in sequence. Each step has a name and an expression. Selecting a step changes the preview to show the result at that point in the workflow.

Common steps include:

• Source;
• Navigation;
• Promoted Headers;
• Changed Type;
• Removed Columns;
• Filtered Rows;
• Split Column;
• Merged Queries;
• Expanded Table Column;
• Appended Query;
• Grouped Rows;
• Renamed Columns;
• Added Custom Column.

The order of steps matters. Filtering before an expensive merge may reduce the amount of data processed. Changing types before arithmetic is usually necessary, while changing types too early can cause errors if the source contains nonconforming values.

A step can usually be renamed by right-clicking it and choosing a rename command. Descriptive names such as Filtered to Active Customers and Standardized Order Date make a query easier to audit than generic names such as Filtered Rows.

3. Applied Steps and the M Evaluation Model

Power Query uses a sequence of named expressions. A simplified query can be represented conceptually as:

This arrow indicates dependency, not a mathematical operation. Each step receives the output of a preceding step and produces a new value. In the user interface, a query may appear to modify a table in place, but the underlying model is a chain of expressions.

A simple M query may look like this:

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(SalesSheet, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(
        PromotedHeaders,
        {{"OrderDate", type date}, {"Quantity", Int64.Type}, {"UnitPrice", Currency.Type}}
    ),
    FilteredRows = Table.SelectRows(ChangedType, each [Quantity] > 0)
in
    FilteredRows

The let section defines names and expressions. The expression after in identifies the value returned by the query. In this example, the final result is the table produced by FilteredRows.

M is case-sensitive in many contexts and uses structured values such as tables, records, lists, and functions. The graphical interface hides much of this complexity, but understanding the basic structure helps users diagnose errors.

Step order and dependency

Suppose a query contains the following sequence:

1. Source
2. PromotedHeaders
3. ChangedType
4. FilteredRows
5. RenamedColumns

If RenamedColumns changes OrderDate to Order Date, a later expression that refers to OrderDate may fail. A transformation does not merely change the display; it changes the structure available to subsequent expressions.

A practical rule is to make structural changes early and business-rule transformations later. For example:

• remove unnecessary top rows;
• promote headers;
• standardize column names;
• assign data types;
• remove unusable records;
• apply business filters and calculations;
• merge or append data;
• select the final output columns.

This is a guideline rather than an absolute law. The optimal order depends on source behaviour, query folding, and the transformation being performed.

4. Creating and Organizing Queries

4.1 Duplicate versus reference

Power Query provides both Duplicate and Reference operations, and the distinction is important.

A duplicated query creates a separate copy of the existing query definition. Subsequent changes to the original do not normally alter the duplicate’s later steps. Duplication is useful when two queries need similar starting logic but will evolve independently.

A referenced query creates a new query whose starting point is the output of another query. If the original query changes, the reference generally receives the changed output. References are useful for building a staged architecture in which several outputs depend on a standardized source query.

For example, a company may create Sales_Staging once and then reference it to create FactSales, SalesByRegion, and SalesExceptions. This reduces repeated source logic and promotes consistency.

4.2 Merge and append

A merge combines columns from two tables by matching key values, similar to a relational join. A merge requires compatible key columns and an explicitly selected join kind, such as left outer, inner, or full outer. If the matching table contains multiple rows for a key, expanding the merge result can multiply rows. This is a major source of accidental overstatement in business reports.

An append combines rows from two or more tables. It is appropriate when tables have the same conceptual meaning, such as monthly sales files. Column names and data types should be standardized before appending. Missing columns may produce null values in the appended result.

4.3 Parameters

Parameters store values that may change independently of the transformation logic. Examples include a folder path, a reporting date, a server name, or a minimum transaction value. Parameters support reuse and controlled deployment, but they should be named clearly and protected from unauthorized changes.

4.4 Query properties and documentation

Query properties may include a description and load configuration. Descriptions should record the source, purpose, owner, refresh assumptions, and important data-quality rules. Documentation is particularly valuable when a report is maintained by a team rather than a single author.

5. Load Settings and Model Design

A query can be available in Power Query without being loaded into the Power BI data model. The Enable load setting controls whether its result is imported into the model when the query is applied.

The distinction between transformation queries and output queries is central to maintainable solutions:

Disabling load for an intermediate query can reduce clutter in the model and prevent users from creating relationships against incomplete staging data. It does not necessarily mean that the query is irrelevant: a disabled-load query may still be used by other queries.

Load settings should be considered alongside storage mode and source behaviour. Import mode stores a copy of the transformed result in the model. DirectQuery uses a different architecture in which queries and transformations may be constrained by source capabilities. Some Power Query transformations can be translated back to a source through query folding; others cannot.

A query should not be loaded merely because it exists. Load only tables that are intended for reporting, controlled audit, or an explicit operational purpose.

6. The Formula Bar and Advanced Editor

6.1 Formula bar

The formula bar is appropriate for localized edits. For example, a filter expression might be changed from:

= Table.SelectRows(ChangedType, each [Status] = "Open")

to:

= Table.SelectRows(ChangedType, each [Status] <> "Cancelled")

The symbols = and <> have specific meanings in M: the first begins the expression shown for a step, while the second means “not equal to.” Text values are enclosed in quotation marks.

After editing, select the confirmation control or press Enter, then examine the preview for errors. A syntactically valid expression may still produce an incorrect business result.

6.2 Advanced Editor

The Advanced Editor displays the complete M script for a query. It is useful for:

• reviewing the full dependency sequence;
• copying a query pattern;
• adding comments;
• editing multiple related steps;
• diagnosing syntax or structural errors;
• learning how interface commands translate into M.

A commented query might include:

let
    // Connect to the standardized staging output
    Source = Sales_Staging,
    // Keep only completed transactions
    Completed = Table.SelectRows(Source, each [Status] = "Completed")
in
    Completed

The Advanced Editor should be used carefully. A missing comma, unmatched parenthesis, misspelled step name, or incorrect type declaration can prevent the query from running. It is good practice to make one logical change at a time and preserve a previous working version.

6.3 Error interpretation

Power Query errors commonly arise from:

• a missing column;
• an invalid data type;
• a malformed date or number;
• an incorrect key in a navigation expression;
• a failed file path or credential;
• a function receiving the wrong argument;
• a merge or expansion that does not contain the expected field.

The error message is evidence about the failed expression, not necessarily proof that the source is defective. Trace the error backward through the Applied Steps pane and identify the first step at which the output becomes unexpected.

7. Query Dependencies

The query-dependency view presents relationships among queries. It is especially useful in solutions with staging, reference, merge, and append operations.

For example:

Customer_File ─┐
               ├─> Customer_Staging ─> DimCustomer
Sales_File ────┤
               └─> Sales_Staging ─────> FactSales
Product_File ─────────────────────────> DimProduct

The diagram should be read as a dependency structure. Arrows indicate that one query uses another query’s output. It does not necessarily indicate that one table is related to another in the Power BI semantic model. Query dependencies and model relationships are different concepts.

Dependency analysis helps answer questions such as:

• Which final table depends on a particular source?
• Which queries can be disabled safely?
• Is the same source being connected to repeatedly?
• Where should a standardization rule be implemented?
• Which change might affect several outputs?

A well-designed dependency structure usually has a small number of clearly identified source queries, reusable staging logic, and a limited set of final outputs.

8. A Practical Workflow for Reliable Transformations

The following procedure is appropriate for many certificate-level projects.

Step 1: Establish the business purpose

Define the intended output before transforming data. Identify the grain of the table: one row per order, order line, customer, product, or date. Many errors occur because the analyst does not know what one row represents.

Step 2: Inspect the source

Check headers, blank rows, merged cells, totals, notes, hidden sheets, inconsistent spelling, duplicate keys, and mixed data types. Spreadsheet formatting that looks clear to a person may be structurally ambiguous to Power Query.

Step 3: Create a source or staging query

Connect to the source and perform general cleaning. Remove irrelevant presentation elements, promote headers, standardize names, and assign types. Avoid embedding report-specific filters in a staging query if multiple outputs require different populations.

Step 4: Profile and validate

Use column quality and distribution tools, but also calculate or inspect meaningful business controls. For a sales table, controls may include row count, total quantity, total revenue, distinct order count, and records with missing keys.

If revenue is calculated as quantity multiplied by unit price, the arithmetic is:

Here, Quantity is the number of units, UnitPrice is currency per unit, and Revenue is currency. The relationship assumes that unit price is expressed in the same currency and that discounts, tax, returns, and shipping are either already included or handled separately. A column called Revenue should not be calculated this way if the source already contains a net revenue value with different business rules.

Step 5: Apply transformations in a traceable order

Use explicit, named steps. Avoid unnecessary transformations and preserve important source fields until validation is complete. Remove fields only when their absence is understood.

Step 6: Create output queries

Use references for related outputs. Merge only on keys whose uniqueness and meaning have been established. Append only tables with compatible grain and semantics.

Step 7: Configure load settings

Disable loading for intermediate queries and load only intended model tables. Review the final model after applying changes.

Step 8: Test refresh

A query that works against a small sample may fail during a full refresh. Test with current and historical data, missing values, unexpected categories, changed file names, and source outages where feasible.

9. Illustrative Case: Regional Sales Reporting

Background

A fictional retail company, Northstar Outfitters, receives monthly Excel files from four regions. Each file is intended to contain order-line data, but regional staff use different column names and date formats. Management wants a Power BI report showing sales by product, customer, region, and month.

Problem

The initial report has three weaknesses. First, every regional file is loaded as a separate table, producing a cluttered model. Second, some files contain a subtotal row that is interpreted as a transaction. Third, product descriptions are merged into sales using a non-unique description rather than a product code, causing duplicated sales amounts.

Analysis

The intended grain is one row per order line. Therefore, a regional subtotal row violates the grain and must be removed. The product code is the appropriate key because product descriptions are not guaranteed to be unique. The solution also requires a consistent data type for dates, quantities, and prices.

Approach

1. A folder connection is created to enumerate the regional files.
2. A staging query extracts the contents of each file.
3. Header rows are promoted and regional column names are mapped to standard names.
4. Rows where RecordType = "Subtotal" are removed.
5. Dates are converted to the date type, quantities to whole numbers, and prices to currency.
6. The regional tables are appended into Sales_Staging.
7. Product data is standardized in Product_Staging.
8. A merge is performed using ProductCode, and the product attributes are expanded.
9. A validation query identifies sales rows with no matching product code.
10. FactSales, DimProduct, DimCustomer, and a date table are loaded; staging and exception-support queries are not loaded unless required for audit.

The merge must be checked for key uniqueness. If Product_Staging contains two rows for one product code, expanding the merge can produce two output rows for a single order line. The correct response is to resolve the duplicate in the product staging logic, not simply to remove duplicate sales rows after the merge.

Outcome

The resulting model contains a consistent sales fact table and reusable dimensions. Refreshing the folder connection automatically incorporates new monthly files, provided the files follow the agreed structural rules. The exception query gives data stewards a list of unmatched product codes for correction.

Learning points

• Table grain must be defined before transformations are designed.
• Folder ingestion requires structural controls, not only file selection.
• A merge key must represent the intended relationship.
• Exception outputs are valuable for governance and operational correction.
• Disabled-load queries can still be essential parts of a dependency chain.

10. Performance, Quality, and Governance

Query folding and transformation order

Query folding occurs when Power Query translates transformations into operations performed by the source system, particularly for relational databases. Folding can reduce data transferred and improve refresh performance. Not every connector or transformation supports folding, and folding behaviour should be verified rather than assumed.

For foldable sources, filtering columns and rows early may reduce the amount of data processed. However, “early” should not mean blindly placing every filter before every other operation. A filter based on a derived column cannot be applied before that column exists, and a filter can change the meaning of a later aggregation. Logical correctness remains the first priority.

Data types

Data types affect sorting, filtering, arithmetic, storage, and joins. A date stored as text may sort alphabetically; an identifier stored as a number may lose leading zeros. Product codes such as 00125 should generally remain text even if they contain only digits.

Automatic type detection is convenient but should be reviewed. Explicit type assignment is more reliable for production queries.

Privacy and credentials

Power Query may combine data from multiple sources, including local files, databases, and web services. Privacy-level settings and credentials help control how sources interact. Analysts should not embed passwords, tokens, or confidential values directly in M code. Access should follow organizational security policies, least-privilege principles, and applicable privacy regulation.

Reproducibility and change management

A query should be understandable and repeatable by another competent analyst. Use stable source paths or parameters, descriptive names, comments for unusual logic, and documented assumptions. Keep a copy of the previous working version before substantial changes. In collaborative environments, store relevant scripts and documentation in an approved version-control or document-management system.

AI-assisted transformation

AI tools may suggest M expressions or diagnose an error, but generated code must be reviewed. The analyst remains responsible for checking syntax, data types, key uniqueness, privacy, licensing, and business meaning. Confidential source data should not be pasted into an unapproved external service.

11. Common Mistakes and Their Remedies

Chapter Summary

Power Query Editor is the working environment in which Power BI data is connected, inspected, transformed, validated, and prepared for loading. Its major areas include the Queries pane, ribbon, preview grid, formula bar, Applied Steps pane, and Advanced Editor. Applied Steps represent a dependent sequence of M expressions, so order, naming, and column structure matter.

Queries should be organized into source, staging, transformation, and output layers. Duplication creates an independent copy, whereas a reference builds on another query’s output. Merge operations combine columns through keys, while append operations combine rows from compatible tables. Load settings determine which query results enter the data model, and intermediate queries can usually remain disabled while still supporting final outputs.

The formula bar supports local expression edits, while the Advanced Editor exposes the complete M script. Query dependencies reveal how queries support one another but should not be confused with semantic-model relationships. Reliable practice includes explicit data types, defined table grain, validation controls, careful key selection, appropriate query folding, secure credentials, documentation, and controlled use of AI assistance.

Key Terms

Query: A set of instructions that retrieves and transforms data.

Power Query Editor: The interface used to connect to, inspect, and transform data.

M: The Power Query Formula Language.

Applied Step: A named transformation in a query’s sequence.

Preview: A displayed sample or view of a query result used for inspection.

Query pane: The area listing queries and query groups.

Formula bar: The interface area showing the expression for a selected step.

Advanced Editor: The interface for viewing and editing a complete M script.

Staging query: A query used to standardize or prepare source data.

Reference: A query built from the output of another query.

Duplicate: An independent copy of a query definition.

Merge: A join operation that adds columns based on matching keys.

Append: An operation that combines rows from tables.

Query dependency: A relationship in which one query uses another query’s output.

Enable load: A setting controlling whether a query result enters the model.

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

Data type: The interpretation of values as text, date, number, logical value, or another type.

Table grain: The meaning represented by one row in a table.

Parameter: A reusable value that can control query behaviour.

Key uniqueness: The property that a key identifies no more than the intended number of records.

Review Questions

Short Answer Questions

1. What is the purpose of the Applied Steps pane?
2. Distinguish between duplicating and referencing a query.
3. What does the Enable load setting control?
4. Why should data types be assigned explicitly in production queries?
5. How does a merge differ from an append?

Long Answer Questions

1. Explain the main areas of the Power Query Editor and describe how each supports the query workflow.
2. Explain how M represents a sequence of transformations, using a suitable example.
3. Discuss how query groups, naming conventions, references, and load settings contribute to maintainability.
4. Explain the risks of using a non-unique key in a merge operation and propose controls to prevent duplicated output rows.
5. Describe a complete workflow for preparing a folder of monthly files for a Power BI model.

Analytical / Application Questions

1. A product table contains two records with the same product code, and a sales table contains one order line for that code. Predict the effect of expanding a left merge and explain how you would correct the problem.
2. A query preview shows no errors, but the final sales total is higher than the source control total. Design a sequence of diagnostic checks using Applied Steps, query dependencies, data types, and merge validation.
3. Design a query architecture for a report that receives customer, product, and sales data from separate sources. Identify which queries should load, which should remain as staging queries, and where you would place validation or exception logic.

Multiple Choice Questions with Answers

Question 1

Which Power Query Editor area lists the transformations performed in sequence?

A. Queries pane

B. Applied Steps pane

C. Model view

D. Status bar

Correct Answer: B. Applied Steps pane

Question 2

What is the main difference between a referenced query and a duplicated query?

A. A reference uses another query’s output, while a duplicate is an independent copy

B. A reference can connect only to databases, while a duplicate can connect only to files

C. A duplicate is always loaded, while a reference is never loaded

D. A reference changes the source data, while a duplicate deletes it

Correct Answer: A. A reference uses another query’s output, while a duplicate is an independent copy

Question 3

Which operation combines rows from tables with compatible structures?

A. Merge

B. Expand

C. Append

D. Transpose

Correct Answer: C. Append

Question 4

What does disabling Enable load generally do?

A. Deletes the query from Power Query

B. Prevents the query result from being loaded into the data model

C. Prevents all other queries from using the query

D. Stops the source system from refreshing

Correct Answer: B. Prevents the query result from being loaded into the data model

Question 5

Which tool displays the complete M script for a query?

A. Advanced Editor

B. Column profile

C. Relationship view

D. Navigator window

Correct Answer: A. Advanced Editor

Question 6

Why can a merge on a non-unique key increase the number of rows?

A. Power Query automatically appends all source tables

B. One source row may match multiple rows in the other table

C. Data types are always converted to text

D. The preview grid duplicates every visible row

Correct Answer: B. One source row may match multiple rows in the other table

Question 7

Which data type is generally most appropriate for a product code such as 00125?

A. Whole number

B. Decimal number

C. Text

D. Date

Correct Answer: C. Text

Question 8

What does query folding describe?

A. Grouping queries into folders

B. Converting a query into a dashboard

C. Translating transformations into operations performed by the source

D. Compressing the M script into a smaller file

Correct Answer: C. Translating transformations into operations performed by the source

Question 9

In the relationship Revenue = Quantity × UnitPrice, what is the unit of Revenue if Quantity is units and UnitPrice is currency per unit?

A. Units per currency

B. Currency

C. Currency per unit squared

D. Units

Correct Answer: B. Currency

Question 10

What is the main purpose of the query-dependency view?

A. To define visual formatting rules

B. To show how queries use one another’s outputs

C. To replace all model relationships

D. To calculate financial measures

Correct Answer: B. To show how queries use one another’s outputs