Power Query Architecture and Workflow

Introduction

Power Query is Microsoft’s data preparation and integration technology for connecting to data, inspecting it, transforming it, combining it, and delivering the result to a destination such as a Power BI semantic model. It is available in Power BI Desktop, Excel, and several other Microsoft products. For a business intelligence professional, Power Query is not merely a collection of buttons for cleaning spreadsheets. It is an execution environment based on the Power Query M language, a dependency-aware query system, and an important boundary between raw source data and analytical models.

A well-designed Power Query solution separates four activities:

1. Ingestion: connecting to and reading data from sources.
2. Transformation: cleaning, reshaping, combining, and validating data.
3. Modeling: defining relationships, measures, hierarchies, and analytical structure.
4. Visualization: communicating insights through reports and dashboards.

These activities are related but should not be confused. Power Query primarily handles ingestion and transformation. Power BI modeling occurs after transformed data is loaded, while visualization occurs after the model is ready for analysis.

Learning Objectives

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

• explain the architecture and purpose of Power Query;
• distinguish queries, steps, previews, dependencies, and load settings;
• use the Power Query Editor to inspect and transform data;
• interpret the Applied Steps pane and understand step order;
• describe the role of the Mashup Engine and M language;
• explain query evaluation, dependencies, and source folding;
• separate ingestion, transformation, modeling, and visualization responsibilities;
• design maintainable queries using professional workflow practices;
• identify operational, governance, privacy, and performance considerations.

1. The Role of Power Query in Business Intelligence

A business intelligence system normally receives data from multiple operational environments: enterprise databases, cloud services, spreadsheets, text files, application programming interfaces, and data platforms. These sources are designed for operational work rather than direct analysis. Their structures may differ, names may be inconsistent, and values may contain errors or missing information.

Power Query provides a repeatable preparation layer. Instead of manually opening files and editing values, an analyst defines a sequence of transformations. The sequence can then be rerun when new data arrives. This improves consistency, auditability, and maintainability.

For example, a sales query might:

• connect to a folder containing monthly files;
• combine files with a common structure;
• promote the first row to column headers;
• remove unused columns;
• convert dates and amounts to appropriate data types;
• standardize product codes;
• filter invalid transactions;
• merge sales with a product reference table; and
• load the prepared result into Power BI.

The result is not usually a new physical source file. It is an evaluated output produced when the query runs.

2. Power Query Architecture

2.1 The main architectural components

A Power Query solution can be understood through five related components:

The editor is the user interface, but it is not the entire technology. When a user selects commands, Power Query generates or modifies M code. The Mashup Engine interprets that code, obtains source data, applies transformations, and returns a result.

2.2 Queries and expressions

A query is a named definition that produces a value when evaluated. In common data-preparation work, that value is a table, but it may also be a list, record, binary value, or scalar value. A query can refer to another query, which creates a dependency.

A simplified M expression 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]),
    ChangedTypes = Table.TransformColumnTypes(
        PromotedHeaders,
        {{"OrderDate", type date}, {"SalesAmount", Currency.Type}}
    )
in
    ChangedTypes

The expression contains a let section with named steps and an in clause identifying the final result. Step names are identifiers, while step values are expressions. Each later step can use the result of an earlier step.

The graphical interface normally creates this code automatically. Users do not need to write M code for every operation, but understanding the structure helps with troubleshooting and advanced development.

2.3 The Mashup Engine

The Mashup Engine is the execution component that evaluates Power Query expressions. It performs tasks such as:

• evaluating M expressions;
• requesting data through connectors;
• applying transformations;
• combining tables and other values;
• managing query dependencies; and
• returning results to the host application.

A query is therefore a program or recipe rather than a static copy of the data. The visible preview is a sample or representation of an evaluated result, while a refresh evaluates the query against the available source.

The engine may evaluate only the portions necessary for the requested output. It can also push some operations back to a source system when the connector and source support this behavior. This is commonly called query folding.

2.4 Query folding

Query folding occurs when Power Query translates compatible transformation steps into operations that the source system performs. For example, a filter applied to a relational database may become a SQL WHERE condition executed by the database rather than after all rows have been transferred to Power BI.

Folding can reduce data movement and improve performance, but it is not guaranteed for every connector or transformation. Operations involving custom logic, certain local functions, or incompatible data types may prevent folding. Folding is therefore a performance opportunity, not a universal assumption.

3. The Power Query Editor

The Power Query Editor is the main working environment for creating and managing queries. Its exact layout can vary slightly by product version, but its important regions are consistent.

3.1 Common interface regions

• Queries pane: lists queries and often groups them into folders.
• Ribbon: provides commands for connecting, transforming, combining, and managing queries.
• Formula bar: displays or permits editing of the M expression for the selected step.
• Data preview: shows the current result at the selected step.
• Applied Steps pane: lists transformations in execution order.
• Status bar: may show column quality, distribution, profiling, or preview information.

The editor should be treated as a development workspace. It allows a user to inspect intermediate results rather than viewing only the final output.

3.2 Data preview is not the loaded dataset

The data preview is an interactive representation of the current step. It may be limited in row count or based on a preview evaluation. Consequently, a preview should not be confused with a complete validation of all data.

For example, a column may appear valid in the visible sample while later rows contain invalid dates. Column profiling options can provide broader information, but full validation may require refreshing the complete query and checking errors in the resulting data.

3.3 Query properties and load settings

A query has properties that influence how it is used. In Power BI Desktop, a query can normally be configured to load into the model or remain available only as a supporting query. A supporting query may be used by other queries but does not need to create a table in the final model.

The Enable load setting is particularly important. Disabling load for a staging or helper query can reduce unnecessary model tables and make the semantic model easier to understand. It does not necessarily prevent the query from being evaluated when another enabled query depends on it.

The Include in report refresh setting controls whether a query participates in ordinary refresh operations. This setting must be used carefully because excluding a query from refresh can leave its output unchanged while other data is updated.

4. Queries, Steps, and Applied Steps

4.1 A query as a sequence of transformations

A query commonly begins with a source step and continues through transformations. The steps are evaluated in order, with each step receiving the output of the previous step. This sequence is visible in the Applied Steps pane.

Typical steps include:

• Source;
• navigation to a worksheet, table, or database object;
• promoted headers;
• changed type;
• removed columns;
• filtered rows;
• replaced values;
• merged queries;
• expanded columns;
• grouped rows; and
• renamed columns.

The final step is the value returned by the query.

4.2 Step order matters

Power Query transformations are often order-dependent. Removing rows before grouping can produce a different result from grouping before removing rows. Changing a data type before filtering may produce different errors from changing it afterward. Renaming a column also affects later steps that refer to that column name.

A good workflow places structural and quality-related operations early, while preserving business logic in clear, readable steps. However, the best order also depends on source folding and the need to retain information for later transformations.

4.3 Editing and deleting steps

Selecting a step displays its intermediate output. A step can be renamed to communicate its purpose, and many steps can be edited through a settings dialog. Deleting a step may cause later steps to fail if they refer to the deleted step or to fields created by it.

The Applied Steps pane therefore provides both documentation and a dependency chain. It should not be treated as a cosmetic list.

4.4 Errors and step diagnostics

Errors may arise from several causes:

• a source is unavailable;
• a file path has changed;
• a column has been renamed or removed;
• a value cannot be converted to the requested type;
• a merge uses incompatible keys;
• an expression contains invalid syntax; or
• permissions or privacy rules block a source combination.

Troubleshooting should begin at the earliest failing step. Later errors are often consequences rather than independent problems.

5. Query Dependencies and Reusable Design

5.1 Dependency relationships

A query can reference another query. For example, a Sales_Staging query may clean source files, while a Sales query references it and applies business-specific filters. The relationship can be represented as:

Source files → Sales_Staging → Sales → Power BI model
Product file → Product_Staging → Product → Power BI model

The Query Dependencies view displays these relationships graphically. It helps identify upstream sources, downstream outputs, and queries that can be reused.

5.2 Staging and reference queries

A staging query is normally responsible for source connection and general cleaning. A reference query uses the staging result without duplicating the original connection logic. This design supports reuse and reduces maintenance.

For example, one staged customer table might support:

• a customer dimension;
• an inactive-customer report; and
• a data-quality exception table.

If each output connects independently to the source and repeats the same cleaning steps, changes become difficult to manage and refresh work may increase.

5.3 Duplicate versus reference

A duplicate creates an independent copy of the query definition at the point of duplication. A reference creates a query that uses another query as its input. A reference is generally more suitable when a central staging process should feed multiple outputs.

The choice depends on the intended architecture. Duplication can be useful when two transformations should evolve independently; reference is useful when a shared preparation layer should remain authoritative.

Figure: Separation of source ingestion, Power Query transformation, semantic modeling, and reporting.

6. A Professional Power Query Workflow

A repeatable workflow reduces accidental complexity.

Step 1: Understand the source

Identify the source owner, update frequency, grain, keys, expected columns, data types, and privacy classification. The grain is the level represented by one row, such as one order line or one customer. Misunderstanding grain is a major cause of incorrect joins and duplicated totals.

Step 2: Connect with the appropriate connector

Use the connector designed for the source where possible. It may provide authentication, metadata, folding, incremental access, or navigation features that are not available through a generic method.

Step 3: Create a stable staging layer

Select the relevant table, worksheet, folder, or endpoint. Remove technical clutter, standardize names, and establish appropriate data types. Avoid embedding report-specific assumptions in a general staging query.

Step 4: Profile and validate

Inspect nulls, errors, distinct values, outliers, duplicates, and unexpected types. Use column quality and distribution indicators, but remember that preview information may not represent every row.

Step 5: Transform and combine

Use filters, replacements, splits, aggregations, merges, and appends as required. Confirm the matching key and cardinality before merging. Expand only the columns needed from a related table.

Step 6: Configure load behavior

Disable load for intermediate queries that should not become model tables. Keep enabled only those outputs required by the semantic model or other intended destinations.

Step 7: Apply and validate the model

After selecting Close & Apply, inspect the resulting tables, relationships, data types, row counts, and measures. A successful query refresh does not prove that the business result is correct.

7. Applied Example: Regional Sales Integration

Background

This illustrative case concerns Northstar Retail, which receives monthly sales files from three regions. Each file contains order number, order date, product code, quantity, and sales amount. A separate product workbook contains product codes, categories, and standard product names.

Problem

The regional files use inconsistent column names. One region stores dates as text, another includes currency symbols, and one file contains a temporary notes column. Analysts previously combined the files manually, producing inconsistent totals and repeated effort.

Analysis

The intended grain is one row per order line. Therefore, the order number alone may not be a unique key if an order contains several products. A more appropriate business key may be a combination of order number and product code, subject to confirmation with the business owner.

The integration design should include:

• a folder-based source query;
• a staging query for file standardization;
• a product reference query;
• a merge using product code;
• a final sales fact query; and
• a product dimension query.

Approach and solution

The analyst creates a folder connection and uses the combine-files process. The transformation function removes the notes column, promotes headers, standardizes column names, converts order date to date, converts quantity to a whole-number type, and converts sales amount to a suitable decimal or currency type.

A reference query named Sales_Staging becomes the input for Sales. The product workbook is cleaned separately. The Sales query merges product attributes using product code and expands only product name and category. Intermediate queries have load disabled, while the final sales and product outputs are enabled.

The analyst then checks:

• whether every sales product code matches a product record;
• whether any rows contain conversion errors;
• whether quantities are negative for legitimate returns or for data errors;
• whether the row count is plausible by month and region; and
• whether the merge has unexpectedly multiplied rows.

Outcome

The organization obtains a refreshable preparation process. New monthly files can be incorporated without repeating manual edits, and the final model receives standardized tables. Exceptions are visible and can be directed to data owners rather than silently removed.

Learning points

The case demonstrates that technical transformation and business interpretation must work together. A correctly executed merge can still be conceptually wrong if the key or grain is misunderstood. It also shows why staging, references, validation, and load settings are architectural decisions rather than minor interface choices.

8. Separation of Power Query, Modeling, and Visualization

A common beginner error is to solve every problem in the wrong layer. Power Query is appropriate for row-level cleaning, structural reshaping, source combination, and preparation of analytical tables. The semantic model is appropriate for relationships, measures, hierarchies, and analytical semantics. Visualization is appropriate for selecting charts, filters, interactions, and narrative presentation.

The boundaries are not absolute. A calculation can sometimes be performed during transformation or in a model measure. The decision should consider refresh behavior, reusability, data volume, semantic meaning, and the need for user interaction.

9. Governance, Privacy, and Security

Data integration can expose sensitive information. Power Query solutions should follow organizational policies for personal data, financial data, credentials, and regulated information.

Important practices include:

• use organizational authentication rather than sharing personal credentials;
• avoid hard-coding passwords or secrets in M expressions;
• use parameters for environment-specific paths and endpoints;
• document data sources and ownership;
• restrict access to workspaces and source systems;
• consider privacy levels when combining sources; and
• retain only fields required for the analytical purpose.

Privacy levels are intended to help prevent unintended information leakage when data from different sources is combined. They can also affect how the engine evaluates combinations. Administrators and developers should understand the organizational policy before changing privacy settings to eliminate a refresh warning.

Data minimization is both a governance principle and a design advantage. Removing unnecessary sensitive columns early reduces exposure and simplifies later processing.

10. Performance and Maintainability

Professional Power Query development involves more than making a query return the desired rows.

Performance practices

• filter early when the source can fold the filter;
• select only necessary columns;
• preserve query folding where practical;
• avoid repeated expensive operations;
• use appropriate data types;
• avoid loading unused helper queries; and
• test refresh with realistic data volumes.

These practices are guidelines, not rigid rules. For example, an early transformation may improve readability but prevent folding. The developer should test the actual connector and source system.

Maintainability practices

• use meaningful query and step names;
• separate staging from business-specific outputs;
• group related queries;
• use parameters for paths, dates, and environments;
• document unusual business rules;
• avoid unnecessary automatic type changes;
• preserve source metadata where it is useful; and
• validate assumptions about keys and row grain.

A short query is not necessarily a good query. Clarity, testability, and controlled dependencies are more valuable than minimizing the number of visible steps.

11. Contemporary Developments and AI-Assisted Work

Power Query continues to evolve through improved connectors, cloud integration, dataflows, deployment features, and enhanced profiling. Organizations increasingly use reusable data-preparation assets across reports rather than rebuilding transformations in every file.

AI-assisted development can help suggest transformations, explain M expressions, or identify likely data-quality issues. However, generated code must be reviewed. An AI system may misunderstand business grain, infer an incorrect data type, expose confidential content, or produce a transformation that works only for the visible sample. Professional accountability remains with the analyst and organization.

The most reliable approach is to use AI as a development assistant while retaining human review, test cases, source documentation, privacy controls, and reconciliation against trusted totals.

Challenges and Limitations

Power Query has important limitations:

• source connectors differ in capabilities;
• query folding is connector- and operation-dependent;
• previews may not reveal all data errors;
• large transformations can consume substantial memory or refresh time;
• schema changes can break navigation and later steps;
• privacy and credential settings can complicate source combinations; and
• a technically successful refresh may still produce a poor analytical result.

Power Query also does not replace a governed enterprise data warehouse. For large, shared, highly controlled environments, transformations may belong partly or primarily in centralized data platforms. Power Query remains valuable as an integration and semantic preparation tool, but its role should fit the organization’s broader architecture.

Best-Practice Checklist

Before publishing a Power Query solution, confirm that:

1. each source is documented and authorized;
2. the grain of each output is understood;
3. staging and final queries are clearly separated;
4. intermediate queries have appropriate load settings;
5. data types are explicitly checked;
6. errors and unmatched keys are investigated;
7. query dependencies are understandable;
8. folding and refresh performance have been considered;
9. sensitive fields are minimized and protected; and
10. the final model and business totals have been reconciled.

Chapter Summary

Power Query is a query-based data integration and transformation environment built around M expressions and evaluated by the Mashup Engine. The Power Query Editor provides the workspace for connecting to sources, inspecting previews, applying transformations, managing dependencies, and configuring load behavior.

A query is an executable definition, and its Applied Steps show the ordered sequence through which data is transformed. Query Dependencies reveal relationships among staging, reference, and output queries. Preview results are useful for development but do not guarantee complete-data validity. Load settings determine whether query results become model tables, while refresh settings influence when outputs are recomputed.

A professional workflow separates ingestion and transformation from semantic modeling and visualization. It uses appropriate connectors, reusable staging queries, explicit types, controlled dependencies, validation, privacy safeguards, and performance testing. The Northstar Retail case illustrates how these principles support reliable, refreshable integration. Power Query is powerful, but it must be used with attention to source behavior, folding, data grain, governance, and business meaning.

Key Terms

Query: A named M expression that produces a value when evaluated.

Power Query Editor: The graphical environment used to create, inspect, and manage queries.

M language: The functional language used to define Power Query expressions and transformations.

Mashup Engine: The execution engine that evaluates M expressions and performs data integration operations.

Data source: An original system or file that provides data.

Connector: A component that enables Power Query to access a particular source type.

Data preview: An interactive representation of a query step’s result.

Applied Steps: The ordered list of transformations in a query.

Query dependency: A relationship in which one query uses another query as an input.

Staging query: A reusable query that performs source connection and general preparation.

Reference query: A query that uses another query as its input.

Duplicate query: An independently copied query definition.

Enable load: A setting that determines whether a query output is loaded into the destination model.

Query folding: Translation of compatible Power Query operations into operations performed by the source system.

Data grain: The level of detail represented by one row in a table.

Merge: A join operation that combines columns from related tables using matching values.

Append: An operation that places rows from one table below rows from another table.

Semantic model: The analytical structure containing tables, relationships, measures, and related metadata.

Data profiling: Examination of column quality, distributions, errors, nulls, and value patterns.

Privacy level: A setting that governs how data from sources may be combined and protected.

Review Questions

Short Answer Questions

1. What is the purpose of the Mashup Engine?
2. Why is the data preview not necessarily a complete validation of a dataset?
3. What is the difference between a staging query and a final output query?
4. What does the Enable load setting control?
5. Define query folding.

Long Answer Questions

1. Explain the major components of Power Query architecture and describe how they interact during refresh.
2. Describe how the Applied Steps pane supports both transformation design and troubleshooting.
3. Compare duplicate and reference queries, including situations in which each may be appropriate.
4. Explain why ingestion, transformation, modeling, and visualization should be treated as distinct activities.
5. Discuss the main performance, maintainability, privacy, and governance considerations in a professional Power Query solution.

Analytical / Application Questions

1. A monthly sales file changes its column name from SalesAmount to NetSales. Explain which parts of a Power Query workflow are likely to fail and propose a systematic troubleshooting approach.
2. A merge between a sales table and a product table increases the number of sales rows by 18%. Analyze possible causes and describe the checks required before accepting the result.
3. Design a query architecture for three regional files and one product reference file. Identify the staging queries, reference queries, load settings, validation checks, and model outputs you would use.

Multiple Choice Questions with Answers

Question 1

Which component evaluates M expressions and performs Power Query transformations?

A. The visualization canvas

B. The Mashup Engine

C. The relationship diagram

D. The report filter pane

Correct Answer: B. The Mashup Engine

Question 2

What does the Applied Steps pane primarily show?

A. The report’s visual interaction settings

B. The database server’s hardware configuration

C. The ordered transformations in a query

D. The model’s calculated measures only

Correct Answer: C. The ordered transformations in a query

Question 3

Which setting prevents an intermediate query from becoming a table in the Power BI model?

A. Enable load disabled

B. Column quality enabled

C. Privacy level public

D. Query folding enabled

Correct Answer: A. Enable load disabled

Question 4

What is query folding?

A. Compressing a report into a PDF file

B. Translating compatible transformations so the source performs them

C. Combining all queries into one visual

D. Converting every column to text

Correct Answer: B. Translating compatible transformations so the source performs them

Question 5

Why is a reference query commonly used in a staging architecture?

A. It allows several outputs to reuse a prepared query

B. It permanently deletes the source data

C. It prevents all refreshes from occurring

D. It converts a table into a chart

Correct Answer: A. It allows several outputs to reuse a prepared query

Question 6

Which activity belongs primarily to the semantic modeling layer rather than Power Query?

A. Removing an unused source column

B. Converting text to a date type

C. Defining a relationship between model tables

D. Appending monthly source files

Correct Answer: C. Defining a relationship between model tables

Question 7

Why must the grain of a table be understood before performing a merge?

A. Grain determines the report theme

B. Grain helps determine whether matching can multiply rows incorrectly

C. Grain controls the screen resolution

D. Grain automatically creates a password

Correct Answer: B. Grain helps determine whether matching can multiply rows incorrectly

Question 8

Which statement about a data preview is most accurate?

A. It always proves that every source row is valid

B. It is an interactive representation that may not show all data problems

C. It is the same as the final semantic model

D. It permanently stores a complete source backup

Correct Answer: B. It is an interactive representation that may not show all data problems

Question 9

Which practice generally improves maintainability?

A. Using meaningful query and step names

B. Hard-coding credentials in M code

C. Repeating the same source logic in every output query

D. Loading every helper query into the model

Correct Answer: A. Using meaningful query and step names

Question 10

Which operation places rows from one table below rows from another table?

A. Merge

B. Append

C. Expand

D. Promote headers

Correct Answer: B. Append