if
Chooses one of two values based on a test, and always needs an else part.
if condition then expression else expressionReturns: 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
condition: a test that gives true or false, such as[Days] > 90.then expression: the value used when the condition is true.else expression: the value used when it is false. It is always required.
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
elseis required. Leave it out and you get a syntax error.- Test the larger or more specific condition first. The first true test wins.
- A null in the condition can stop the query with an error. Test for blanks first, for example
if [DaysOverdue] = null then "No date" else if [DaysOverdue] > 90 then .... - Many nested ifs are hard to read. Consider a lookup table with Table.Join for long lists of categories.
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.