Historical Context & Motivation
Data analysts have long needed the ability to derive new columns from existing data during the extract-transform-load (ETL) process. Before purpose-built tools existed, transformations were typically coded in SQL views, Python scripts, or even manual spreadsheet formulas—approaches that, while powerful, introduce maintenance overhead, version-control challenges, and reproducibility issues. Power Query and its underlying language M were designed by Microsoft to address this gap: a declarative, functional data-preparation engine that operates entirely within the BI tool, producing an auditable sequence of transformation steps that non-engineers can inspect and modify through a graphical interface while still offering full programmatic expressiveness under the hood.
The central question that custom columns answer is straightforward yet critical: How do we produce new, derived data attributes at query time without modifying the source system? Understanding the M expressions that drive custom columns is essential for any data professional who wants full control over the Power Query transformation pipeline rather than relying solely on the GUI's point-and-click capabilities.
Core Principles & Definitions
Before writing your first custom column, it helps to ground yourself in the design philosophy of M. M (officially called the Power Query Formula Language) is a functional, dynamically-typed, case-sensitive language that evaluates expressions lazily. Every transformation step in Power Query—whether generated by the UI or typed by hand—is ultimately an M expression. A custom column is simply a Table.AddColumn invocation where the third argument is a function that computes a value for each row.
Functional Evaluation
Row Context via each / _
each is syntactic sugar for (_) =>. Inside a custom column expression, _ refers to the current row record.Field Access with [ ]
[ColumnName]. This is equivalent to _[ColumnName] when inside an each block.Type System
Lazy Evaluation & Query Folding
list.map(lambda row: row['price'] * row['qty']) in Python applies a function to every element of a list, Table.AddColumn(source, "Total", each [Price] * [Qty]) applies a function to every row record of a table. The result is a new table with the appended column—the original table is never mutated.Visual Explanation — The Custom Column Pipeline
Observe that the source table remains immutable throughout the operation—M returns a brand-new table reference with the additional column. This aligns with the functional-programming principle of referential transparency: any step can be replaced by its result without altering program behavior. The expression each [Price] * 1.10 is desugared by the engine to the anonymous function (_) => _[Price] * 1.10, where _ binds to each successive row record. Because records in M are structurally typed, the engine verifies at evaluation time that the field Price exists; if it does not, M raises an Expression.Error rather than silently returning null.
How Table.AddColumn Works Under the Hood
Every custom column ultimately invokes the library function Table.AddColumn. Understanding its signature is key to writing expressions beyond what the GUI dialog can generate.
type number), which helps the engine optimize downstream operations.The each / _ Desugaring
The each keyword is not special syntax recognized by the parser in some ad-hoc way; it is formally equivalent to a lambda expression where the parameter name is the underscore character. The following two forms are semantically identical:
Col field plus 10. When each is used, field access without a record prefix implicitly references _.Conditional Logic (if-then-else)
Table.AddColumn. You can nest if expressions for multi-branch logic akin to CASE in SQL.Null Handling with the Null Coalescing Operator
?? operator returns the left operand if it is non-null; otherwise it returns the right operand. This is analogous to Python's idiom x if x is not None else default or SQL's COALESCE.Common Custom Column Expression Patterns
While the grammar of M permits arbitrarily complex expressions, most real-world custom columns fall into a handful of recurring patterns. The following classification covers the patterns you will encounter most frequently when preparing data in Power Query. Each pattern maps to an analogous construct in languages you already know, which should accelerate your fluency.
| Pattern | M Example | Python Analogue |
|---|---|---|
| Arithmetic | each [Qty] * [Price] | df['Qty'] * df['Price'] |
| String concat | each [First] & " " & [Last] | df['First'] + ' ' + df['Last'] |
| Conditional | each if [X]>0 then "Pos" else "Non" | np.where(df['X']>0, 'Pos', 'Non') |
| Date extraction | each Date.Month([D]) | df['D'].dt.month |
| Null coalesce | each [X] ?? 0 | df['X'].fillna(0) |
| Error handling | each try [A]/[B] otherwise null | try/except per row or np.where |
Worked Example — Categorizing Orders by Revenue Tier
Suppose you have an Orders table with columns OrderID, Quantity (integer), and UnitPrice (decimal). Your task is to create two custom columns: LineTotal (arithmetic) and RevenueTier (conditional classification into "High", "Medium", or "Low").
Orders query in the Queries pane. Verify that Quantity is typed as Int64.Type and UnitPrice as Currency.Type or type number. If types are incorrect, Power Query may silently coerce values during multiplication.LineTotal and enter the expression [Quantity] * [UnitPrice]. Click OK. Power Query inserts a new step in the Applied Steps list.= Table.AddColumn(#"Previous Step", "LineTotal", each [Quantity] * [UnitPrice], type number)let step after the LineTotal step. Write a nested if-then-else expression that classifies each order based on its LineTotal value.each if [LineTotal] >= 1000 then "High" else if [LineTotal] >= 250 then "Medium" else "Low". Note that M evaluates conditions top-down, so the ordering matters—place the most restrictive condition first. We pass type text as the fourth argument to declare the column type explicitly.AddedTier = Table.AddColumn(AddedLineTotal, "RevenueTier", each if [LineTotal] >= 1000 then "High" else if [LineTotal] >= 250 then "Medium" else "Low", type text)try ... otherwise to handle edge cases gracefully.Custom Columns vs. Calculated Columns vs. Measures
A frequent source of confusion for Power BI newcomers is distinguishing among the three places you can create derived fields: custom columns in Power Query (M), calculated columns in the data model (DAX), and measures (DAX). Each operates at a different stage of the data lifecycle and is optimized for different use cases. The table below clarifies the key distinctions.
| Dimension | Custom Column (M) | Calculated Column (DAX) | Measure (DAX) |
|---|---|---|---|
| Evaluation time | During data refresh (ETL) | After load, during model processing | At query time (report interaction) |
| Language | M (Power Query Formula Language) | DAX | DAX |
| Row context | each row record via each | Implicit row context in the model table | No inherent row context; uses filter context |
| Storage | Materialized in the model like any other column | Materialized in the model | Computed on the fly; not stored |
| Can reference other tables? | Only via merges/joins in Power Query | Yes, via RELATED / RELATEDTABLE | Yes, via any DAX function |
| Best for | Data cleaning, normalization, row-level derivation | Columns needing cross-table relationships | Aggregations, KPIs, dynamic calculations |
Connection to Advanced M Techniques
The introductory custom-column patterns covered so far—arithmetic, conditionals, text functions—only scratch the surface of what M can express. As you gain fluency, you will encounter scenarios that require more sophisticated language features. This section briefly maps the introductory concepts to their advanced counterparts so you know where the learning path leads.
| Intro Concept | Advanced Extension |
|---|---|
each [Col] * 2 | Custom functions with let...in blocks inside Table.AddColumn for multi-step per-row computations |
Simple if-then-else | Pattern matching with List.Contains, Record.FieldValues, or lookup tables via Table.Join |
| Scalar column results | Returning lists or records from custom columns (structured columns), enabling hierarchical data expansion |
| Hardcoded type in 4th arg | Dynamic type ascription via Value.ReplaceType and custom type definitions for schema enforcement |
try ... otherwise | Full error record inspection (try returns [HasError, Value, Error] record), enabling granular error-routing logic |
CASE WHEN or computed-column SQL. However, calling M-only library functions (e.g., Text.BetweenDelimiters) will break the fold, forcing the engine to download raw data and compute locally. You can right-click any step and select View Native Query to verify whether folding is still active.Practice Problems
each [Price] * [Qty] is equivalent to (_) => _[Price] * _[Qty]. What role does the underscore play, and why does M offer each as syntactic sugar?HoursWorked (number) and HourlyRate (number), write the complete Table.AddColumn expression to create a GrossPay column. Include a type annotation.FullName column by concatenating [FirstName] and [LastName], but handles the case where either field may be null by substituting an empty string. Use the ?? operator.Employees table with a HireDate column (type date). Write a custom-column expression that computes YearsOfService as a whole number, calculated as the difference in years between DateTime.LocalNow() and [HireDate]. Consider that the result should be an integer (floor of the duration in years).each Text.BetweenDelimiters([RawAddress], "|", "|") to a query sourced from a SQL Server database. They notice the query now takes 10× longer. Diagnose the likely cause, explain how you would confirm your hypothesis, and propose an alternative approach that preserves query folding.Lesson Summary
Custom columns in Power Query let you derive new data attributes at refresh time using M expressions passed to Table.AddColumn. The each keyword provides concise row-level lambdas where [ColumnName] accesses fields on the current row record. Common patterns include arithmetic operations, text manipulation, conditional branching via if-then-else, date/time extraction, and null/error handling with ?? and try-otherwise.
Custom columns differ from DAX calculated columns (which operate post-load in the data model) and DAX measures (which compute at query time). The recommended practice is to push row-level, single-table derivations into the M layer to benefit from query folding and keep the DAX model lean. Always provide an explicit type annotation in the fourth argument of Table.AddColumn to avoid unnecessary type-detection overhead during data load.