The M language

let and in

An M query is one expression:

let
    Source = Sql.Database("server", "db"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    Filtered = Table.SelectRows(Sales, each [Amount] > 0),
    Typed = Table.TransformColumnTypes(Filtered, {{"OrderDate", type date}})
in
    Typed

Each name is a step; the in clause names the result. Step names with spaces appear as #"Step Name".

M is case sensitive — Table.SelectRows works, table.selectrows does not. This trips up almost everyone once.

Custom columns

Add Column → Custom Column takes an M expression evaluated per row, where each is shorthand for a function of the current row.

if [Quantity] > 100 then "Bulk"
else if [Quantity] > 10 then "Standard"
else "Small"

Note if/then/else is lowercase and else is mandatory. Text functions use Text.Upper, Text.Combine, Text.Start; dates use Date.Year, Date.AddMonths, Date.From.

Custom functions

Turn a query into a reusable function by writing a parameterised expression:

(TableToClean as table) as table =>
let
    Trimmed = Table.TransformColumns(TableToClean,
              {{"Name", Text.Trim, type text}}),
    Removed = Table.SelectRows(Trimmed, each [Name] <> "")
in
    Removed

Invoke it from another query, or use Invoke Custom Function to apply it across every row of a table — the standard pattern for combining many files from a folder.

Error handling

try ... otherwise catches step errors:

try Number.From([Value]) otherwise null

Without it, a single unparseable value can fail an entire refresh. Wrapping risky type conversions is cheap insurance on data you don't control.