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, 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 and extensive function library (700+ functions) provide flexibility for complex data workflows without sacrificing efficiency.
What This Cheat Sheet Covers
This topic spans 25 focused tables and 245 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
Table 1: Language Fundamentals
| Concept | Example | Description |
|---|---|---|
let Source = 1, Result = Source + 1in Result | • Core syntactic structure defining variables (after let) and returning a final value (after in)• every query uses this pattern | |
Text.Upper("abc")text.upper("abc") // error | • All identifiers, keywords, and function names are case-sensitive • Text.Upper and text.upper are different | |
List.Transform({1,2,3}, each _ * 2) | • Shorthand for creating single-parameter lambda functions • each is syntactic sugar for (_) => ... | |
Table.SelectRows(data, each [Sales] > 100) | • Represents the current item in iteration contexts when using each• refers to current row, list item, or value |