Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query

Introduction

You've inherited a Power Query solution that works perfectly in development — until it doesn't. The data feed from your ERP system occasionally sends malformed dates. The API you're calling sometimes returns nulls where numbers should be. A colleague's SharePoint list has a column that intermittently contains text in a numeric field. The query runs fine for three weeks, then at 7:45 AM on a Monday it fails with a cryptic error, and your stakeholders are staring at a blank report wondering where their numbers went.

This is the reality of production data work. Raw data is never as clean as you want it to be, and brittle queries that crash on the first unexpected value aren't a minor inconvenience — they're a reliability problem. The M language in Power Query has a sophisticated error handling model that most practitioners only scratch the surface of, defaulting to wrapping everything in try-otherwise null and calling it done. That approach trades one problem for another: silent failures that corrupt your results without raising any flags.

By the end of this lesson, you'll be able to design genuinely robust data pipelines that handle errors gracefully, surface meaningful diagnostics, isolate failures to specific records rather than crashing entire queries, and build reusable error-handling patterns you can apply across your work. This is the difference between a query that survives production and one that becomes a maintenance liability.

What you'll learn:

• How M's error model actually works at the language level — values, propagation, and the difference between expression errors and step errors
• The full try expression syntax, including the record it returns and how to interrogate it
• Building tiered error handling strategies that distinguish between "expected" errors and genuine bugs
• Row-level error isolation patterns for processing large datasets where some records will always fail
• Constructing diagnostic workflows that log error context, support debugging during development, and produce actionable output for operations teams

---

Prerequisites

This lesson assumes you're comfortable with:

• Writing M expressions directly in the Advanced Editor — not just pointing and clicking in the GUI
• Understanding how Power Query's query folding works conceptually
• Working familiarity with M data types: records, lists, tables, and functions
• Basic M function syntax, including custom function definitions with (x) => ...

If you've been relying entirely on the GUI and haven't written custom M, work through the Power Query Essentials lessons on M fundamentals first.

---

How M's Error Model Actually Works

Before you can handle errors well, you need to understand what an error is in M. This isn't just academic — the mental model shapes every design decision you'll make.

In M, an error is a value — specifically, a special kind of value that cannot be used in further computation. When an expression produces an error, M doesn't throw an exception the way C# or Python would. Instead, it creates an error value and attempts to propagate it. Any expression that depends on an error value also becomes an error value. This propagation continues up the dependency chain until something either handles the error or the error reaches a query output step, at which point the Power Query engine surfaces it to the user.

Think of it like contamination. If cell A1 in a spreadsheet has a #REF! error, any formula that references A1 also shows an error. M works the same way, but with more structure: every error value carries a record that describes what went wrong.

// An error value has this structure:
[
    Reason = "Expression.Error",
    Message = "We cannot convert the value ""abc"" to type Number.",
    Detail = "abc"
]

The Reason field is a category string. The Message is human-readable. The Detail field contains whatever additional information the engine could capture — often the value that triggered the error.

Understanding this structure matters because when you use try, you get access to this record. You can make decisions based on the Reason, include the Detail in diagnostic output, or filter your error handling by the type of error that occurred.

Step Errors vs. Expression Errors

Power Query distinguishes between two scopes of error:

A step error is when an entire named step in your query fails. If the step called #"Changed Type" throws an error, the entire query fails and nothing downstream runs.

An expression error is when a specific expression within a step fails — typically during row-level operations like applying a function to each row of a table. These errors can be isolated to individual cells or records while the rest of the step succeeds.

This distinction is crucial. When you call Table.TransformColumns and one row has a bad value, by default that single bad value propagates an error to its cell — but the step itself can still succeed if you handle it correctly. The query produces a table with an error cell rather than failing entirely.

The Power Query UI actually handles this for you implicitly when you remove errors with "Remove Errors" or use "Keep Errors." But in M code, you need to be explicit about which scope you're operating in.

---

The `try` Expression: Beyond the Basics

Most Power Query practitioners know try in its simplest form:

try Number.FromText("abc") otherwise 0

This returns 0 when the conversion fails. It works, and it's fine for throwaway use cases. But this pattern discards all error information and doesn't distinguish between expected errors (a blank cell, a non-numeric string) and unexpected ones (a null reference, a type that shouldn't exist in this column at all).

Let's look at what try actually gives you.

The Full `try` Return Value

When you use try without otherwise, it always succeeds — it returns a record regardless of whether the expression succeeded or failed. That record has a predictable shape:

// When the expression succeeds:
try 42 + 1
// Returns:
[HasError = false, Value = 43]

// When the expression fails:
try Number.FromText("abc")
// Returns:
[
    HasError = true,
    Error = [
        Reason = "Expression.Error",
        Message = "We cannot convert the value ""abc"" to type Number.",
        Detail = [Value = "abc"]
    ]
]

Notice that when HasError = true, there's no Value field. And when HasError = false, there's no Error field. This is important — don't try to access Value from an error result.

The power of this full record form is that you can make intelligent decisions based on what actually went wrong:

let
    result = try Number.FromText(someText),
    output = 
        if result[HasError] then
            if result[Error][Reason] = "Expression.Error" then
                // Expected conversion failure - use a default
                0
            else
                // Unexpected error type - propagate it
                error result[Error]
        else
            result[Value]
in
    output

This pattern lets you be selective about what you suppress. You're not just silencing all errors — you're handling the ones you expected and re-raising the ones that indicate something genuinely wrong.

Re-raising Errors

The error keyword lets you construct and raise error values. You can re-raise a captured error using its record:

error result[Error]

Or construct a new one with custom information:

error Error.Record("DataQuality.Error", "Expected numeric value in Revenue column", someValue)

Error.Record takes three arguments: reason, message, and detail. The reason string is arbitrary — you can define your own taxonomy. Using your own reason strings lets you write handling logic that distinguishes your application-level errors from M engine errors.

Tip: Establish a naming convention for your custom error reasons. Something like "AppName.Category.Specific" makes it easy to filter and route errors in diagnostic workflows. For example: "SalesReport.Validation.NegativeRevenue" vs. "SalesReport.Source.APITimeout".

---

Building Tiered Error Handling Strategies

A mature error handling strategy isn't one size fits all. Different errors require different responses:

1. Recoverable data quality issues — A cell has a non-numeric string in a numeric column. You can substitute a default value and flag the record.
2. Expected structural variations — Some records have an optional nested JSON field that doesn't always exist. Absence isn't an error, it's a valid state.
3. Upstream source failures — The database connection timed out, or the API returned an HTTP 500. These should fail loudly.
4. Logic bugs in your own M code — Division by zero, accessing a field that doesn't exist. These should surface clearly during development.

Let's build a handling pattern that respects these tiers.

Tier 1: Data Quality Recovery

Suppose you're pulling invoice data from a legacy system where the Amount field is stored as text and sometimes contains values like "N/A", empty strings, or accounting-formatted numbers like "(1,234.56)" for negatives.

let
    // A function that converts messy currency text to a number
    SafeParseAmount = (rawText as nullable text) as nullable number =>
        let
            // Null input is valid - some invoices may not have amounts yet
            cleaned = 
                if rawText = null or Text.Trim(rawText) = "" then null
                else
                    let
                        // Strip currency symbols, commas, spaces
                        stripped = Text.Remove(
                            Text.Trim(rawText), 
                            {"$", ",", " "}
                        ),
                        // Handle accounting negatives: (1234.56) -> -1234.56
                        isNegative = Text.StartsWith(stripped, "(") and Text.EndsWith(stripped, ")"),
                        numericText = if isNegative 
                            then "-" & Text.Middle(stripped, 1, Text.Length(stripped) - 2)
                            else stripped
                    in
                        numericText,
            result = if cleaned = null then null else try Number.FromText(cleaned),
            output = 
                if cleaned = null then null
                else if result[HasError] then
                    error Error.Record(
                        "DataQuality.UnparsableAmount",
                        "Cannot parse amount value",
                        rawText  // Preserve the original for diagnostics
                    )
                else
                    result[Value]
        in
            output
in
    SafeParseAmount

Notice what this does: nulls pass through cleanly (that's valid data), parseable strings get converted, and genuinely unparseable values raise a labeled error rather than returning null. The label means downstream handling can distinguish "no amount recorded" from "amount recorded but corrupt."

Tier 2: Structural Variation

Consider parsing JSON API responses where some records have a nested shipping_address object and others don't:

let
    SafeGetField = (rec as record, fieldName as text, defaultValue as any) as any =>
        let
            hasField = Record.HasFields(rec, fieldName),
            value = if hasField then Record.Field(rec, fieldName) else defaultValue
        in
            value,

    ParseOrderRecord = (orderJson as text) as record =>
        let
            parsed = Json.Document(orderJson),
            orderId = SafeGetField(parsed, "order_id", null),
            customerId = SafeGetField(parsed, "customer_id", null),
            // shipping_address might not exist OR might be null if not yet shipped
            rawShipping = SafeGetField(parsed, "shipping_address", null),
            city = if rawShipping = null then null else SafeGetField(rawShipping, "city", null),
            state = if rawShipping = null then null else SafeGetField(rawShipping, "state", null)
        in
            [
                OrderId = orderId,
                CustomerId = customerId,
                ShippingCity = city,
                ShippingState = state
            ]
in
    ParseOrderRecord

The Record.HasFields check prevents the "field not found" error before it happens. This is prevention rather than handling — and where you can prevent cleanly, that's preferable to catching.

Tier 3: Source Failures

Source failures should fail loudly. If your database connection is unavailable, you do not want to silently substitute nulls into your revenue figures. The correct behavior is a complete, obvious failure.

let
    // Example: wrapping a potentially flaky web service call
    FetchAPIData = (endpoint as text) as table =>
        let
            rawResult = try Web.Contents(endpoint, [Timeout = #duration(0, 0, 30, 0)]),
            data = 
                if rawResult[HasError] then
                    error Error.Record(
                        "Source.APIUnavailable",
                        "Failed to retrieve data from API endpoint: " & endpoint,
                        rawResult[Error]  // Preserve original error as detail
                    )
                else
                    Json.Document(rawResult[Value])
        in
            data
in
    FetchAPIData

The key move here is wrapping the original error as the Detail of your new, more descriptive error. Anyone investigating the failure gets both the application-level context ("API endpoint unavailable") and the underlying engine error that caused it.

---

Row-Level Error Isolation: The Core Production Pattern

This is where the rubber meets the road for most data pipeline work. You have a table with 100,000 rows. Some fraction of them have data quality problems. You need to:

1. Process the good rows successfully
2. Capture the bad rows with enough context to investigate or re-process them
3. Not let bad rows crash the processing of good rows

Power Query's Table.AddColumn with a try expression is the foundation of this pattern:

let
    Source = YourDataTable,

    // Add a column that captures the result of a potentially-failing operation
    WithParseResult = Table.AddColumn(
        Source,
        "_ParseResult",
        each try SafeParseAmount([RawAmount]),
        // Return type is any because it could be a try-record
        type any
    ),

    // Separate successes from failures
    SuccessRows = Table.SelectRows(
        WithParseResult,
        each [_ParseResult][HasError] = false
    ),

    FailureRows = Table.SelectRows(
        WithParseResult,
        each [_ParseResult][HasError] = true
    ),

    // Clean up the success path - extract the actual value
    SuccessClean = Table.TransformColumns(
        Table.RemoveColumns(
            Table.AddColumn(
                SuccessRows,
                "Amount",
                each [_ParseResult][Value],
                type nullable number
            ),
            "_ParseResult"
        ),
        {}  // No additional transforms needed
    ),

    // Enrich the failure path with diagnostic information
    FailureEnriched = Table.SelectColumns(
        Table.AddColumn(
            Table.AddColumn(
                Table.AddColumn(
                    FailureRows,
                    "ErrorReason",
                    each [_ParseResult][Error][Reason],
                    type text
                ),
                "ErrorMessage",
                each [_ParseResult][Error][Message],
                type text
            ),
            "OriginalValue",
            each [RawAmount],
            type nullable text
        ),
        // Select only the columns you need for the error log
        {"InvoiceId", "InvoiceDate", "ErrorReason", "ErrorMessage", "OriginalValue"}
    )
in
    // Return the clean data; the error table is available as a separate query
    SuccessClean

The FailureEnriched result becomes its own query output — an error log table that an operations team can review, investigate, and use as a basis for corrections or reprocessing.

Warning: The try expression wrapping a custom function will catch errors raised by the custom function itself, but if the column access itself fails (for example, the column doesn't exist), that error occurs before the try can catch it, at the step level. Always validate table structure before row-level processing.

A More Elegant Pattern: The Error Triage Table

The split-and-recombine pattern above gets verbose. Here's a cleaner approach using a helper function to capture results in a consistent structure:

let
    // A wrapper that normalizes try results into a consistent record shape
    CaptureResult = (value as any, operation as text) as record =>
        let
            result = try value,
            timestamp = DateTimeZone.UtcNow()
        in
            if result[HasError] then
                [
                    Status = "Error",
                    Value = null,
                    ErrorReason = result[Error][Reason],
                    ErrorMessage = result[Error][Message],
                    ErrorDetail = result[Error][Detail],
                    Operation = operation,
                    CapturedAt = timestamp
                ]
            else
                [
                    Status = "Success",
                    Value = result[Value],
                    ErrorReason = null,
                    ErrorMessage = null,
                    ErrorDetail = null,
                    Operation = operation,
                    CapturedAt = timestamp
                ],

    Source = InvoiceTable,

    // Apply processing with consistent error capture
    Processed = Table.AddColumn(
        Source,
        "_Result",
        each CaptureResult(SafeParseAmount([RawAmount]), "ParseAmount"),
        type record
    ),

    // Expand the result record
    Expanded = Table.ExpandRecordColumn(
        Processed,
        "_Result",
        {"Status", "Value", "ErrorReason", "ErrorMessage", "ErrorDetail", "Operation", "CapturedAt"},
        {"_Status", "ParsedAmount", "_ErrorReason", "_ErrorMessage", "_ErrorDetail", "_Operation", "_CapturedAt"}
    )
in
    Expanded

This gives you a single table where every row has processing metadata. You can filter _Status = "Error" to get your error log, filter _Status = "Success" to get clean data, and keep the intermediate table for full audit capability.

---

Diagnostic Workflows for Development

Handling errors in production is one concern. Diagnosing why your query is failing during development is another. M doesn't have a debugger with breakpoints, which frustrates developers coming from other languages. But you can build diagnostic workflows that give you equivalent insight.

Tracing Values Mid-Query

The most common need is "what is the value of X at this point in my query?" You can't console-log in M, but you can return intermediate values directly:

let
    Step1 = SomeOperation(),
    Step2 = AnotherOperation(Step1),
    // DIAGNOSTIC: temporarily return Step1 to inspect it
    Output = Step1  // Change back to Step2 when done
in
    Output

This is obvious but easily forgotten in complex queries. For deeper inspection, you can also return a record of multiple intermediate values:

let
    Source = YourTable,
    Step1 = Table.SelectRows(Source, each [Year] = 2024),
    Step2 = Table.TransformColumns(Step1, {{"Revenue", each _ * 1000, type number}}),
    // Return diagnostics record
    Diagnostics = [
        SourceRowCount = Table.RowCount(Source),
        AfterFilterCount = Table.RowCount(Step1),
        AfterTransformSample = Table.FirstN(Step2, 5)
    ]
in
    Diagnostics

You load this query and inspect the record in the preview pane. Change Output = Diagnostics back to Output = Step2 when you're done.

Defensive Column Existence Checks

A common cause of mysterious failures is a source table that changes its schema. An upstream table gains or drops a column, and suddenly your downstream queries fail with "column not found" errors that offer no context about which table was the problem or what was expected.

Build a schema validator you can call at the top of sensitive queries:

let
    ValidateColumns = (tbl as table, requiredColumns as list, queryName as text) as table =>
        let
            actualColumns = Table.ColumnNames(tbl),
            missingColumns = List.Select(
                requiredColumns,
                each not List.Contains(actualColumns, _)
            ),
            validationResult = 
                if List.Count(missingColumns) > 0 then
                    error Error.Record(
                        "SchemaValidation.MissingColumns",
                        "Query '" & queryName & "' expects columns that are not present in the source table.",
                        [
                            MissingColumns = missingColumns,
                            AvailableColumns = actualColumns,
                            RequiredColumns = requiredColumns
                        ]
                    )
                else
                    tbl
        in
            validationResult,

    Source = SalesData,

    // Fail immediately with a clear error if the schema has changed
    ValidatedSource = ValidateColumns(
        Source,
        {"OrderDate", "CustomerId", "ProductSKU", "Quantity", "UnitPrice"},
        "SalesTransformQuery"
    ),

    // Continue with confident processing
    Processed = Table.TransformColumns(
        ValidatedSource,
        {
            {"OrderDate", each Date.From(_), type date},
            {"UnitPrice", each _ * 1.0, type number}
        }
    )
in
    Processed

When a column goes missing, you get an error that names the query, lists exactly which columns are missing, and shows what's actually available. Compare that to the default "Expression.Error: The column 'UnitPrice' of the table wasn't found," which gives you no context about where in a 15-query solution the problem occurred.

Building a Query Health Check Table

For complex solutions with many queries, you can build a health-check query that tests each major component and reports its status. This is particularly useful when deploying solutions that will be maintained by others.

let
    // Test each data source and transformation stage
    TestResult = (testName as text, testFn as function) as record =>
        let
            startTime = DateTimeZone.UtcNow(),
            result = try testFn(),
            endTime = DateTimeZone.UtcNow(),
            durationMs = Duration.TotalSeconds(endTime - startTime) * 1000
        in
            [
                TestName = testName,
                Status = if result[HasError] then "FAIL" else "PASS",
                DurationMs = durationMs,
                ErrorMessage = if result[HasError] then result[Error][Message] else null,
                RunAt = startTime
            ],

    // Define test cases
    TestCases = {
        TestResult("SalesDB Connection", () => Table.RowCount(SalesData) >= 0),
        TestResult("Date Range Validation", () => 
            let minDate = List.Min(SalesData[OrderDate])
            in minDate <> null
        ),
        TestResult("Revenue Calculation", () =>
            let
                sample = Table.FirstN(SalesData, 100),
                withRevenue = Table.AddColumn(sample, "Revenue", each [Quantity] * [UnitPrice])
            in
                Table.RowCount(withRevenue) = 100
        )
    },

    // Convert to table
    HealthReport = Table.FromRecords(TestCases)
in
    HealthReport

Load this query whenever you're deploying or debugging, and you get an immediate summary of what's working and what isn't.

---

Advanced Patterns and Edge Cases

The `otherwise` Clause and Lazy Evaluation

One subtlety that trips people up: the otherwise expression in try-otherwise is not evaluated unless there actually is an error. This means you can put expensive operations in the otherwise branch without worrying about performance cost on the happy path.

However, the inverse is also true: if your otherwise expression itself raises an error, you'll get that error instead of the original one. And there's no clean way to debug this with try-otherwise — you get the secondary error with no trace of the original.

This is why the full try record form is preferable when you're doing anything nontrivial in error handling: you preserve the original error information before doing anything else.

Nested `try` Expressions and Error Masking

Be careful with nested try blocks. Consider this:

try (try Number.FromText([Col1]) otherwise 0) + (try Number.FromText([Col2]) otherwise 0)

The outer try will never see an error because the inner try-otherwise expressions suppress them. If both columns have bad data, you get 0 + 0 = 0 — which looks like a valid result. This is silent data corruption.

A better approach:

let
    col1Result = try Number.FromText([Col1]),
    col2Result = try Number.FromText([Col2]),
    hasError = col1Result[HasError] or col2Result[HasError],
    errorDetail = if hasError then
        [
            Col1Error = if col1Result[HasError] then col1Result[Error][Message] else null,
            Col2Error = if col2Result[HasError] then col2Result[Error][Message] else null
        ]
    else null
in
    if hasError then 
        error Error.Record("Calculation.InputError", "One or more inputs could not be parsed", errorDetail)
    else 
        col1Result[Value] + col2Result[Value]

Now the error carries information about which column failed and why, and you haven't silently absorbed a data quality problem.

Handling Errors in List Operations

List.Transform and List.Select don't have native error isolation — if any element causes an error, the entire list operation fails. To process lists with per-element error isolation:

let
    InputList = {"10", "abc", "25", null, "17.5", "N/A"},

    // Process each element with error capture
    Results = List.Transform(
        InputList,
        each
            let
                r = try Number.FromText(_)
            in
                if r[HasError] then [Value = null, Error = r[Error][Message], Original = _]
                else [Value = r[Value], Error = null, Original = _]
    ),

    ResultTable = Table.FromRecords(Results)
in
    ResultTable

This gives you a table where each row has its parsed value, any error message, and the original input. You can then filter and analyze.

Query Folding and Error Handling Interactions

Here's a critical performance consideration that most practitioners don't think about: try expressions prevent query folding.

When Power Query can fold operations to a SQL source, it pushes the computation to the database engine. try wrapping breaks this because SQL has no direct equivalent of M's try semantics. If you wrap a type conversion in try at the row level, that entire operation happens in memory in Power Query rather than in the database.

For large datasets, this is a significant performance difference.

The practical guidance: where possible, fold your filtering and aggregation operations before applying try-based error handling. Apply error isolation to already-reduced datasets.

let
    // GOOD: Fold the filtering to the database, then handle errors in memory
    Source = Sql.Database("server", "db"),
    SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
    
    // This filter can fold to SQL
    RecentSales = Table.SelectRows(SalesTable, each [Year] = 2024),
    
    // This type transform also folds
    TypedSales = Table.TransformColumnTypes(RecentSales, {{"Amount", type number}}),
    
    // THEN apply try-based error handling to the already-reduced set
    WithErrorCapture = Table.AddColumn(
        TypedSales,
        "_Result",
        each try ComplexCustomOperation([Amount])
    )
in
    WithErrorCapture

Warning: Adding a try column as the first operation on a database-connected table will immediately prevent folding for all subsequent steps. Always verify query folding behavior with View > Query Diagnostics when working with large database sources.

---

Hands-On Exercise

You have a table called RawTransactions with these columns:

• TransactionId (text)
• TransactionDate (text — formatted as "DD/MM/YYYY" but occasionally "MM-DD-YYYY", null, or "INVALID")
• Amount (text — dollar amounts, sometimes with "$", sometimes accounting format like "(500.00)", sometimes "N/A")
• CategoryCode (text — should be a number 1-10, but sometimes contains descriptive text)

Your goal is to build a complete solution that:

1. Parses each column with appropriate error handling
2. Produces a clean output table for rows where all columns parse successfully
3. Produces a separate error log table showing each failed row, which column failed, and why
4. Does not use try-otherwise null anywhere — all errors should be captured with context

Step 1: Build individual safe parsing functions for each column.

For TransactionDate, handle both date formats:

SafeParseDate = (raw as nullable text) as any =>
    let
        trimmed = if raw = null then null else Text.Trim(raw),
        r1 = if trimmed = null or trimmed = "" then [HasError = false, Value = null]
             else try Date.FromText(trimmed, [Format = "dd/MM/yyyy"]),
        r2 = if r1[HasError] then try Date.FromText(trimmed, [Format = "MM-dd-yyyy"]) else r1,
        final = if r2[HasError] then
                    [HasError = true, Error = Error.Record(
                        "Parse.DateFormat",
                        "Cannot parse date: " & (if trimmed = null then "null" else trimmed),
                        trimmed
                    )]
                else r2
    in
        final

Note: Date.FromText with a Format parameter uses .NET format strings. The try on this function should be tested against your actual locale and Power Query version, as format string support can vary.

Step 2: Apply parsing to the table using Table.AddColumn.

Add a _DateResult, _AmountResult, and _CategoryResult column using your safe parse functions.

Step 3: Build the split logic.

A row is "clean" if all three parse results have HasError = false:

IsClean = (row as record) as logical =>
    not row[_DateResult][HasError] and 
    not row[_AmountResult][HasError] and 
    not row[_CategoryResult][HasError]

Step 4: Build the error log.

For rows where IsClean is false, create a record per failing column (a row might contribute multiple error records):

// For each failing row, generate one error record per failing column
ExpandErrors = (row as record) as list =>
    let
        dateErrors = if row[_DateResult][HasError] then
            {[TransactionId = row[TransactionId], Column = "TransactionDate", 
              Reason = row[_DateResult][Error][Reason], 
              Message = row[_DateResult][Error][Message]]}
        else {},
        // Similarly for Amount and Category...
        allErrors = List.Combine({dateErrors, /* amountErrors, categoryErrors */})
    in
        allErrors

Work through this exercise fully in Power Query's Advanced Editor. The goal is a solution where someone operating your pipeline can look at the error log and know exactly what to fix.

---

Common Mistakes & Troubleshooting

Mistake 1: Using `try-otherwise null` Universally

This is the number-one error handling antipattern. When you substitute null for any error, you lose information about what failed and why. Nulls in numeric columns get aggregated as zero or ignored in SUM. An entire revenue line vanishes from your report with no indication anything was wrong.

Fix: Use the full try record form and make a deliberate decision for each error type. Substitute null only for errors you've explicitly classified as "expected absence of value."

Mistake 2: Catching Errors You Should Be Preventing

If you're frequently catching "column not found" errors in your processing logic, that's a sign your query structure is wrong. try for column access is masking a design problem.

Fix: Use Record.HasFields and Table.HasColumns defensively before accessing fields. Reserve try for operations that are inherently fallible (parsing, external calls) rather than operations you could check statically.

Mistake 3: The `try` Record Field Access Error

This bites everyone at least once:

// THIS WILL FAIL if result has no error:
let
    result = try someExpression,
    errMsg = result[Error][Message]  // ERROR if HasError = false
in
    errMsg

When HasError = false, the Error field doesn't exist in the record. Accessing it causes a new error.

Fix: Always gate field access on HasError:

errMsg = if result[HasError] then result[Error][Message] else null

Mistake 4: Performance-Destroying Row-Level `try`

If you add a try column to a table with millions of rows sourced from a SQL database, and that try prevents folding, you've just forced a full table scan into memory.

Fix: Apply Table.SelectRows and other foldable operations first. Check folding status with Query Diagnostics. If try is unavoidable on large datasets, consider whether pre-processing at the source (stored procedure, database view) is more appropriate.

Mistake 5: Not Testing Error Paths

It's easy to write error handling code and never actually trigger the error paths during testing. You test your happy path, everything works, you ship it — and the first time a bad record appears in production, your error handling has a bug.

Fix: Explicitly construct test cases that trigger each error path. Use a small test table with known-bad values alongside your real data. Validate that your error log captures exactly what you expect.

Troubleshooting: "I'm Getting an Error Even Inside a `try`"

If you're getting an error at the step level despite wrapping expressions in try, the error is occurring before the try expression evaluates. Common causes:

• Column access each [ColumnName] fails because the column doesn't exist — this fails before try can catch it
• The function you're calling has a parameter type mismatch that fails at the function call boundary
• You're inside a Table.TransformColumns call where the column type specification is wrong

Work inward: comment out steps until the error disappears, then add them back one at a time to identify the exact expression.

---

Summary & Next Steps

Error handling in M is genuinely sophisticated when you engage with it fully. The key principles to carry forward:

Understand the model. Errors are values in M. They propagate deterministically. try gives you a record you can interrogate — use it.

Be selective about suppression. try-otherwise null is a trap. Handle errors at the right tier: recover from expected data quality issues, pass through expected structural variations, fail loudly on source failures, and let logic bugs surface clearly.

Isolate errors at the row level. Don't let one bad record crash your entire pipeline. Use try inside Table.AddColumn to capture per-row results, then split clean and error records into separate outputs.

Build diagnostics into your queries. Schema validators, health check tables, and enriched error logs aren't optional polish — they're what makes the difference between a query you can trust in production and one you're nervous about.

Respect query folding. Know where try breaks folding and structure your queries so folding happens first on large datasets.

Your next steps from here should be:

1. Review your existing Power Query solutions for try-otherwise null patterns and evaluate whether any of them are masking data quality issues that deserve visibility.

2. Build a reusable error capture helper function that you include in a shared parameters query and reference across your solution files.

3. Explore Power Query Diagnostics (Tools > Query Diagnostics) to understand the performance profile of your error handling code against real data volumes.

4. Study M's type system more deeply, particularly how type assertions interact with error propagation — this opens up additional validation patterns for complex nested data structures.

5. Move into advanced M topics: recursive functions (which require careful error handling to avoid infinite loops), and the M streaming model for understanding why large-table operations behave differently than small-table ones.

The queries you build after internalizing these patterns will fail gracefully, surface meaningful information when they do fail, and give you and your stakeholders the confidence that the data they're seeing is either correct or clearly flagged as suspect.