Custom Columns

// Revenue per unit — safe division
= try [Revenue] / [Quantity] otherwise null
// 1001: 1200/4 = 300    1002: 850/0 → null (not error)

// Gross margin % (assuming a Cost column exists)
= try Number.Round(([Revenue] - [Cost]) / [Revenue] * 100, 1) otherwise null

// Days overdue — how many days past the due date
= try Duration.Days([OrderDate] - [DueDate]) otherwise null
// Positive = order placed after due date (late)
// Negative = order placed before due date (on time)

// Revenue band label — bucketing with Number.Round
= "Band " & Text.From(Number.RoundUp([Revenue] / 500, 0))
// 95   → "Band 1"   (95/500 = 0.19, rounds up to 1)
// 430  → "Band 1"   (430/500 = 0.86, rounds up to 1)
// 850  → "Band 2"   (850/500 = 1.7,  rounds up to 2)
// 1200 → "Band 3"   (1200/500 = 2.4, rounds up to 3)
// 1540 → "Band 4"   (1540/500 = 3.08, rounds up to 4)

// Basic if — flag late orders
= if [Status] = "Late" then "⚠ Late" else ""

// Numeric threshold — high value order flag
= if [Revenue] >= 1000 then "High Value" else "Standard"

// Null-safe condition — check for null before comparing
= if [CustomerTier] = null then "Unknown"
  else if [CustomerTier] = "Premium" then "Priority"
  else "Regular"

// Date comparison — is order overdue?
= if [OrderDate] > [DueDate] then "Overdue" else "On Time"

// Boolean flag as number (useful for SUMIF-style DAX measures)
= if [Status] = "Late" then 1 else 0

// Revenue tier — 4 bands using nested if
= if      [Revenue] >= 1500 then "Platinum"
  else if [Revenue] >= 1000 then "Gold"
  else if [Revenue] >= 500  then "Silver"
  else                           "Bronze"
// 1001 (1200) → "Gold"
// 1003 (430)  → "Bronze"
// 1005 (1540) → "Platinum"

// Compound condition — AND using nested if
// "Late AND Premium customer" = Priority escalation
= if [Status] = "Late" then
    if [CustomerTier] = "Premium" then "Escalate"
    else "Monitor"
  else "OK"
// 1003: Late + Premium → "Escalate"
// 1004: Late + Standard → "Monitor"
// 1001: Delivered → "OK"

// Compound condition — AND written inline
= if [Status] = "Late" and [CustomerTier] = "Premium"
  then "Escalate"
  else if [Status] = "Late"
  then "Monitor"
  else "OK"

// Compound condition — OR
= if [Status] = "Late" or [Status] = "Processing"
  then "Needs Attention"
  else "Closed"

// Pattern 1 — List.PositionOf lookup table (switch equivalent)
// Map Status values to numeric priority scores
let
    statuses  = {"Delivered", "Shipped", "Processing", "Late"},
    scores    = {0,            1,         2,             3},
    position  = List.PositionOf(statuses, [Status])
in
    if position = -1 then null     // -1 means not found in list
    else scores{position}
// Delivered → 0, Shipped → 1, Processing → 2, Late → 3, anything else → null

// Pattern 2 — Record field lookup (cleanest for text-to-text mapping)
// Map abbreviated region codes to full names
let
    lookup = [
        NSW = "New South Wales",
        VIC = "Victoria",
        QLD = "Queensland",
        WA  = "Western Australia"
    ]
in
    if Record.HasFields(lookup, [RegionCode])
    then Record.Field(lookup, [RegionCode])
    else "Unknown Region"

// Pattern 3 — nested if with readable indentation (still fine for 4-5 cases)
= if      [Status] = "Delivered"  then "✓ Complete"
  else if [Status] = "Shipped"    then "↗ In Transit"
  else if [Status] = "Processing" then "⏳ Pending"
  else if [Status] = "Late"       then "⚠ Overdue"
  else                                 "? Unknown"

// Pattern 1 — null-coalescing: use a default if null
// "Give me the value, or use this default if it's null"
= if [Discount] = null then 0 else [Discount]
// Equivalent, shorter — using the null-coalescing trick:
= [Discount] ?? 0
// Note: ?? is not valid M syntax — use the if form above

// Pattern 2 — null-safe concatenation
// Wrapping each nullable column before &
= (if [FirstName] = null then "" else [FirstName])
  & " "
  & (if [LastName] = null then "" else [LastName])
// null + null → "  " (two spaces — then trim if needed)

// Pattern 3 — propagate null (if any input is null, output is null)
// Use when a null input should mean "unknown result"
= if [Revenue] = null or [Quantity] = null
  then null
  else try [Revenue] / [Quantity] otherwise null

// Pattern 4 — null check before date arithmetic
= if [OrderDate] = null or [DueDate] = null
  then null
  else Duration.Days([OrderDate] - [DueDate])

// Multi-step logic inside one Custom Column
// Calculate a delivery performance score combining lateness and tier

= let
    // Step 1 — days overdue (negative = early, positive = late)
    DaysLate   = if [OrderDate] = null or [DueDate] = null
                 then 0
                 else Duration.Days([OrderDate] - [DueDate]),

    // Step 2 — base score from lateness
    BaseScore  = if DaysLate <= 0  then 100
                 else if DaysLate <= 3  then 75
                 else if DaysLate <= 7  then 50
                 else                        25,

    // Step 3 — tier multiplier
    Multiplier = if [CustomerTier] = "Premium" then 1.2 else 1.0,

    // Step 4 — final score capped at 100
    FinalScore = Number.Min(Number.Round(BaseScore * Multiplier, 0), 100)
  in
    FinalScore

// Results:
// 1001: DaysLate=-5, BaseScore=100, Multiplier=1.2 → min(120,100) = 100
// 1003: DaysLate=+2, BaseScore=75,  Multiplier=1.2 → min(90,100)  = 90
// 1004: DaysLate=+4, BaseScore=50,  Multiplier=1.0 → min(50,100)  = 50