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. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
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 |