M Language (Power Query Formula Language) is a functional, case-sensitive mashup language used in Microsoft Power Query for data transformation and preparation across Power BI, Excel, Microsoft Fabric, and other Microsoft platforms. Built on a let-in expression structure, M enables powerful ETL operations including filtering, combining, and reshaping data from diverse sources. Understanding M's query folding capability—where transformations are pushed to the data source—is critical for performance, while its lazy evaluation, extensive function library (700+ functions), and composable custom-function model provide the flexibility to solve virtually any data-preparation challenge.
What This Cheat Sheet Covers
This topic spans 28 focused tables and 374 indexed concepts, 156 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
A jump-to index of every table row in this cheat sheet.
An interactive map of every table and concept in this topic.
Table 1: Language Fundamentals
The building blocks every M query is made of: the let-in structure, identifier rules, the each/_ shorthand, comments, and the null value. Mastering these is the prerequisite for reading and writing any M code.
| Concept | Example | Description | |
|---|---|---|---|
let Source = 1, Result = Source + 1in Result | • Core syntactic structure: variables defined after let, final output after in• every query uses this pattern | ||
List.Transform({1,2,3}, each _ * 2) | • Shorthand for (_) => ...• single-parameter lambda used throughout the standard library | ||
Table.SelectRows(data, each [Sales] > 100) | Represents the current item in an each context — current row, list item, or value | ||
if [Column] = null then 0 else [Column] | • Represents absence of value — distinct from empty string or zero • requires explicit = null comparison | ||
Text.Upper("abc")text.upper("abc") // error | • All identifiers, keywords, and function names are case-sensitive • Text.Upper ≠ text.upper | ||
// single line/* multi-line */ | • // for single-line, /* */ for multi-line• identical to C-style comments | ||
"Step Name" = Table.Sort(...) | Wraps identifiers containing spaces or special characters in #"..." — used for step names auto-generated by the UI | ||
"text" as text | • Attaches type metadata to values using as• helps with type checking | ||
[] meta [Source = "API"] | • Attaches descriptive information to values using meta• retrieved with Value.Metadata() | ||
= #shared | • Returns record of all available functions, enumerators, and queries in scope • use as a live function reference in the Advanced Editor |