SECTION 1
Historical Context & Motivation
Before the rise of self-service business intelligence platforms, data preparation was almost exclusively the domain of database administrators and ETL engineers who wrote complex SQL scripts, stored procedures, and SSIS packages to cleanse, reshape, and load enterprise data into warehouses. Analysts who needed a quick column split or a filtered subset of rows had to submit change requests or resort to fragile spreadsheet macros. This bottleneck—where the people closest to the business questions were furthest from the data-shaping tools—motivated Microsoft to develop Power Query, a graphical yet code-backed transformation engine that places ETL capabilities directly in the hands of analysts and data scientists. Understanding the lineage of this tool helps contextualize why its design favors composable, step-by-step transformations rather than monolithic scripts.
2010 • Power Pivot Launches
2013 • Power Query Preview for Excel
2015 • Power BI Desktop Ships
2018–Present • Dataflows & Power Query Online
The central question these developments address is straightforward yet consequential: how can an analyst reproducibly clean messy, real-world data without writing raw SQL or procedural code, while still maintaining an auditable, editable transformation log? The four operations we examine in this lesson—filtering rows, removing columns, splitting columns, and replacing values—form the irreducible core of that workflow.
SECTION 2
Core Principles & Definitions
Power Query's transformation model rests on a few design principles borrowed from functional programming: every transformation is a pure function that takes a table (or a scalar) as input and returns a new table (or scalar) as output, with no side effects. The editor records each GUI action as an Applied Step—an M-language expression appended to an ordered list. Because each step references the output of the previous step by a let … in binding, the entire pipeline is deterministic and replayable. This functional, step-chain architecture makes Power Query transformations composable, auditable, and safe to reorder or delete without the cascading breakage typical of imperative ETL scripts.
Filter Rows
WHERE clause. Power Query generates Table.SelectRows(source, each [Column] = value) in M.Remove Columns
SELECT list. M function: Table.RemoveColumns(prev, {"Col"}).Split Column
Table.SplitColumn(prev, "Col", Splitter.SplitTextByDelimiter(",")).Replace Values
Table.ReplaceValue(prev, "old", "new", Replacer.ReplaceText, {"Col"}).KEY TAKEAWAY
SECTION 3
Visual Explanation — The Transformation Pipeline
Notice that the pipeline is strictly linear: each step's output becomes the next step's input, and the Applied Steps pane on the right side of the Power Query Editor mirrors this chain exactly. If you click on any intermediate step, the data preview refreshes to show the table's state at that point—effectively a time-travel debugger for your data pipeline. This deterministic, inspectable design is what makes Power Query transformations so valuable for reproducible data engineering, and it maps directly onto the functional-programming concept of function composition, where f(g(h(x))) is evaluated inside-out with each function producing an immutable intermediate result.
SECTION 4
How It Works — The M Language Under the Hood
Every GUI click in the Power Query Editor generates an M expression (Power Query Formula Language). M is a functional, dynamically typed, case-sensitive language with lazy evaluation semantics—expressions are only evaluated when their results are needed. Understanding the generated M is essential for debugging, parameterizing queries, and performing transformations that go beyond the GUI's capabilities. Below we dissect the M expression that each of our four transformations produces.
Filter Rows — Table.SelectRows
M EXPRESSION — FILTER ROWS
The each keyword is syntactic sugar for an anonymous function (_) => _[Status] = "Active". The underscore _ represents the current row record. The function returns true or false, and only rows evaluating to true are retained.
Remove Columns — Table.RemoveColumns
M EXPRESSION — REMOVE COLUMNS
The second argument is a list (denoted by curly braces in M) of column names to drop. The complementary function Table.SelectColumns retains only the listed columns instead—useful when you need a whitelist rather than a blacklist approach.
Split Column — Table.SplitColumn
M EXPRESSION — SPLIT COLUMN
The Splitter.SplitTextByDelimiter factory returns a function that tokenizes each cell's text at the specified delimiter. The final list argument names the resulting columns. Alternative splitters include Splitter.SplitTextByEachDelimiter, Splitter.SplitTextByPositions, and Splitter.SplitTextByCharacterTransition.
Replace Values — Table.ReplaceValue
M EXPRESSION — REPLACE VALUES
The fourth argument specifies the replacement strategy. Replacer.ReplaceValue performs an exact match (suitable for null, numeric, or exact-text replacements), while Replacer.ReplaceText performs a substring match (useful for fixing partial text like replacing "NY" with "New York" inside a longer string).
The let…in Pattern
let block. Each step is a named binding: let Source = Csv.Document(...), Filtered = Table.SelectRows(Source, ...), Removed = Table.RemoveColumns(Filtered, ...) in Removed. The in clause specifies which binding is the query's final output. If you have experience with Haskell's let…in or F#'s pipelines, M's semantics will feel familiar.SECTION 5
Detailed Breakdown — Transformation Options & Variants
Each of the four core transformations has multiple variants exposed through the Power Query Editor's ribbon and context menus. Choosing the right variant depends on data types, data quality, and downstream modeling requirements. The diagram below classifies these variants in a decision-tree format, and the table that follows provides a quick-reference comparison.
| Transformation | M Function | SQL Analogy | Affects |
|---|---|---|---|
| Filter Rows | Table.SelectRows | WHERE | Row count (cardinality) |
| Remove Columns | Table.RemoveColumns | Omitting from SELECT | Column count (degree) |
| Split Column | Table.SplitColumn | SUBSTRING / CHARINDEX | Column count (increases degree) |
| Replace Values | Table.ReplaceValue | CASE WHEN / REPLACE | Cell values (no structural change) |
Comparison of the four transformations with their M functions, SQL equivalents, and structural effects.
SECTION 6
Worked Example — Cleaning a Sales Dataset
Suppose you receive a CSV export from a legacy CRM system containing 12 columns and 50,000 rows of sales transactions. The dataset has several quality issues: inactive customer records are mixed with active ones, there are columns like "InternalAuditCode" and "LegacyID" that are irrelevant to your analysis, the "Product-Region" column contains concatenated values like "Widget-EMEA", and the "PaymentMethod" column uses inconsistent labels ("CC" instead of "Credit Card"). We will walk through all four transformations to produce a clean, analysis-ready table.
End-to-End Data Cleaning Pipeline
- 1
Step 1 — Filter Rows to Active Customers
In the Power Query Editor, click the dropdown arrow on the CustomerStatus column header. Uncheck "Inactive" and "Suspended" from the value checklist, leaving only "Active" selected. Alternatively, use Home → Remove Rows → Remove Blank Rows to first strip rows with null keys. The generated M is:= Table.SelectRows(Source, each [CustomerStatus] = "Active"). This reduces the dataset from 50,000 rows to approximately 38,000 rows, depending on data.50,000 → ~38,000 rows (inactive/suspended records removed) - 2
Step 2 — Remove Irrelevant Columns
Hold Ctrl and click on the column headers for "InternalAuditCode", "LegacyID", "CustomerStatus" (no longer needed since all remaining rows are Active), and "ModifiedTimestamp". Right-click and select Remove Columns. The M expression is:= Table.RemoveColumns(FilteredRows, {"InternalAuditCode", "LegacyID", "CustomerStatus", "ModifiedTimestamp"}). This trims the table from 12 columns to 8, reducing memory consumption and downstream model complexity.12 → 8 columns (4 irrelevant fields dropped) - 3
Step 3 — Split the Product-Region Column
Select the "Product-Region" column. Navigate to Transform → Split Column → By Delimiter. In the dialog, set the delimiter to the custom value "-" and choose "At the left-most delimiter" (since product names themselves might contain hyphens, we want only the first split). Name the new columns "Product" and "Region". The generated M is:= Table.SplitColumn(RemovedColumns, "Product-Region", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.None, false), {"Product", "Region"}). After splitting, set the data types of both new columns to Text.8 → 9 columns ("Product-Region" becomes "Product" and "Region") - 4
Step 4 — Replace Inconsistent Payment Labels
Select the "PaymentMethod" column. Go to Transform → Replace Values. Enter "CC" in the "Value to Find" box and "Credit Card" in "Replace With". Click OK. Repeat for "BT" → "Bank Transfer" and "PP" → "PayPal". Each replacement generates a separate Applied Step. The first M expression is:= Table.ReplaceValue(SplitColumn, "CC", "Credit Card", Replacer.ReplaceValue, {"PaymentMethod"}). For efficiency, you could consolidate these into a single custom M step usingList.Accumulate, but the GUI approach is perfectly acceptable for three replacements.All abbreviated payment codes standardized to full descriptive labels - 5
Step 5 — Verify and Close & Apply
Review the Applied Steps pane to confirm all four transformations appear in the correct order. Click on each step to inspect intermediate results. Verify data types are correct (especially after the split). Finally, click Close & Apply in the Home ribbon to load the cleaned table into the Power BI data model. The entire pipeline will replay automatically on every future data refresh.Clean table: 9 columns × ~38,000 rows, fully reproducible on refresh
SECTION 7
Strengths, Limitations & GUI vs. M Trade-offs
Power Query's GUI-first approach to these four transformations offers significant productivity advantages, but it also introduces constraints that become apparent as transformations grow in complexity. The table below compares the GUI workflow against hand-written M code across several dimensions that matter in production data pipelines.
| Dimension | GUI Approach | Hand-Written M |
|---|---|---|
| Learning Curve | Minimal — point-and-click interface with immediate data preview | Moderate — requires understanding M syntax, types, and lazy evaluation |
| Expressiveness | Limited to pre-built ribbon options; complex predicates require switching to Advanced Editor | Full M language available: custom functions, recursion, error handling, parameterization |
| Auditability | Good — Applied Steps pane provides step-by-step inspection | Excellent — code is version-controllable and can be reviewed in PRs |
| Batch Replacements | One replace at a time — generates N steps for N replacements | Can use List.Accumulate or a lookup table for bulk replacements in a single step |
| Schema Resilience | Fragile if source columns are renamed; errors break downstream steps | Can use try…otherwise and dynamic column detection for robust pipelines |
| Query Folding | Supported for standard operations on foldable sources (SQL Server, OData) | Same folding rules apply; custom M functions may break the fold |
GUI vs. hand-written M comparison for the four basic transformations
KEY TAKEAWAY
SECTION 8
Connection to Advanced Power Query & Data Engineering
The four transformations covered in this lesson are the building blocks for a much richer set of Power Query capabilities. As your data pipelines mature—handling multiple sources, slowly changing dimensions, or incremental refresh patterns—you will encounter advanced M constructs that extend these basics. The table below maps each basic operation to its advanced counterpart, giving you a roadmap for continued learning.
| Basic Operation | Advanced Extension | Use Case |
|---|---|---|
| Filter Rows | Parameterized filters (Table.SelectRows with dynamic values from query parameters or other tables) | Incremental refresh: filter to only the last N days of data, where N is a parameter |
| Remove Columns | Dynamic column selection (Table.SelectColumns with programmatically generated column lists via Table.ColumnNames) | Handling schemas that change over time (e.g., new columns added monthly to a SharePoint list) |
| Split Column | Custom parsing with Text.BetweenDelimiters, regex-like Splitter.SplitTextByCharacterTransition, and JSON/XML record expansion | Parsing semi-structured API responses, log files, or embedded JSON columns |
| Replace Values | Conditional columns (Table.AddColumn with if…then…else), lookup-table-driven replacements via Table.NestedJoin | Mapping codes to descriptive labels maintained in a separate reference table—a standard data warehousing pattern |
Basic to advanced transformation roadmap
Beyond Power BI Desktop, Power Query now runs in Microsoft Fabric Dataflows Gen2 and Azure Data Factory Wrangling Data Flows, where the same M transformations execute at cloud scale against data lake storage. If you continue into data engineering or MLOps, you will find that the conceptual patterns—predicate-based row filtering, projection, string decomposition, and value normalization—reappear in every ETL framework, from Apache Spark's DataFrame API to dbt's SQL transformations. Mastering them here in Power Query gives you a transferable mental model for data preparation regardless of the toolchain.
SECTION 9
Practice Problems
Question 1: CONCEPTUAL
Show answer
Question 2: BASIC
Show answer
Filtered = Table.SelectRows(Source, each [ArchiveFlag] = false) followed by Cleaned = Table.RemoveColumns(Filtered, {"InternalNotes", "ArchiveFlag"}). The filter uses a predicate function expressed with the each keyword, which is syntactic sugar for (_) => _[ArchiveFlag] = false. We remove ArchiveFlag after filtering because it becomes a constant (all false) and carries no analytical value. Removing InternalNotes reduces model size and keeps the downstream schema focused.Question 3: INTERMEDIATE
Show answer
= Table.SplitColumn(PreviousStep, "FullAddress", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"StreetAddress", "City", "State", "ZipCode"}). We use Splitter.SplitTextByDelimiter which splits at every occurrence of the pipe character, producing four segments. Choosing "at the left-most delimiter" (i.e., Splitter.SplitTextByEachDelimiter with a single delimiter) would produce only two columns—everything before the first pipe and everything after—which is useful when the right portion may itself contain the delimiter, but not what we want here. Since the pipe character is not expected to appear within any individual field, splitting at every occurrence is both safe and correct, yielding exactly four columns from three delimiters.Question 4: APPLIED
Show answer
Question 5: CRITICAL THINKING
Show answer
Table.ColumnNames, set operations, and try…otherwise. First, define the required columns as a list: RequiredCols = {"OrderID", "Date", "Amount"}. Then, compute the actual column names dynamically: ActualCols = Table.ColumnNames(Source). Check for missing required columns using MissingCols = List.Difference(RequiredCols, ActualCols). If MissingCols is non-empty, you can either raise a structured error with error "Missing columns: " & Text.Combine(MissingCols, ", ") or add placeholder columns with Table.AddColumn returning null. To retain unknown new columns, simply do not filter them out—leave Table.SelectColumns on the union of RequiredCols and ActualCols: Table.SelectColumns(Source, List.Union({RequiredCols, ActualCols})) effectively keeps everything but reorders required columns first. This approach embodies defensive programming: it fails loudly on missing requirements but gracefully accepts schema extensions, balancing robustness with forward-compatibility.SUMMARY
Summary
Summary
Power Query's four foundational transformations provide a complete toolkit for reshaping raw data into analysis-ready tables. Filter Rows uses Table.SelectRows to apply row-level predicates that reduce cardinality, analogous to a SQL WHERE clause. Remove Columns employs Table.RemoveColumns to project away unneeded fields, minimizing memory footprint and model complexity. Split Column leverages Table.SplitColumn with various Splitter functions to decompose concatenated text into properly normalized fields. Replace Values applies Table.ReplaceValue for cell-level substitutions that standardize labels, fix typos, and handle nulls.
All four transformations are recorded as Applied Steps in the M language, forming a deterministic, inspectable, and replayable pipeline modeled on functional composition. Step ordering matters for performance: filter and remove early to minimize data volumes before expensive operations like splits and replacements, especially when query folding can push computation to a source database. These four operations form the irreducible core of data preparation in Power BI and transfer directly to advanced M patterns, cloud-scale Dataflows, and the broader discipline of data engineering.