Downloads
Power Query MLanguageBeginner

if

Chooses one of two values based on a test, and always needs an else part.

Syntax
if condition then expression else expression

Returns: The value of the then part when the condition is true, otherwise the value of the else part.

What it does

The if expression tests a condition and returns one of two results. It is how you label rows, flag exceptions and group values into buckets. M has no elseif, so for more than two outcomes you nest another if inside the else part. The else part is required.

Syntax and arguments

Example

Receivables are split into three aging buckets for the collections report: over 90 days, 31 to 90 days and up to 30 days.

let
    Invoices = Table.FromRecords({
        [Invoice = "INV-4471", DaysOverdue = 120],
        [Invoice = "INV-4472", DaysOverdue = 45],
        [Invoice = "INV-4475", DaysOverdue = 7]
    }),
    WithBucket = Table.AddColumn(Invoices, "Bucket", each
        if [DaysOverdue] > 90 then "Over 90"
        else if [DaysOverdue] > 30 then "31 to 90"
        else "Up to 30",
        type text)
in
    WithBucket

The first invoice is "Over 90", the second is "31 to 90" and the third is "Up to 30". The last else catches everything else, so every row gets a bucket.

Example: a second, different use

Flagging a payment that needs a second approver: if [Amount] >= 5000 then "Second approver" else "Standard".

Things to watch

In business terms

Aging buckets, materiality flags, approval thresholds and status labels are almost always written with if. A clear bucket rule, reviewed once, keeps the report consistent every month.

Related functions

All Power Query M functions · View as Markdown · M basics