Power Query M cheat sheet

let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"),
        [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Source,
        [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(Promoted,
        {{"Date", type date}, {"Amount", type number}})
in
    Typed

Table.SelectRows(Typed, each [Amount] > 100)
Table.SelectRows(Typed, each [Region] = "North"
    and [Date] >= #date(2026, 1, 1))
Table.Distinct(Typed, {"OrderID"})
Table.Sort(Typed, {{"Amount", Order.Descending}})
Table.FirstN(Sorted, 10)

Table.SelectColumns(Typed, {"Date", "Region", "Amount"})
Table.RemoveColumns(Typed, {"Notes"})
Table.RenameColumns(Typed, {{"Amt", "Amount"}})
Table.AddColumn(Typed, "Net",
    each [Amount] - [Discount], type number)

Table.TransformColumns(Typed, {
    {"Customer", Text.Trim, type text},
    {"Region", Text.Proper, type text}})
Table.ReplaceValue(Typed, "N/A", null,
    Replacer.ReplaceValue, {"Region"})
Table.AddColumn(Typed, "Domain",
    each Text.AfterDelimiter([Email], "@"), type text)

Table.Group(Typed, {"Region"}, {
    {"Total", each List.Sum([Amount]), type number},
    {"Orders", each Table.RowCount(_), Int64.Type}})

Merged = Table.NestedJoin(Sales, {"ProductID"},
    Products, {"ProductID"}, "Product", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Product",
    {"Category", "Price"}),
AllYears = Table.Combine({Sales2025, Sales2026})

// Month columns into rows: Region | Month | Amount
Table.UnpivotOtherColumns(Source, {"Region"},
    "Month", "Amount")

// And back again
Table.Pivot(Long, List.Distinct(Long[Month]),
    "Month", "Amount", List.Sum)

Table.AddColumn(Typed, "Band", each
    if [Amount] >= 1000 then "Large"
    else if [Amount] >= 100 then "Medium"
    else "Small", type text)

Table.AddColumn(Typed, "Unit price",
    each try [Amount] / [Qty] otherwise null)
Table.RemoveRowsWithErrors(Typed, {"Amount"})
Table.ReplaceErrorValues(Typed, {{"Amount", 0}})
Table.SelectRows(Typed, each [Region] <> null)

Date.Year([Date])
Date.StartOfMonth([Date])
Date.EndOfMonth([Date])
Date.AddMonths([Date], 1)
Date.DayOfWeekName([Date])

let
    Start = #date(2026, 1, 1),
    Dates = List.Dates(Start, 365, #duration(1, 0, 0, 0)),
    Calendar = Table.FromList(Dates,
        Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(Calendar,
        {{"Date", type date}})
in
    Typed