Replacing Errors and Handling Exceptions

// Replace errors in a single column — via UI or M
// Transform tab → Replace Errors → enter replacement value

// M generated for replacing Revenue errors with null:
#"Replaced Errors" = Table.ReplaceErrorValues(
    #"Changed Type",
    {{"Revenue", null}}
)

// Replace errors in multiple columns in one step:
#"Replaced Errors" = Table.ReplaceErrorValues(
    #"Changed Type",
    {
        {"Revenue",  null},   // Revenue errors → null
        {"Quantity", 0},      // Quantity errors → 0
        {"Discount", 0.0}     // Discount errors → 0.0
    }
)

// Replace errors across ALL columns at once (no UI — M only):
#"Replaced All Errors" = Table.TransformColumns(
    #"Changed Type",
    List.Transform(
        Table.ColumnNames(#"Changed Type"),
        each {_, each try _ otherwise null}
    )
)

// Basic syntax
try expression otherwise fallback_value

// Example 1 — Safe type conversion
// Instead of letting Changed Type produce errors:
// Add Column → Custom Column
= try Number.From([Revenue]) otherwise null
// If Revenue contains "N/A" → returns null (no error cell)
// If Revenue contains "1200" → returns 1200

// Example 2 — Safe division (avoid divide-by-zero errors)
= try [Revenue] / [Quantity] otherwise null
// If Quantity = 0 → returns null instead of error
// If Quantity > 0 → returns the division result

// Example 3 — Safe date parsing
= try Date.From([OrderDate]) otherwise null
// If OrderDate is a valid date string → returns a date
// If OrderDate is "TBD" or blank → returns null

// Example 4 — try with error inspection
// try returns a record with [HasError], [Value], [Error]
// Use this when you want to log the error message too
= try Number.From([Revenue])
// Returns: [HasError = false, Value = 1200, Error = null]
//       or [HasError = true,  Value = null, Error = [record]]

// Access just the value safely:
= (try Number.From([Revenue]))[Value]

// Access HasError to create a flag column:
= if (try Number.From([Revenue]))[HasError]
  then "Invalid"
  else "Valid"

// Pattern: count error rows before removing them
// Add this as a separate diagnostic query (load disabled)

let
    Source = Orders,  // reference the cleaned Orders query

    // Count rows where Revenue has an error
    ErrorCount = Table.RowCount(
        Table.SelectRows(
            Source,
            each (try [Revenue])[HasError] = true
        )
    ),

    // Count total rows
    TotalRows = Table.RowCount(Source),

    // Build a summary record
    Summary = [
        ErrorRows  = ErrorCount,
        TotalRows  = TotalRows,
        ErrorPct   = Number.Round(ErrorCount / TotalRows * 100, 1)
    ]
in
    Record.ToTable(Summary)

// This produces a small 3-row table:
// Name         Value
// ErrorRows    12
// TotalRows    500
// ErrorPct     2.4

// Defensive column — add it with a default if missing from source

// Pattern: check if column exists, add it with null if not
#"Safe Discount" = if Table.HasColumns(#"Changed Type", "Discount")
    then #"Changed Type"
    else Table.AddColumn(#"Changed Type", "Discount", each null, type number),

// Pattern: ensure a set of required columns always exist
// Useful when sources merge or split columns inconsistently
RequiredColumns = {"OrderID", "Revenue", "Discount", "Status"},

#"With Required Cols" = List.Accumulate(
    RequiredColumns,
    #"Changed Type",
    (state, colName) =>
        if Table.HasColumns(state, colName)
        then state
        else Table.AddColumn(state, colName, each null)
),

// Pattern: rename a column only if the old name exists
// (handles sources where the column was already renamed)
#"Safe Rename" = if Table.HasColumns(#"Changed Type", "rev")
    then Table.RenameColumns(#"Changed Type", {{"rev", "Revenue"}})
    else #"Changed Type"