Power Query M Cheat Sheet

30 essentials with syntax and working examples. Search or filter by group.

let … in

Every query is a let block: each line is a named step that can use earlier steps, and in says which step is the result.

let
    Step1 = expression,
    Step2 = expression
in
    Step2

let
    Source = Csv.Document(File.Contents("C:\Data\sales_flat.csv"), [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
in
    Promoted

each / _

each creates a one-argument function; _ is the current item. Inside a row, [column] is short for _[column].

each expression // same as (_) => expression

List.Transform({1, 2, 3}, each _ * 10)   // {10, 20, 30}

if … then … else

Conditional expression. Lowercase keywords; chain with else if. There is no end keyword.

if condition then value1 else value2

Table.AddColumn(Source, "size", each if [net_sales] >= 50 then "High" else if [net_sales] >= 20 then "Medium" else "Low", type text)

try … otherwise

Evaluates an expression and returns the fallback if it raises an error, like IFERROR in Excel.

try expression otherwise fallback

Table.AddColumn(Source, "qty", each try Number.From([quantity]) otherwise null, type nullable number)

Table.SelectRows

Keeps only the rows where the condition is true (the Filter Rows step).

Table.SelectRows(table as table, condition as function) as table

Table.SelectRows(Source, each [region] = "South" and [net_sales] > 20)

Table.AddColumn

Adds a calculated column. Give the type as the fourth argument so it is not Any.

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

Table.AddColumn(Source, "gross_margin", each [net_sales] - [cost], type number)

Table.TransformColumnTypes

Sets data types for columns. The optional culture controls how text dates and numbers are read (e.g. "en-US" for MM/DD/YYYY).

Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table

Table.TransformColumnTypes(Source, {{"order_date", type date}, {"quantity", Int64.Type}, {"net_sales", type number}}, "en-US")

Table.RenameColumns

Renames columns using a list of {old, new} pairs.

Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table

Table.RenameColumns(Source, {{"net_sales", "Net Sales"}, {"store_name", "Store"}})

Table.RemoveColumns

Deletes the listed columns.

Table.RemoveColumns(table as table, columns as any, optional missingField as nullable number) as table

Table.RemoveColumns(Source, {"cost", "segment"})

Table.SelectColumns

Keeps only the listed columns, in that order. Safer than removing when new columns may appear.

Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table

Table.SelectColumns(Source, {"order_date", "store_name", "net_sales"})

Table.TransformColumns

Applies a function to the values of existing columns without adding new ones.

Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as table

Table.TransformColumns(Source, {{"store_name", each Text.Proper(Text.Trim(_)), type text}, {"category", Text.Proper, type text}})

Table.ReplaceValue

Replaces values in columns. Replacer.ReplaceText swaps part of a text; Replacer.ReplaceValue swaps the whole value.

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

Table.ReplaceValue(Source, "$", "", Replacer.ReplaceText, {"net_sales"})

Table.Group

Groups rows by key columns and aggregates each group (the Group By step).

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table

Table.Group(Source, {"region"}, {{"Total Sales", each List.Sum([net_sales]), type number}, {"Rows", each Table.RowCount(_), Int64.Type}})

Table.Sort

Sorts rows by one or more columns.

Table.Sort(table as table, comparisonCriteria as any) as table

Table.Sort(Source, {{"region", Order.Ascending}, {"net_sales", Order.Descending}})

Table.Distinct

Removes duplicate rows, comparing all columns or only the ones you list.

Table.Distinct(table as table, optional equationCriteria as any) as table

Table.Distinct(Source)

Table.NestedJoin

Merges two queries on key columns; the matches arrive as a nested table column (Merge Queries).

Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind as nullable number, optional keyEqualityComparers as nullable list) as table

Table.NestedJoin(Orders, {"store_id"}, Stores, {"store_id"}, "Stores", JoinKind.LeftOuter)

Table.ExpandTableColumn

Expands a nested table column (usually after a merge) into regular columns.

Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table

Table.ExpandTableColumn(Merged, "Stores", {"region", "store_type"})

Table.Combine

Appends tables one below another (Append Queries). Columns are matched by name.

Table.Combine(tables as list, optional columns as any) as table

Table.Combine({Sales2024, Sales2025})

Table.UnpivotOtherColumns

Turns many columns into attribute/value rows, keeping the listed columns fixed. Ideal for month-per-column reports.

Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table

Table.UnpivotOtherColumns(Source, {"store_name"}, "Month", "Sales")

Table.Pivot

Turns the values of one column into new columns, aggregating another column.

Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table

Table.Pivot(Source, List.Distinct(Source[channel]), "channel", "net_sales", List.Sum)

Table.FillDown

Copies the last non-null value down into null cells below it.

Table.FillDown(table as table, columns as list) as table

Table.FillDown(Source, {"region"})

Table.PromoteHeaders

Uses the first row as column names.

Table.PromoteHeaders(table as table, optional options as nullable record) as table

Table.PromoteHeaders(Source, [PromoteAllScalars = true])

Text.Trim

Removes leading and trailing spaces. Unlike Excel TRIM, it does not collapse double spaces in the middle.

Text.Trim(text as nullable text, optional trim as any) as nullable text

Text.Trim("  Sunshine Miami  ")   // "Sunshine Miami"

Text.Proper

Capitalizes the first letter of each word. Text.Upper and Text.Lower change the whole text.

Text.Proper(text as nullable text, optional culture as nullable text) as nullable text

Text.Proper("SUNSHINE MIAMI")   // "Sunshine Miami"

Text.Replace

Replaces every occurrence of a substring.

Text.Replace(text as nullable text, old as text, new as text) as nullable text

Text.Replace("$12.50", "$", "")   // "12.50"

Text.BeforeDelimiter

Returns the text before a delimiter. Text.AfterDelimiter returns the part after it.

Text.BeforeDelimiter(text as nullable text, delimiter as text, optional index as any) as any

Text.BeforeDelimiter("3 units", " ")   // "3"

Date.Year

Returns the year of a date. Date.Month and Date.Day return the month and day.

Date.Year(dateTime as any) as nullable number

Date.Year(#date(2025, 3, 14))   // 2025

Date.StartOfMonth

Returns the first day of the month, useful for monthly grouping. Date.EndOfMonth returns the last day.

Date.StartOfMonth(dateTime as any) as any

Date.StartOfMonth(#date(2025, 3, 14))   // #date(2025, 3, 1)

List.Sum

Adds the numbers in a list. Table[column] gives a column as a list.

List.Sum(list as list, optional precision as nullable number) as any

List.Sum(Source[net_sales])

Number.Round

Rounds to the given decimals. The default mode rounds halves to even; use RoundingMode.AwayFromZero to match Excel.

Number.Round(number as nullable number, optional digits as nullable number, optional roundingMode as nullable number) as nullable number

Number.Round(2.5, 0, RoundingMode.AwayFromZero)   // 3 (default mode gives 2)