Patterns

Use if/then/else inside Table.AddColumn to classify, label, and derive values from row data — the most common M transformation pattern.

Understand how null propagates silently through M expressions and learn the standard patterns for defaulting, filtering, and intentionally preserving null values.

Build queries that accept parameters — from simple Power BI parameters to dynamic function-based filtering — without hardcoding values.

Look up values from one table in another — the M equivalent of VLOOKUP — using Table.NestedJoin or direct list lookup patterns.

Define M functions once and call them across multiple queries — eliminating copy-paste logic, improving maintainability, and applying consistent transformations at scale.

Generate a complete date table in M with year, month, quarter, week, and fiscal period columns — no external data source needed.

Select table columns by name at runtime using a list variable instead of hardcoding column names.

Use try/otherwise to recover from errors cell-by-cell in a column, preserving rows that would otherwise fail and optionally capturing the error message.

Match records across tables when values aren't identical — handling typos, spacing, and case differences.

Parse and flatten nested JSON objects, arrays, and XML structures returned by APIs using iterative expansion with Table.ExpandRecordColumn, Table.ExpandListColumn, and Xml.Document.

Reshape tables between wide and long format using Table.Unpivot, Table.UnpivotOtherColumns, and Table.Pivot — including null handling, aggregation, and dynamic column lists.

Diagnose and fix slow Power Query M queries by preserving query folding, buffering intermediate results, consolidating transform steps, and knowing when to push work back to the source system.

Compute cumulative sums, running counts, and other window-style calculations in M using List.Generate or List.Accumulate — no built-in window function needed.

Combine tables on shared keys using Table.NestedJoin and Table.ExpandTableColumn — covering left outer, inner, right outer, full outer, anti-join, and multi-column joins.

Extract, split, and transform text in M using Text.* functions — parsing delimited strings, extracting substrings, and cleaning messy data without regular expressions.

Pass API keys, Bearer tokens, and query parameters to authenticated REST APIs using the Web.Contents options record — including how to handle token refresh and avoid hardcoding secrets.

Fetch all pages of a paginated REST API by looping with List.Generate until an empty response is returned.

Flatten arbitrarily nested lists into a single flat list using recursion with the @ self-reference operator.