M Function Reference
A categorized reference of common Power Query M functions — table, text, list, date, and source functions — linking to full explanations where available.
A quick-scan index of the M functions most commonly used in Power Query, grouped by category. Where a function is covered in depth elsewhere on this site — as part of a real transformation, merge, or troubleshooting example — the name links to it. Functions without a dedicated example get a one-line description here.
Not a single function, but the concept behind every each expression and every function passed to Table.AddColumn, List.Transform, or List.Accumulate. See Custom Functions in Power Query M — this is the single highest-leverage M concept for anything beyond basic UI-driven transformations.
| Function | Description |
|---|
| Table.SelectRows | Filters a table to rows matching a condition. |
| Table.RemoveColumns | Drops specified columns from a table — shares the MissingField option below. |
| Table.RenameColumns | Renames one or more columns — shares the MissingField option below. |
| Table.SelectColumns | Returns a table with only the specified columns, dropping the rest. |
| Table.TransformColumnTypes | Sets the data type of one or more columns. |
| Table.TransformColumns | Applies a function to every value in a column, transforming it in place. |
| Table.AddColumn | Adds a new column, computed from an expression evaluated per row. |
| Table.AddIndexColumn | Adds a sequential index column — the standard way to generate a surrogate key. |
| Table.Group | Groups rows and computes an aggregation per group, like SQL's GROUP BY. |
| Table.Sort | Sorts a table by one or more columns. |
| Table.Distinct | Removes duplicate rows, optionally based on specific columns. |
Table.RowCount | Returns the number of rows in a table. |
| Table.FirstN / Table.Skip | Returns the first N rows, or skips the first N rows — a condition instead of N behaves like "take while," not a filter. |
| Table.SplitColumn | Splits one column into multiple columns, by delimiter or position. |
| Table.CombineColumns | Merges multiple columns into one, with a separator. |
| Table.Pivot / Table.Unpivot | Turns row values into columns, or columns into rows. |
| Table.ReplaceValue | Replaces specific values throughout a column. |
| Table.Buffer | Loads a table fully into memory, useful for stabilizing a source before repeated reads. |
See Transformations for most of these applied to a real dataset, and Power Query Editor for how Applied Steps map directly to these function calls.
| Function | Description |
|---|
| Table.NestedJoin | Joins two tables on matching columns, producing a column of nested tables. |
| Table.ExpandTableColumn | Expands a nested-table column (typically from a merge) into regular columns. |
| Table.Combine | Stacks multiple tables with matching columns into one — the function behind Append Queries. |
Table.NestedJoin (Left Anti) | The same merge function, with a join kind that returns only unmatched rows — see Merge Queries. |
Not sure whether a given problem calls for a merge or an append? See Merge vs. Append: When to Use Each.
| Function | Description |
|---|
| List.Select | Filters a list to values matching a condition. |
List.Sum / List.Average / List.Count | Aggregates the values in a list. |
| List.Distinct | Removes duplicate values from a list — case-sensitive by default, like Text.Contains. |
| List.Contains | Returns true if a list contains a given value — also case-sensitive by default. |
| List.Transform | Applies a function to every value in a list. |
| List.Accumulate | Reduces a list to a single value by carrying state across every item — M's general-purpose "reduce." |
| List.Generate | Builds a list by repeatedly applying a function, useful for custom sequences. |
| Function | Description |
|---|
| Number.Round / Number.RoundUp / Number.RoundDown | Rounds a number to a specified number of decimal places — nearest, always up, or always down. |
| Number.From | Converts a value (often text) to a number — a locale mismatch on the decimal separator can misread or fail the conversion. |
| Number.ToText | Converts a number to text, optionally with a format string — "P" multiplies by 100. |
| Function | Description |
|---|
| Value.Type | Returns the type of a value — useful for debugging unexpected type errors. |
| Value.Is | Tests whether a value matches a given type. |
Value.ReplaceType | Overrides the declared type of a value without changing the value itself. |
| Function | Description |
|---|
| try ... otherwise | Catches an error from an expression, substituting a fallback value instead of failing the step. |
| error | Explicitly raises a custom error from within an expression. |
| Function | Description |
|---|
| Csv.Document | Parses CSV content into a table — the function behind Get Data > Text/CSV. |
| Excel.Workbook | Reads an Excel file's sheets and tables. |
| Sql.Database | Connects to a SQL Server database. |
| Json.Document | Parses JSON content into a record or list. |
| Web.Contents | Fetches raw content from a URL — the basis of most API-based connectors. |
File.Contents | Reads the raw binary contents of a local file. |
See M Language for how these source functions typically appear as the first step (Source =) in a generated query.
- Linked functions are shown applied to a real, worked example elsewhere on this site — not just a syntax definition.
- Unlinked functions are simple enough that a one-line description is sufficient — Microsoft's own Power Query M function reference covers full parameter lists for anything not detailed here.
- Start with Introduction and M Language if the underlying syntax (the
let...in structure, each, referencing previous steps) isn't yet familiar — the function list assumes those concepts.
Continue learning Power Query: