Power Query Editor — Use Power Query Editor to inspect and transform data - Microsoft Power Bi

SECTION 1

Historical Context & Motivation

Long before Power Query became the default ETL layer inside Microsoft Power BI, data analysts routinely wrestled with the painful gap between raw source data and a clean, analysis-ready model. Spreadsheets arrived with inconsistent column names, CSVs contained embedded line breaks, and SQL exports mixed data types across rows—all of which demanded tedious manual cleanup. The evolution of Power Query is, in many respects, the story of Microsoft recognizing that data preparation consumes far more analyst time than actual analysis, and that a dedicated, reproducible transformation engine was essential for modern business intelligence.

2010 • Project "Data Explorer" begins

An internal Microsoft Research team prototypes a self-service data-mashup engine code-named "Data Explorer," designed to let non-developers ingest, reshape, and combine heterogeneous data sources entirely through a graphical interface.

2013 • Power Query ships as an Excel add-in

Microsoft publicly releases Power Query as a free Excel 2010/2013 add-in, introducing the M language (informally called "M") as the underlying functional transformation language. Analysts can now record and replay data-cleaning steps.

2015 • Power BI Desktop launches

Power BI Desktop ships with Power Query Editor as its primary data-ingestion interface. The editor becomes the front door through which every dataset enters the BI model, unifying data acquisition and transformation in a single tool.

2018–2020 • Dataflows & Power Query Online

Microsoft extends Power Query to the cloud via Power BI Dataflows and Power Platform Dataflows, enabling server-side execution, shared transformation logic, and enterprise-grade governance—all built on the same M engine.

2023–Present • Fabric & Copilot integration

Power Query is embedded across Microsoft Fabric lakehouses and warehouses. Natural-language Copilot prompts can now generate M code, lowering the barrier further while preserving the deterministic, step-based transformation paradigm.

The core question Power Query addresses is deceptively simple: how can we make data-cleaning steps reproducible, auditable, and automatically re-executable every time the source data refreshes? This is the same concern that motivates version-controlled build pipelines in software engineering, and understanding Power Query through that lens—an immutable, declarative transformation pipeline—will serve you well as a computer science student.

SECTION 2

Core Principles & Definitions

Power Query Editor operates on a set of foundational design principles that distinguish it from ad-hoc data manipulation in spreadsheets or imperative scripting. Each principle maps cleanly onto concepts you already know from functional programming and software engineering: immutability, lazy evaluation, and composable transformations. Grasping these principles first will make every button, menu, and formula bar interaction in the editor feel intuitive rather than arbitrary.

Applied Steps as an Immutable Log

Every action you perform—renaming a column, filtering rows, changing a type—is recorded as a discrete Applied Step in the Query Settings pane. Steps are appended, never mutated in place. You can click any prior step to inspect the table's state at that point, much like checking out a previous commit in Git.

M Language — A Functional Core

Behind the GUI, every step generates M code—a case-sensitive, purely functional language. Expressions are chained via let … in blocks. M supports higher-order functions, list comprehensions, and lazy evaluation, meaning values are computed only when downstream steps demand them.

Query Folding

When the source is a relational database, Power Query attempts query folding—translating your M steps back into native SQL and pushing computation to the server. This is analogous to predicate push-down in query optimizers and can dramatically reduce data transfer and processing time.

Schema-on-Read

Power Query infers column types at read time rather than requiring a pre-defined schema. You explicitly confirm or override types in a dedicated step, enforcing a schema-on-read pattern similar to data lake ingestion workflows.

Non-Destructive Preview

The editor displays a preview of the first 1,000 rows by default. Source data is never modified; transformations are applied in memory and materialized only upon Close & Apply, at which point the final result is loaded into the Power BI data model.

✦

KEY TAKEAWAY

Think of Power Query Editor as a functional build pipeline for data—like a Makefile or a CI/CD pipeline. Each Applied Step is a deterministic stage: given the same input, it always produces the same output. If your source CSV updates overnight, Power Query re-executes every step in sequence, delivering a freshly cleaned table without human intervention.

SECTION 3

Visual Explanation — The Power Query Editor Interface

The Power Query Editor window is divided into several functional regions, each serving a distinct role in the inspect-and-transform workflow. The diagram below maps the five major regions you will interact with most frequently: the Ribbon, the Queries Pane, the Data Preview, the Formula Bar, and the Query Settings / Applied Steps pane.

Region ① provides ribbon tabs (Home, Transform, Add Column, View) that expose GUI-driven transformation commands. Region ② shows the M expression for the currently selected Applied Step. Region ③ lists all queries (tables) in the current project. Region ④ renders a live data preview with optional column-quality indicators. Region ⑤ shows the ordered list of Applied Steps—clicking a step rewinds the preview to that point.

Notice the structural similarity to an IDE: the Queries Pane on the left functions like a project explorer, the Data Preview is your output console, and the Applied Steps pane is a version history. The Formula Bar is the most underused region by beginners—yet it is where you gain direct access to M code and can make edits that no ribbon button exposes. Enabling the Formula Bar via View → Formula Bar should be one of the first things you do whenever you open the editor, because inspecting the generated M code accelerates your learning curve dramatically.

SECTION 4

How Power Query Evaluates Transformations

Under the hood, every query you build in Power Query Editor compiles to a single M expression wrapped in a let … in block. The let section declares a sequence of named expressions (one per Applied Step), and the in clause returns the final expression. Because M is a lazily evaluated, purely functional language, expressions are not computed until their values are actually needed—an optimization strategy directly analogous to Haskell's evaluation semantics.

Anatomy of a Generated M Script

M LET-IN PATTERN

let Source = Csv.Document(\dots), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(\dots), #"Filtered Rows" = Table.SelectRows(#"Changed Type", \dots) in #"Filtered Rows"

Each identifier (e.g., #"Promoted Headers") is the name of an Applied Step. The in clause references the last step, but you could reference any intermediate step to branch or debug. The #"…" quoting syntax is M's way of handling identifiers that contain spaces.

Query Folding — Pushing Computation to the Source

When your data source is a relational database (SQL Server, PostgreSQL, Oracle, etc.), the Power Query engine attempts to translate your Applied Steps into a single SQL statement and execute it server-side—a process called query folding. This is functionally equivalent to the predicate and projection push-down optimizations you study in database query optimization courses. If folding succeeds, only the filtered, projected result set travels over the network; if it fails (e.g., because you invoked a custom M function with no SQL analog), the engine materializes the data locally and applies remaining steps in memory.

FOLDING VERIFICATION

Right-click an Applied Step → "View Native Query" If available → step folds ✓ If grayed out → step breaks folding ✗

A practical heuristic: place all foldable operations (filters, column selection, type changes, renames) before any non-foldable operations to maximize server-side execution. Non-foldable operations are those with no direct SQL equivalent, such as custom M functions or certain text-transformation functions. You will encounter concrete examples of non-foldable steps—including custom columns created via the Add Column ribbon tab—in the Core Transformations section that follows.

The upper path shows query folding in action: foldable M steps are translated into a SQL query and executed on the server, so only the filtered result crosses the network. The lower path shows what happens when a non-foldable step (one with no direct SQL analog, such as a custom M function) breaks folding—the entire table is downloaded and processed by the local Power Query mashup engine.

💡

Performance Tip

In large-scale deployments with millions of rows, broken query folding can inflate refresh times from seconds to minutes. A disciplined approach is to structure your Applied Steps so that all foldable operations—Table.SelectRows, Table.SelectColumns, Table.TransformColumnTypes—appear before any steps that cannot be expressed in SQL. Custom M functions and certain text-manipulation operations applied via the Add Column tab (which you will explore in the next section) are common examples of non-foldable steps. Think of it like pushing predicates below joins in a relational algebra tree.

SECTION 5

Core Transformation Operations

Power Query Editor exposes dozens of transformation commands, but for practical purposes, most data-preparation workflows rely on a handful of core operations. Understanding these operations in terms of their relational algebra or functional programming analogs will help you reason about their behavior and predict the M code they generate. The table below classifies the most-used transforms, links them to their ribbon location, and maps each to the corresponding M function.

Operation	Ribbon Tab	M Function	Relational Analog
Remove Columns	Home	`Table.RemoveColumns`	Projection (π)
Filter Rows	Home	`Table.SelectRows`	Selection (σ)
Sort Rows	Home	`Table.Sort`	ORDER BY
Group By	Transform	`Table.Group`	Aggregation (γ)
Merge Queries	Home	`Table.NestedJoin`	Join (⋈)
Append Queries	Home	`Table.Combine`	Union (∪)
Unpivot Columns	Transform	`Table.UnpivotColumns`	UNPIVOT / melt
Add Custom Column	Add Column	`Table.AddColumn`	Derived attribute
Replace Values	Transform	`Table.ReplaceValue`	UPDATE SET

Common Power Query transforms mapped to their M functions and relational algebra equivalents.

Notice that Add Custom Column lives on the Add Column ribbon tab and uses Table.AddColumn with a user-supplied M expression. Because custom column expressions can reference M functions that have no SQL equivalent—for example, Text.Proper, which title-cases a string—adding a custom column is a common point at which query folding breaks. As noted in the Performance Tip in the previous section, keeping all foldable steps before any custom column steps ensures the maximum amount of work is pushed to the source server.

Data Inspection Tools

Before transforming, you should inspect. Power Query provides three column-level diagnostics accessible from the View ribbon tab. Column Quality shows the percentage of values that are valid, in error, or empty—displayed as a tiny stacked bar atop each column header. Column Distribution renders a histogram of distinct and unique values, which is useful for spotting high-cardinality columns versus categorical ones. Column Profile (available when you select a single column) provides detailed statistics—min, max, mean, standard deviation, value distribution chart—operating over the entire dataset rather than just the preview rows. Toggling "Column profiling based on entire data set" in the status bar ensures these statistics reflect all rows, not just the first 1,000.

Column Quality Indicator Example

0% 100%

SECTION 6

Worked Example — Cleaning a Messy Sales CSV

Suppose you receive a CSV file named raw_sales_2024.csv containing 50,000 rows of transactional data. The file has extraneous header rows, inconsistent date formats, a "Revenue" column stored as text with embedded currency symbols, and null values in the "Region" column. Your goal is to produce a clean table suitable for loading into a Power BI data model. The following walkthrough mirrors the exact sequence of Applied Steps you would create in Power Query Editor.

Cleaning raw_sales_2024.csv in Power Query Editor

1
Step 1 — Connect to Source

In Power BI Desktop, select Home → Get Data → Text/CSV and navigate to the file. Power Query infers delimiters and encoding. Click Transform Data (not "Load") to open the editor. The first Applied Step, Source, is created automatically.

Applied Step: Source = Csv.Document(File.Contents("raw_sales_2024.csv"), …)
2
Step 2 — Promote Headers & Remove Top Rows

The CSV contains two extraneous title rows above the actual column headers. Navigate to Home → Remove Rows → Remove Top Rows and enter 2. Then use Home → Use First Row as Headers to promote the now-first row to column names. This generates two Applied Steps: Removed Top Rows and Promoted Headers.

Columns now display meaningful names: OrderID, Date, Product, Revenue, Region.
3
Step 3 — Change Data Types

Select the Date column, right-click → Change Type → Date. If dates are in a non-US format (e.g., dd/MM/yyyy), use Change Type → Using Locale… to specify the correct culture. Set OrderID to Int64.Type and Product / Region to type text.

M generated: Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"OrderID", Int64.Type}, …})
4
Step 4 — Clean the Revenue Column

Revenue values appear as "$1,234.56". Select the column, then Transform → Replace Values: replace "$" with nothing. Repeat to replace "," with nothing. Finally, change the column type to type number. Alternatively, combine these in the Formula Bar as a single Table.TransformColumns call using Number.FromText(Text.Remove([Revenue], {"$",","})).

Revenue is now a clean numeric column. Column Quality shows 100% valid.
5
Step 5 — Handle Nulls in Region

Enable View → Column Quality and observe that Region shows 8% empty. Navigate to Transform → Replace Values and replace null with "Unknown". In scenarios where business rules allow, you could instead use Home → Remove Rows → Remove Blank Rows or fill down from the previous non-null value using Transform → Fill → Down.

Region column now has 0% empty. Column Quality confirms all five columns are 100% valid. Click Home → Close & Apply to materialize the pipeline and load the cleaned 50,000-row table into the Power BI data model. The Applied Steps pane now shows the complete, ordered log: Source → Removed Top Rows → Promoted Headers → Changed Type → Replaced Value ($) → Replaced Value (,) → Changed Type (Revenue) → Replaced Value (null) — each step a deterministic, replayable record of every transformation applied.

ℹ

Reproducibility Note

Every step above is recorded in the Applied Steps list. If raw_sales_2024.csv is updated next month with new rows, clicking Refresh in Power BI will re-run all five steps automatically, producing a freshly cleaned table without any manual intervention. This is the core value proposition of Power Query: declarative, replayable ETL.

SECTION 7

Strengths, Limitations & Comparisons

Power Query occupies a specific niche in the data-engineering ecosystem: it excels at self-service, GUI-driven ETL for small-to-medium datasets and democratizes data preparation for analysts who may not write Python or SQL. However, it is not a replacement for dedicated orchestration platforms when enterprise-scale pipelines are required. Understanding where Power Query shines and where it falls short will help you make informed architectural decisions.

Dimension	Strength	Limitation
Ease of Use	Fully graphical; every click generates M code. Analysts with zero coding experience can build non-trivial pipelines.	Complex logic (recursive CTEs, advanced string parsing) quickly becomes verbose in M compared to SQL or Python.
Reproducibility	Applied Steps form a deterministic pipeline re-executed on every refresh. Changes are versioned within the .pbix file.	No native Git integration for M scripts. Diffing changes requires exporting the .pbix or using third-party tools (e.g., pbi-tools).
Performance	Query folding can push heavy computation to the source RDBMS, minimizing data transfer.	If folding breaks, the entire dataset is pulled into local memory. Datasets exceeding available RAM will fail.
Connectors	100+ built-in connectors: SQL, Oracle, REST APIs, SharePoint, web pages, OData, JSON, Parquet, and more.	Some connectors lack full-featured query folding. Web-based sources always evaluate locally.
Scalability	Dataflows offload processing to the cloud (Power BI Service or Fabric), enabling shared, reusable queries across reports.	Still single-threaded per query at evaluation time. Not designed for petabyte-scale transformations (use Spark/Fabric notebooks instead).

Strengths and limitations of Power Query Editor for data preparation.

✦

KEY TAKEAWAY

Power Query is the "right tool for the right job" when the job is preparing datasets of moderate size (up to tens of millions of rows) for analytical models. For anything requiring distributed compute, real-time streaming, or complex orchestration with branching logic, you would reach for Apache Spark, dbt, or Azure Data Factory—often feeding their outputs back into Power BI via a connector. Think of Power Query as the last-mile data preparation layer rather than a full data-engineering platform.

SECTION 8

Connection to Advanced Theory & Ecosystem

Power Query Editor is the entry point into a broader continuum of data-transformation technologies within the Microsoft ecosystem and beyond. As your projects grow in complexity, you will encounter scenarios where the Desktop-local Power Query engine is insufficient and you need to graduate to cloud-scale or code-first alternatives. The table below maps Power Query concepts to their advanced counterparts, helping you see how the skills you develop here transfer directly.

Power Query Concept	Advanced Equivalent	When to Graduate
Applied Steps (M script)	dbt models (SQL + Jinja), Spark DataFrame transformations	When you need version-controlled, CI/CD-tested transformation logic across teams
Query Folding to SQL	Predicate push-down in Spark Catalyst optimizer; query delegation in Presto/Trino	When data volumes exceed single-node capacity
Power BI Dataflows	Azure Data Factory Mapping Data Flows; Fabric Pipelines	When you need orchestration, scheduling, monitoring, and alerting at enterprise scale
Merge Queries (Join)	Broadcast / sort-merge joins in Spark; hash joins in query engines	When join cardinalities exceed local memory
Parameters & Functions in M	Parameterized SQL templates; Python/Scala UDFs in Spark	When reuse patterns demand a library of shared transformation logic

Mapping Power Query concepts to their advanced ecosystem equivalents.

An important advanced feature within Power Query itself is the ability to define custom M functions and invoke them across multiple queries. For instance, if you need to apply the same cleaning logic to twelve monthly CSVs, you can encapsulate the transformation in a function, then use Table.AddColumn with Function.Invoke to apply it uniformly. This pattern anticipates the reusable transformation modules you would build in dbt or Spark, and mastering it within Power Query gives you a strong conceptual foundation for those tools.

📝

Looking Ahead: Microsoft Fabric

Microsoft Fabric unifies Power BI, Azure Data Factory, and Azure Synapse into a single platform. Power Query remains the default ingestion interface for Fabric lakehouses, but you can seamlessly switch to Spark notebooks for heavy transformations. The M-to-Spark handoff is becoming increasingly fluid, so the declarative thinking you develop in Power Query transfers directly to distributed-compute contexts.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain the relationship between the Applied Steps pane in Power Query Editor and the M code displayed in the Formula Bar. Why is the term "immutable log" an appropriate description for the Applied Steps list?

Show answer

Each Applied Step corresponds to a named expression in the let … in block of the generated M script. When you click a step, the Formula Bar shows that step's M expression, and the Data Preview shows the table's state after that step executes. The term "immutable log" is appropriate because steps are appended sequentially rather than modified in place—deleting or inserting a step creates a new version of the pipeline rather than mutating the existing one. This design mirrors the commit log in version-control systems: each step captures a deterministic transformation, and replaying the entire sequence from Source to the final step always produces the same output given the same input data.

Question 2: BASIC CALCULATION

You have a Power Query table with 200,000 rows and a column named "Price" stored as text with values like "€1.234,56" (European format: period as thousands separator, comma as decimal). Write the M expression for a custom column that converts this to a standard numeric value.

Show answer

You can use Table.AddColumn(PreviousStep, "CleanPrice", each Number.FromText(Text.Remove([Price], {"€", "."}), "de-DE")). First, Text.Remove strips the euro sign and thousand-separator periods. Then Number.FromText with the German locale ("de-DE") interprets the comma as a decimal separator, correctly parsing "1234,56" into the number 1234.56. Alternatively, you could chain Text.Replace calls to swap commas for periods and then cast using Number.From, but the locale-aware approach is more robust and handles edge cases gracefully.

Question 3: INTERMEDIATE

A colleague has built a Power Query pipeline against a SQL Server database. The first three Applied Steps (Source, Navigation, Filter Rows) all fold successfully—verified via "View Native Query." The fourth step adds a custom column using Text.Proper([CustomerName]), and "View Native Query" is grayed out for that step. Explain what happened and propose a restructuring strategy that preserves the custom column while maximizing folding.

Show answer

The Text.Proper function has no direct SQL equivalent, so the Power Query engine cannot translate that step into SQL. Query folding breaks at step 4, meaning all subsequent steps also evaluate locally. However, the first three steps still fold—so at step 3, a SQL query with the WHERE clause executes on the server, and only the filtered result set is downloaded. The optimization strategy is to ensure all foldable operations (additional filters, column removal, type changes, renames) are placed before step 4. If CustomerName title-casing is not strictly needed in the data model, consider handling it in a DAX measure or calculated column instead, preserving full folding. Alternatively, if the database supports it, create a computed column or view on the server that applies proper casing, then reference that view as the Power Query source.

Question 4: APPLIED

You receive twelve CSV files (Jan.csv through Dec.csv) with identical schemas: columns Date, Product, Units, Revenue. Design a Power Query solution that (a) loads all twelve files into a single table, (b) adds a "Month" column derived from the file name, (c) removes rows where Revenue is null, and (d) changes Revenue to a decimal number type. Describe the sequence of queries and Applied Steps you would create.

Show answer

Place all twelve CSVs in a single folder. In Power BI Desktop, use Home → Get Data → Folder and point to that directory. Power Query creates a query listing all files with metadata columns including Name (the file name). Click "Combine & Transform Data" to auto-generate a helper function that reads each CSV with consistent settings. This produces a combined table with all rows and an auto-added "Source.Name" column. Next, add a custom column: Text.BeforeDelimiter([Source.Name], ".") to extract "Jan", "Feb", etc. Rename this column to "Month". Then apply Table.SelectRows to filter out rows where Revenue is null: each [Revenue] <> null. Finally, change the Revenue column type to type number. The Applied Steps will be: Source (Folder), Filtered Hidden Files, Invoke Custom Function, Expanded Table Column, Changed Type, Added Custom (Month), Removed Columns (Source.Name), Filtered Rows (null removal), Changed Type (Revenue to decimal). This pattern—folder-based ingestion with a parameterized function—scales automatically: adding a thirteenth file requires only dropping it in the folder and refreshing.

Question 5: CRITICAL THINKING

Power Query's M language is described as "purely functional" with lazy evaluation. Compare and contrast M's evaluation model with Haskell's lazy evaluation and Python/pandas' eager evaluation. In what scenarios does M's laziness provide a tangible performance advantage, and when might it introduce unexpected behavior during debugging in Power Query Editor?

Show answer

Like Haskell, M defers computation until a value is demanded by a downstream consumer. This means that if you define ten Applied Steps but only the final step is referenced in the in clause, intermediate expressions that are not part of the dependency chain are never evaluated—similar to how an unreferenced Haskell thunk remains unevaluated. This is a tangible advantage when combined with query folding: M can compose multiple lazy transformations and then fold the entire chain into a single SQL query, minimizing round-trips. In contrast, pandas executes each transformation eagerly and materializes intermediate DataFrames in memory, which can cause memory spikes for large datasets. However, M's laziness can produce unexpected behavior during debugging: clicking an intermediate Applied Step forces evaluation of that step and all its dependencies, but the evaluation context may differ from the final pipeline if later steps modify parameters or error-handling paths. Additionally, errors in lazily evaluated branches may not surface until a downstream step triggers their evaluation, making root-cause analysis harder than in Python where exceptions surface immediately at the offending line. The Power Query Editor partially mitigates this by showing a live preview at each step, but the preview operates on only the first 1,000 rows by default, meaning lazy-evaluation bugs involving rows beyond the preview window may go undetected until a full refresh.

SUMMARY

Summary

The Power Query Editor is Power BI's built-in ETL engine that records every data-cleaning action as an Applied Step in a deterministic, replayable pipeline powered by the M language—a purely functional, lazily evaluated transformation language. Its five interface regions (Ribbon, Formula Bar, Queries Pane, Data Preview, and Query Settings) give you full control over inspecting source data, applying transformations, and auditing every change. Key inspection tools—Column Quality, Column Distribution, and Column Profile—surface data-quality issues before you begin cleaning, while core transforms such as filter, merge, unpivot, group, and replace map directly to relational algebra operations you already know from database coursework.

Performance-critical pipelines benefit from query folding, which translates M steps into native SQL and pushes computation to the source server—analogous to predicate push-down in query optimizers. When folding breaks, the engine falls back to local evaluation, so step ordering matters. As datasets grow beyond single-node capacity, the same mental model transfers to Dataflows, Azure Data Factory, and Spark-based platforms within Microsoft Fabric. Mastering Power Query Editor equips you with a portable, declarative approach to data preparation that scales from quick ad-hoc analyses to enterprise BI deployments.