Data Formatting in Power Query

// Rounding applied in a Transform Column step
// Transform tab → Rounding → Round (or use Custom Column for full control)

// Round Revenue to 2 decimal places
#"Rounded Revenue" = Table.TransformColumns(
    #"Changed Type",
    {{"Revenue", each Number.Round(_, 2), type number}}
),

// Round Margin % to 1 decimal place
#"Rounded Margin" = Table.TransformColumns(
    #"Rounded Revenue",
    {{"MarginPct", each Number.Round(_, 1), type number}}
),

// Defensive rounding — wrap in try to handle null rows safely
#"Safe Round" = Table.TransformColumns(
    #"Changed Type",
    {{"Revenue", each try Number.Round(_, 2) otherwise null, type number}}
)

// Common date formatting patterns in Custom Column

// Pattern 1 — Safe date parse from ambiguous text
// "01/02/2024" — is this Jan 2 or Feb 1? Specify the format explicitly.
= try Date.FromText([OrderDate], [Format="dd/MM/yyyy"]) otherwise null

// Pattern 2 — YearMonth sort key (text, sorts correctly)
= Text.From(Date.Year([OrderDate])) & "-"
  & Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0")
// Result: "2024-01", "2024-02" ... "2024-12"

// Pattern 3 — Quarter label
= "Q" & Text.From(Date.QuarterOfYear([OrderDate]))
  & " " & Text.From(Date.Year([OrderDate]))
// Result: "Q1 2024", "Q2 2024"

// Pattern 4 — Fiscal year (April start)
// Fiscal year starts April 1 — months Jan-Mar belong to previous FY
= if Date.Month([OrderDate]) >= 4
  then "FY" & Text.From(Date.Year([OrderDate]))
  else "FY" & Text.From(Date.Year([OrderDate]) - 1)
// Jan 2024 → "FY2023", Apr 2024 → "FY2024"

// Pattern 5 — Days since order (age of order)
= Duration.Days(DateTime.LocalNow() - Date.From([OrderDate]))

// Text.PadStart(text, totalLength, padCharacter)
// Pads the LEFT side of the text until it reaches totalLength characters

// Pad a numeric ID to always be 6 digits
= Text.PadStart(Text.From([CustomerID]), 6, "0")
// 42     → "000042"
// 1234   → "001234"
// 100000 → "100000"  (already 6 digits — no padding added)

// Text.PadEnd(text, totalLength, padCharacter)
// Pads the RIGHT side of the text until it reaches totalLength characters

// Pad a department code to always be 8 characters
= Text.PadEnd([DeptCode], 8, "-")
// "HR"      → "HR------"
// "FINANCE" → "FINANCE-"
// "ENGINEER" → "ENGINEER"  (already 8 — no padding)

// Combined — pad month number for YearMonth sort key
= Text.From(Date.Year([OrderDate])) & "-"
  & Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0")
// Month 1  → "2024-01"
// Month 12 → "2024-12"

// Remove padding (strip leading zeros from a code)
= Text.TrimStart([ProductCode], "0")
// "000042" → "42"
// "001234" → "1234"

// Number cleanup
= Number.Round([Revenue], 2)                          // 2 decimal places
= Number.Abs([Variance])                              // remove negative sign
= Number.Sign([Profit])                               // -1, 0, or 1
= Number.Sqrt([Area])                                 // square root
= Number.Power([Base], 2)                             // raise to power

// Text cleanup and formatting
= Text.Upper([Status])                                // "SHIPPED"
= Text.Lower([Email])                                 // "alice@example.com"
= Text.Proper([CustomerName])                         // "Alice Brown"
= Text.Trim([City])                                   // removes edge spaces
= Text.Clean([Description])                           // removes control chars
= Text.Length([ProductCode])                          // count characters
= Text.Reverse([Code])                                // "ELEC" → "CELE"
= Text.Select([Phone], {"0".."9"})                    // keep only digits
= Text.Remove([Phone], {"-", " ", "(", ")"})          // remove specific chars

// Date extraction
= Date.Year([OrderDate])                              // 2024
= Date.Month([OrderDate])                             // 3
= Date.Day([OrderDate])                               // 15
= Date.QuarterOfYear([OrderDate])                     // 1
= Date.WeekOfYear([OrderDate])                        // 11
= Date.DayOfWeek([OrderDate])                         // 0=Sun … 6=Sat
= Date.IsLeapYear(Date.Year([OrderDate]))             // true / false

// Duration / age
= Duration.Days(DateTime.LocalNow() - [OrderDate])   // days since order
= Duration.TotalHours([ProcessingTime])               // hours as decimal