Custom Columns in M — Create custom columns using M expressions (intro) - Microsoft Power Bi

SECTION 1

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.

2010 • Power Query Origins ("Data Explorer")

Microsoft Research prototyped "Data Explorer," an Excel add-in for self-service data preparation, introducing the concept of step-based transformations recorded as M code.

2013 • Power Query for Excel GA

Power Query shipped as an official Excel add-in, exposing the M language to mainstream users. Custom columns became a first-class feature, letting analysts write arbitrary M expressions per row.

2015 • Power BI Desktop Launch

Microsoft released Power BI Desktop with Power Query integrated natively. The "Add Custom Column" dialog became one of the most-used transformation steps in the tool.

2018–Present • Dataflows & M in the Cloud

Power Query expanded beyond the desktop: Dataflows in Power BI Service and Azure Data Factory adopted M, making custom-column logic shareable and schedulable at enterprise scale.

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.

SECTION 2

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

M treats every step as a pure expression. Custom column formulas receive the current row as input and must return a single value—no side effects, no state mutation.

Row Context via each / _

The keyword each is syntactic sugar for (_) =>. Inside a custom column expression, _ refers to the current row record.

Field Access with [ ]

Columns are accessed via record-field syntax: [ColumnName]. This is equivalent to _[ColumnName] when inside an each block.

Type System

M values carry types at runtime (number, text, logical, date, record, list, table, etc.). Custom column results inherit their type from the expression's return value unless you explicitly ascribe a type.

Lazy Evaluation & Query Folding

M evaluates steps lazily—computation only occurs when a downstream consumer requests data. If the source connector supports it, custom-column logic may fold back into a native query (e.g., SQL), dramatically improving performance.

✦

KEY TAKEAWAY

Think of a custom column expression as a pure function passed through a map operation. Just as 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.

SECTION 3

Visual Explanation — The Custom Column Pipeline

The diagram shows three stages of the custom-column pipeline: the source table on the left, the M expression applied per row in the center, and the result table with the new column appended on the right. The bottom panel reveals the underlying M code that Power Query generates.

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.

SECTION 4

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.

TABLE.ADDCOLUMN SIGNATURE

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as type) as table

table — the input table (previous step reference). newColumnName — a text literal for the column header. columnGenerator — a function(record) → value applied to each row. columnType — optional type ascription (e.g., 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:

EACH DESUGARING

each [Col] + 10 ≡ (_) => _[Col] + 10

Both define an anonymous function accepting a single record argument and returning the value of its Col field plus 10. When each is used, field access without a record prefix implicitly references _.

Conditional Logic (if-then-else)

CONDITIONAL EXPRESSION

each if [Revenue] > 100000 then "High" else "Standard"

M's conditional is an expression (not a statement), so it always returns a value. This makes it safe inside Table.AddColumn. You can nest if expressions for multi-branch logic akin to CASE in SQL.

Null Handling with the Null Coalescing Operator

NULL COALESCING

each [MiddleName] ?? "N/A"

The ?? 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.

SECTION 5

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.

Six common expression pattern families for custom columns in M. The bottom panel shows a combined pattern that mixes conditional logic, date extraction, text manipulation, arithmetic, and error handling in a single expression.

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`

Side-by-side comparison of M custom-column patterns with pandas equivalents

SECTION 6

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").

Adding LineTotal and RevenueTier Custom Columns

1
Step 1 — Load and Inspect the Source

Open Power Query Editor and locate the 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.
2
Step 2 — Add the LineTotal Column (GUI path)

Click Add Column → Custom Column. In the dialog, set the new column name to LineTotal and enter the expression [Quantity] * [UnitPrice]. Click OK. Power Query inserts a new step in the Applied Steps list.

Generated M step: = Table.AddColumn(#"Previous Step", "LineTotal", each [Quantity] * [UnitPrice], type number)
3
Step 3 — Add the RevenueTier Column (manual M)

Open the Advanced Editor (View → Advanced Editor) and append a new let step after the LineTotal step. Write a nested if-then-else expression that classifies each order based on its LineTotal value.
4
Step 4 — Write the Conditional Expression

The expression is: 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)
5
Step 5 — Validate Results

Return to the table preview and spot-check several rows. An order with Quantity = 50 and UnitPrice = 22.50 should show LineTotal = 1125.00 and RevenueTier = "High". An order with Quantity = 5 and UnitPrice = 10.00 should show LineTotal = 50.00 and RevenueTier = "Low". If you see errors, check for null values in the source columns and consider wrapping the expression with try ... otherwise to handle edge cases gracefully.

Final query produces three additional columns: LineTotal (number) and RevenueTier (text)—appended non-destructively to the original table.

SECTION 7

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

Comparison of the three derivation mechanisms in Power BI

⚡

WHEN TO USE WHICH

A practical heuristic: if the value depends only on the columns within the same row and can be determined before the data model is built, prefer a custom column in M. This pushes computation upstream into the ETL layer, where it can benefit from query folding and keep your DAX model lean. If you need cross-table lookups, use a DAX calculated column. If the value must respond to slicer or filter selections, use a measure.

SECTION 8

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

Intro-to-advanced concept mapping for M custom columns

⚠

Query Folding Awareness

When your data source is a relational database (SQL Server, PostgreSQL, etc.), Power Query attempts to translate M steps back into native SQL—a process called query folding. Simple arithmetic and conditional custom columns often fold successfully, generating 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.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why the expression each [Price] * [Qty] is equivalent to (_) => _[Price] * _[Qty]. What role does the underscore play, and why does M offer each as syntactic sugar?

Show answer

The keyword each is a shorthand that the M parser expands into an anonymous function whose single parameter is named _. Inside the body of the function, any unqualified field access [FieldName] implicitly resolves to _[FieldName]. This sugar exists because custom columns and many other Table functions require a row-level mapping function, and writing the full lambda every time would be verbose. The underscore binds to the current row record—a value of type record with one field per column.

Question 2: BASIC CALCULATION

Given a table with columns HoursWorked (number) and HourlyRate (number), write the complete Table.AddColumn expression to create a GrossPay column. Include a type annotation.

Show answer

The expression is: Table.AddColumn(PreviousStep, "GrossPay", each [HoursWorked] * [HourlyRate], type number). Here, PreviousStep is the variable name assigned to the preceding step in the let expression. The third argument is the row-level function that multiplies the two fields, and type number ensures Power Query treats the result as numeric, which avoids an extra type-detection pass during model loading.

Question 3: INTERMEDIATE

Write an M custom-column expression that creates a 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.

Show answer

A robust expression is: each Text.Trim(([FirstName] ?? "") & " " & ([LastName] ?? "")). The ?? operator substitutes an empty string when either name field is null, preventing the entire concatenation from collapsing to null (since null & "text" yields null in M). Wrapping with Text.Trim removes leading or trailing spaces that would result if one name is missing. The full Table.AddColumn call would pass type text as the fourth argument.

Question 4: APPLIED

You have an 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).

Show answer

One correct approach is: each Number.IntegerDivide(Duration.Days(DateTime.LocalNow() - DateTime.From([HireDate])), 365). This subtracts the hire date (promoted to datetime) from the current timestamp, yielding a duration value. Duration.Days converts that duration to a day count, and Number.IntegerDivide(..., 365) performs integer division to approximate whole years. For greater precision, you could use Duration.TotalDays(...) / 365.25 and then Number.RoundDown to account for leap years. Pass Int64.Type as the type annotation.

Question 5: CRITICAL THINKING

A colleague adds a custom column 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.

Show answer

The most likely cause is a query-folding break. Text.BetweenDelimiters is an M-native function with no direct SQL equivalent, so the engine cannot translate that step into a server-side query. As a result, Power Query downloads the entire table (or everything up to the last foldable step) and applies the string extraction locally. To confirm, right-click the step immediately before the custom column and check whether View Native Query is available (it should be), then right-click the custom-column step—if the option is grayed out, folding is broken. An alternative approach is to replace the M function with equivalent SQL logic using Value.NativeQuery to inject a SUBSTRING / CHARINDEX expression, or restructure the query so the fold-breaking step comes last, minimizing the data transferred before local processing begins.

SUMMARY

Lesson Summary

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.