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.
| Function | Description |
|---|
Table.SelectRows | Filters a table to rows matching a condition. |
Table.RemoveColumns | Drops specified columns from a table. |
Table.RenameColumns | Renames one or more columns. |
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. |
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. |
| Function | Description |
|---|
| Text.Trim | Removes leading and trailing whitespace. |
Text.Upper / Text.Lower | Converts text to uppercase or lowercase. |
Text.Start / Text.End / Text.Middle | Extracts a substring from the start, end, or middle of a text value. |
Text.Split | Splits a text value into a list, by delimiter. |
Text.Combine | Joins a list of text values into one, with a separator. |
Text.Contains | Returns true if a text value contains a given substring. |
Text.Replace | Replaces all occurrences of a substring within a text value. |
Text.Length | Returns the number of characters in a text value. |
| 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. |
List.Contains | Returns true if a list contains a given value. |
List.Transform | Applies a function to every value in a list. |
List.Generate | Builds a list by repeatedly applying a function, useful for custom sequences. |
| Function | Description |
|---|
Date.From | Converts a value to a date. |
DateTime.LocalNow | Returns the current local date and time. |
Date.Year / Date.Month / Date.Day | Extracts the year, month, or day from a date. |
Date.AddDays / Date.AddMonths / Date.AddYears | Shifts a date by a given number of days, months, or years. |
Duration.Days / Duration.TotalHours | Extracts a component from a duration value (the result of subtracting two dates/datetimes). |
| Function | Description |
|---|
Number.Round | Rounds a number to a specified number of decimal places. |
Number.From | Converts a value (often text) to a number. |
Number.ToText | Converts a number to text, optionally with a format string. |
| 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: