Error Handling and Try-Otherwise Patterns in M: Building Production-Ready Power Query Solutions

You're building a Power Query solution that pulls sales data from multiple regional APIs, transforms customer demographics from CSV files with inconsistent formats, and merges everything with a legacy database that sometimes goes offline. When you run your query, you get a cryptic "DataSource.Error" that crashes your entire refresh. Sound familiar?

This is where most Power Query developers hit a wall. Basic M language skills get you far, but production data pipelines require bulletproof error handling. The difference between a fragile query that breaks every few weeks and a robust one that runs reliably for years often comes down to how well you anticipate and handle errors.

Error handling in M isn't just about wrapping operations in try statements. It's about understanding M's evaluation model, designing graceful degradation strategies, and building queries that can adapt to changing data conditions. By the end of this lesson, you'll have the tools to build enterprise-grade Power Query solutions that handle errors intelligently rather than just failing silently.

What you'll learn:

• How M's lazy evaluation affects error propagation and when errors actually occur
• The complete try-otherwise pattern ecosystem, including advanced conditional error handling
• Strategies for handling different error types (data source, transformation, type conversion errors)
• Building error logging and monitoring systems directly in M
• Performance implications of error handling patterns and optimization techniques
• Advanced patterns for graceful degradation and fallback data sources

Prerequisites

This lesson assumes you're comfortable with intermediate M language concepts including custom functions, list and record operations, and basic Power Query transformations. You should understand M's functional programming principles and have experience debugging queries in the Advanced Editor.

Understanding M's Error Model and Evaluation Context

Before diving into try-otherwise patterns, you need to understand how errors work in M's lazy evaluation system. Unlike imperative languages where errors occur immediately when a line executes, M only evaluates expressions when their results are actually needed. This has profound implications for error handling strategy.

Consider this seemingly simple query:

let
    Source = Web.Contents("https://api.nonexistent-domain.com/data"),
    ParsedJson = Json.Document(Source),
    FirstRecord = ParsedJson{0},
    Result = FirstRecord[CustomerName]
in
    Result

You might expect this to fail immediately at the Web.Contents step, but M doesn't work that way. The error won't surface until Power Query tries to materialize the final result. This means your error handling needs to account for where evaluation actually happens, not just where you think it might fail.

Let's build a more realistic example that demonstrates this behavior:

let
    // This function appears to work fine
    GetCustomerData = (baseUrl as text) as table =>
    let
        RawData = Web.Contents(baseUrl & "/customers"),
        JsonData = Json.Document(RawData),
        ConvertedTable = Table.FromRecords(JsonData),
        TypedTable = Table.TransformColumnTypes(ConvertedTable, {
            {"CustomerId", Int64.Type},
            {"Revenue", Currency.Type},
            {"LastOrderDate", DateTime.Type}
        })
    in
        TypedTable,
    
    // Multiple data sources
    PrimarySource = GetCustomerData("https://api.primary-system.com"),
    SecondarySource = GetCustomerData("https://backup.secondary-system.com"),
    
    // This is where errors might actually surface
    CombinedData = Table.Combine({PrimarySource, SecondarySource})
in
    CombinedData

In this scenario, if the primary API is down, you won't know until Table.Combine tries to evaluate PrimarySource. The error propagates up the evaluation chain, but by then you've lost the context of which specific data source failed.

This is why effective M error handling requires thinking about evaluation boundaries and designing your error handling around where evaluation actually occurs, not just where operations are defined.

The Complete Try-Otherwise Pattern Ecosystem

The basic try expression in M returns a record with two fields: HasError (logical) and either Value or Error. Most developers learn the simple pattern:

try someOperation otherwise "default value"

But this barely scratches the surface. Let's build up the complete ecosystem of try-otherwise patterns you need for production systems.

Basic Try Expression Anatomy

First, understand what try actually returns:

let
    SuccessfulOperation = try (1 + 1),
    FailedOperation = try (1 / 0),
    
    // Examine the structure
    SuccessExample = SuccessfulOperation,
    // Returns: [HasError = false, Value = 2]
    
    ErrorExample = FailedOperation
    // Returns: [HasError = true, Error = [Reason = "Expression.Error", Message = "...", Detail = ...]]
in
    {SuccessExample, ErrorExample}

The Error record contains crucial debugging information:

• Reason: A categorized error type (like "DataSource.Error", "Expression.Error")
• Message: Human-readable description
• Detail: Additional context, often including the original operation

Conditional Error Handling

Instead of generic fallbacks, you can handle different error types differently:

let
    HandleApiCall = (url as text) as any =>
    let
        ApiResult = try Web.Contents(url),
        Result = if ApiResult[HasError] then
            // Check the specific error type
            if ApiResult[Error][Reason] = "DataSource.Error" then
                // Network/API issues - try backup source
                try Web.Contents(Text.Replace(url, "api.", "backup-api."))
                otherwise null
            else if ApiResult[Error][Reason] = "WebContents.UnsupportedProtocol" then
                // Protocol issues - might need authentication
                error "Authentication required for: " & url
            else
                // Unknown error - propagate it
                error ApiResult[Error]
        else
            ApiResult[Value]
    in
        Result
in
    HandleApiCall

This pattern lets you implement sophisticated error recovery strategies. Network timeouts get retried with backup endpoints, authentication errors get re-thrown with better messages, and truly unexpected errors get properly propagated.

Error Aggregation and Logging

For complex queries processing multiple data sources, you need to collect and analyze errors rather than just handling them:

let
    ProcessDataSources = (sourceUrls as list) as record =>
    let
        // Process each source and capture results/errors
        ProcessedSources = List.Transform(sourceUrls, (url) =>
            let
                SourceName = Text.AfterDelimiter(url, "//"),
                Result = try [
                    Data = Web.Contents(url),
                    ParsedData = Json.Document(Web.Contents(url)),
                    RecordCount = List.Count(Json.Document(Web.Contents(url)))
                ]
            in
                [
                    SourceName = SourceName,
                    Url = url,
                    Success = not Result[HasError],
                    Data = if Result[HasError] then null else Result[Value][ParsedData],
                    RecordCount = if Result[HasError] then 0 else Result[Value][RecordCount],
                    ErrorReason = if Result[HasError] then Result[Error][Reason] else null,
                    ErrorMessage = if Result[HasError] then Result[Error][Message] else null,
                    ProcessedAt = DateTime.LocalNow()
                ]
        ),
        
        SuccessfulSources = List.Select(ProcessedSources, (source) => source[Success]),
        FailedSources = List.Select(ProcessedSources, (source) => not source[Success]),
        
        CombinedData = if List.Count(SuccessfulSources) > 0 then
            Table.FromRecords(List.Transform(SuccessfulSources, (source) => source[Data]))
        else
            error "All data sources failed: " & Text.Combine(
                List.Transform(FailedSources, (source) => 
                    source[SourceName] & " (" & source[ErrorReason] & ")"), ", ")
    in
        [
            Data = CombinedData,
            SuccessCount = List.Count(SuccessfulSources),
            FailureCount = List.Count(FailedSources),
            ErrorLog = Table.FromRecords(FailedSources),
            ProcessingReport = [
                TotalSources = List.Count(sourceUrls),
                SuccessRate = List.Count(SuccessfulSources) / List.Count(sourceUrls),
                CompletedAt = DateTime.LocalNow()
            ]
        ]
in
    ProcessDataSources

This pattern gives you comprehensive error reporting while still producing usable results when possible. The error log becomes valuable for monitoring and improving your data pipeline over time.

Handling Data Source Errors

Data source errors are the most common type you'll encounter in production Power Query solutions. These include network timeouts, authentication failures, API rate limiting, and service unavailability. Each requires different handling strategies.

Web API Error Handling

APIs fail in predictable ways. Here's a robust pattern for handling common API error scenarios:

let
    CallApiWithRetry = (baseUrl as text, endpoint as text, optional headers as record, optional maxRetries as number) as any =>
    let
        MaxAttempts = if maxRetries is null then 3 else maxRetries,
        DefaultHeaders = [#"Content-Type" = "application/json"],
        RequestHeaders = if headers is null then DefaultHeaders else DefaultHeaders & headers,
        
        AttemptRequest = (attemptNumber as number) as any =>
        let
            Url = baseUrl & endpoint,
            Request = try Web.Contents(Url, [Headers = RequestHeaders]),
            
            Result = if Request[HasError] then
                let
                    ErrorReason = Request[Error][Reason],
                    ErrorMessage = Request[Error][Message]
                in
                    if ErrorReason = "DataSource.Error" and attemptNumber < MaxAttempts then
                        // Exponential backoff for retries
                        Function.InvokeAfter(() => AttemptRequest(attemptNumber + 1), 
                                           #duration(0, 0, 0, Number.Power(2, attemptNumber)))
                    else if Text.Contains(ErrorMessage, "429") or Text.Contains(ErrorMessage, "rate limit") then
                        // Rate limiting - longer delay
                        Function.InvokeAfter(() => AttemptRequest(attemptNumber + 1), 
                                           #duration(0, 0, 1, 0))
                    else if Text.Contains(ErrorMessage, "401") or Text.Contains(ErrorMessage, "403") then
                        error "Authentication failed for " & Url & ". Check API credentials."
                    else
                        error Request[Error]
            else
                Request[Value]
        in
            Result
    in
        AttemptRequest(1)
in
    CallApiWithRetry

Performance Note: Function.InvokeAfter creates actual delays in your query execution. Use it judiciously and consider whether retry logic belongs in M or in your broader data architecture.

Database Connection Resilience

Database connections fail for different reasons than APIs. Connection pooling, timeout settings, and transaction state all affect how you should handle errors:

let
    ConnectToDatabase = (serverName as text, databaseName as text) as table =>
    let
        // Primary connection attempt
        PrimaryConnection = try Sql.Database(serverName, databaseName, [
            CommandTimeout = #duration(0, 0, 2, 0),
            ConnectionTimeout = #duration(0, 0, 0, 30)
        ]),
        
        Result = if PrimaryConnection[HasError] then
            let
                ErrorMessage = PrimaryConnection[Error][Message]
            in
                if Text.Contains(ErrorMessage, "timeout") then
                    // Retry with longer timeout
                    try Sql.Database(serverName, databaseName, [
                        CommandTimeout = #duration(0, 0, 10, 0),
                        ConnectionTimeout = #duration(0, 0, 2, 0)
                    ])
                    otherwise error "Database " & databaseName & " unreachable after extended timeout"
                else if Text.Contains(ErrorMessage, "login failed") or Text.Contains(ErrorMessage, "authentication") then
                    error "Database authentication failed. Check connection credentials for " & serverName
                else if Text.Contains(ErrorMessage, "network") then
                    // Try read-only replica if available
                    try Sql.Database(serverName & "-replica", databaseName, [
                        CommandTimeout = #duration(0, 0, 2, 0),
                        ConnectionTimeout = #duration(0, 0, 0, 30)
                    ])
                    otherwise error "Primary database and replica both unreachable: " & serverName
                else
                    error PrimaryConnection[Error]
        else
            PrimaryConnection[Value]
    in
        Result
in
    ConnectToDatabase

File System and SharePoint Error Handling

File-based data sources have their own error patterns, especially when dealing with SharePoint, network drives, or cloud storage:

let
    LoadFileWithFallback = (primaryPath as text, optional backupPath as text) as table =>
    let
        GetFileExtension = (filePath as text) as text =>
            Text.AfterDelimiter(filePath, ".", {0, RelativePosition.FromEnd}),
        
        LoadByExtension = (filePath as text) as table =>
            let
                Extension = Text.Lower(GetFileExtension(filePath)),
                LoadResult = try (
                    if Extension = "xlsx" then Excel.Workbook(File.Contents(filePath))
                    else if Extension = "csv" then Csv.Document(File.Contents(filePath))
                    else if Extension = "json" then Table.FromRecords(Json.Document(File.Contents(filePath)))
                    else error "Unsupported file type: " & Extension
                )
            in
                if LoadResult[HasError] then error LoadResult[Error] else LoadResult[Value],
        
        PrimaryAttempt = try LoadByExtension(primaryPath),
        
        Result = if PrimaryAttempt[HasError] then
            let
                ErrorMessage = PrimaryAttempt[Error][Message],
                ErrorReason = PrimaryAttempt[Error][Reason]
            in
                if ErrorReason = "DataSource.Error" and backupPath <> null then
                    // File access issues - try backup
                    try LoadByExtension(backupPath)
                    otherwise error "Both primary and backup files inaccessible: " & primaryPath
                else if Text.Contains(ErrorMessage, "corrupted") or Text.Contains(ErrorMessage, "invalid format") then
                    error "File format error in " & primaryPath & ". File may be corrupted or incorrectly formatted."
                else if Text.Contains(ErrorMessage, "permission") or Text.Contains(ErrorMessage, "access denied") then
                    error "Access denied to " & primaryPath & ". Check file permissions and authentication."
                else
                    error PrimaryAttempt[Error]
        else
            PrimaryAttempt[Value]
    in
        Result
in
    LoadFileWithFallback

Transformation Error Handling Patterns

Data transformation errors often occur deep in complex transformation chains. Unlike data source errors, these typically indicate data quality issues, schema changes, or assumptions that no longer hold. Your error handling strategy needs to balance data quality with pipeline reliability.

Type Conversion Error Handling

Type conversions fail when data doesn't match expected formats. Instead of failing the entire dataset, you often want to capture conversion errors and continue processing:

let
    SafeTypeConversion = (inputTable as table, columnTransformations as list) as record =>
    let
        // Apply transformations with error tracking
        TransformWithTracking = List.Accumulate(columnTransformations, 
            [Table = inputTable, ErrorLog = {}], 
            (state, transformation) =>
            let
                ColumnName = transformation[ColumnName],
                TargetType = transformation[TargetType],
                DefaultValue = transformation[DefaultValue]?,
                
                // Create conversion function that tracks errors
                ConversionFunction = (value) =>
                    let
                        ConversionResult = try (
                            if TargetType = Int64.Type then Int64.From(value)
                            else if TargetType = Number.Type then Number.From(value)
                            else if TargetType = DateTime.Type then DateTime.From(value)
                            else if TargetType = Date.Type then Date.From(value)
                            else if TargetType = Logical.Type then Logical.From(value)
                            else Text.From(value)
                        )
                    in
                        if ConversionResult[HasError] then
                            [Value = DefaultValue, HasError = true, OriginalValue = value, 
                             ErrorReason = ConversionResult[Error][Reason]]
                        else
                            [Value = ConversionResult[Value], HasError = false, OriginalValue = value],
                
                // Apply conversion to column
                ConvertedTable = Table.TransformColumns(state[Table], {ColumnName, ConversionFunction}),
                
                // Extract error information
                ErrorRows = Table.SelectRows(ConvertedTable, 
                    each Record.Field(_, ColumnName)[HasError] = true),
                
                NewErrors = Table.TransformColumns(ErrorRows, {
                    ColumnName, (conversionResult) => [
                        RowIndex = null, // Would need to be calculated
                        ColumnName = ColumnName,
                        OriginalValue = conversionResult[OriginalValue],
                        TargetType = Type.ToText(TargetType),
                        ErrorReason = conversionResult[ErrorReason],
                        ProcessedAt = DateTime.LocalNow()
                    ]
                }),
                
                // Clean the table to contain only actual values
                CleanTable = Table.TransformColumns(ConvertedTable, {
                    ColumnName, (conversionResult) => conversionResult[Value]
                })
            in
                [
                    Table = CleanTable,
                    ErrorLog = state[ErrorLog] & Table.ToRecords(NewErrors)
                ]
        ),
        
        FinalResult = TransformWithTracking[Table],
        AllErrors = TransformWithTracking[ErrorLog],
        
        QualityMetrics = [
            TotalRows = Table.RowCount(inputTable),
            ErrorCount = List.Count(AllErrors),
            ErrorRate = List.Count(AllErrors) / Table.RowCount(inputTable),
            ColumnsProcessed = List.Count(columnTransformations),
            ProcessedAt = DateTime.LocalNow()
        ]
    in
        [
            Data = FinalResult,
            ErrorLog = Table.FromRecords(AllErrors),
            QualityMetrics = QualityMetrics
        ]
in
    SafeTypeConversion

This pattern gives you granular control over type conversion failures while maintaining data pipeline reliability. You can set quality thresholds and decide whether to continue processing or fail based on error rates.

Schema Validation and Adaptation

Real-world data sources change their schemas. Your transformations need to adapt gracefully:

let
    AdaptiveColumnSelection = (inputTable as table, expectedColumns as list) as record =>
    let
        ActualColumns = Table.ColumnNames(inputTable),
        
        ColumnAnalysis = List.Transform(expectedColumns, (expectedCol) =>
            let
                ColumnName = if expectedCol is text then expectedCol else expectedCol[Name],
                Required = if expectedCol is text then true else (expectedCol[Required]? ?? true),
                DefaultValue = if expectedCol is text then null else (expectedCol[DefaultValue]?),
                
                IsPresent = List.Contains(ActualColumns, ColumnName),
                
                Result = [
                    Name = ColumnName,
                    Required = Required,
                    Present = IsPresent,
                    DefaultValue = DefaultValue,
                    Status = if IsPresent then "Found" 
                           else if Required then "Missing_Required" 
                           else "Missing_Optional"
                ]
            in
                Result
        ),
        
        MissingRequired = List.Select(ColumnAnalysis, (col) => col[Status] = "Missing_Required"),
        MissingOptional = List.Select(ColumnAnalysis, (col) => col[Status] = "Missing_Optional"),
        PresentColumns = List.Select(ColumnAnalysis, (col) => col[Status] = "Found"),
        
        AdaptedTable = if List.Count(MissingRequired) > 0 then
            error "Required columns missing: " & Text.Combine(
                List.Transform(MissingRequired, (col) => col[Name]), ", ")
        else
            let
                // Add missing optional columns with defaults
                TableWithDefaults = List.Accumulate(MissingOptional, inputTable, 
                    (tbl, col) => Table.AddColumn(tbl, col[Name], 
                        each col[DefaultValue], type nullable any)),
                
                // Select and reorder columns to match expected schema
                ExpectedColumnNames = List.Transform(expectedColumns, 
                    (col) => if col is text then col else col[Name]),
                
                ReorderedTable = Table.SelectColumns(TableWithDefaults, ExpectedColumnNames)
            in
                ReorderedTable,
        
        SchemaReport = [
            ExpectedColumns = List.Count(expectedColumns),
            PresentColumns = List.Count(PresentColumns),
            MissingRequired = List.Count(MissingRequired),
            MissingOptional = List.Count(MissingOptional),
            AdditionalColumns = List.Count(ActualColumns) - List.Count(PresentColumns),
            SchemaCompatibility = if List.Count(MissingRequired) = 0 then "Compatible" else "Incompatible"
        ]
    in
        [
            Data = AdaptedTable,
            SchemaAnalysis = Table.FromRecords(ColumnAnalysis),
            SchemaReport = SchemaReport
        ]
in
    AdaptiveColumnSelection

Handling Aggregation and Join Errors

Complex transformations involving joins and aggregations can fail in subtle ways. You need patterns that detect and handle these failures:

let
    SafeTableJoin = (leftTable as table, rightTable as table, joinColumns as list, joinKind as text) as record =>
    let
        // Validate join columns exist
        LeftColumns = Table.ColumnNames(leftTable),
        RightColumns = Table.ColumnNames(rightTable),
        
        JoinValidation = List.Transform(joinColumns, (colName) =>
            [
                ColumnName = colName,
                InLeftTable = List.Contains(LeftColumns, colName),
                InRightTable = List.Contains(RightColumns, colName),
                Valid = List.Contains(LeftColumns, colName) and List.Contains(RightColumns, colName)
            ]
        ),
        
        InvalidColumns = List.Select(JoinValidation, (col) => not col[Valid]),
        
        JoinResult = if List.Count(InvalidColumns) > 0 then
            error "Join columns not found: " & Text.Combine(
                List.Transform(InvalidColumns, (col) => col[ColumnName]), ", ")
        else
            let
                // Perform the join with error handling
                JoinAttempt = try Table.Join(leftTable, joinColumns, rightTable, joinColumns, 
                                           if joinKind = "inner" then JoinKind.Inner
                                           else if joinKind = "left" then JoinKind.LeftOuter
                                           else if joinKind = "right" then JoinKind.RightOuter
                                           else JoinKind.FullOuter),
                
                Result = if JoinAttempt[HasError] then
                    error "Join failed: " & JoinAttempt[Error][Message]
                else
                    JoinAttempt[Value]
            in
                Result,
        
        // Calculate join statistics
        JoinStats = if JoinResult is table then
            let
                LeftRowCount = Table.RowCount(leftTable),
                RightRowCount = Table.RowCount(rightTable),
                ResultRowCount = Table.RowCount(JoinResult),
                
                Stats = [
                    LeftTableRows = LeftRowCount,
                    RightTableRows = RightRowCount,
                    ResultRows = ResultRowCount,
                    JoinEfficiency = if LeftRowCount > 0 then ResultRowCount / LeftRowCount else 0,
                    JoinType = joinKind,
                    ProcessedAt = DateTime.LocalNow()
                ]
            in
                Stats
        else
            null
    in
        [
            Data = JoinResult,
            JoinValidation = Table.FromRecords(JoinValidation),
            JoinStatistics = JoinStats
        ]
in
    SafeTableJoin

Building Error Logging and Monitoring Systems

Production Power Query solutions need comprehensive error logging and monitoring. Since M doesn't have native logging capabilities, you need to build these systems using table operations and data exports.

Comprehensive Error Logging Framework

Here's a complete framework for capturing, categorizing, and storing error information:

let
    ErrorLogger = [
        // Initialize error collection
        CreateErrorLog = () => Table.FromRecords({}),
        
        // Log a single error with context
        LogError = (errorLog as table, errorInfo as record) as table =>
        let
            EnrichedError = errorInfo & [
                ErrorId = Text.NewGuid(),
                Timestamp = DateTime.LocalNow(),
                Severity = errorInfo[Severity]? ?? "Error",
                Category = errorInfo[Category]? ?? "Unknown",
                Source = errorInfo[Source]? ?? "M Query",
                Context = errorInfo[Context]? ?? "No context provided"
            ],
            
            UpdatedLog = Table.InsertRows(errorLog, Table.RowCount(errorLog), {EnrichedError})
        in
            UpdatedLog,
        
        // Log errors from try operations
        LogTryResult = (errorLog as table, tryResult as record, context as text) as table =>
        let
            NewLog = if tryResult[HasError] then
                ErrorLogger[LogError](errorLog, [
                    Message = tryResult[Error][Message],
                    Reason = tryResult[Error][Reason],
                    Detail = Text.From(tryResult[Error][Detail]? ?? ""),
                    Context = context,
                    Category = "TryOperation",
                    Severity = "Error"
                ])
            else
                errorLog
        in
            NewLog,
        
        // Generate error summary report
        GenerateErrorSummary = (errorLog as table) as record =>
        let
            TotalErrors = Table.RowCount(errorLog),
            
            ErrorsByCategory = Table.Group(errorLog, {"Category"}, 
                {{"Count", each Table.RowCount(_), Int64.Type}}),
            
            ErrorsBySeverity = Table.Group(errorLog, {"Severity"}, 
                {{"Count", each Table.RowCount(_), Int64.Type}}),
            
            RecentErrors = Table.SelectRows(errorLog, 
                each [Timestamp] >= DateTime.LocalNow() - #duration(1, 0, 0, 0)),
            
            Summary = [
                TotalErrors = TotalErrors,
                RecentErrors = Table.RowCount(RecentErrors),
                ErrorCategories = Table.ToRecords(ErrorsByCategory),
                SeverityBreakdown = Table.ToRecords(ErrorsBySeverity),
                LastErrorTime = if TotalErrors > 0 then 
                    List.Max(Table.Column(errorLog, "Timestamp")) else null,
                GeneratedAt = DateTime.LocalNow()
            ]
        in
            Summary
    ]
in
    ErrorLogger

Query Health Monitoring

Beyond just logging errors, you need to monitor the overall health of your queries:

let
    QueryHealthMonitor = (queryName as text) as function =>
    let
        MonitorFunction = (queryOperation as function) as record =>
        let
            StartTime = DateTime.LocalNow(),
            ErrorLog = ErrorLogger[CreateErrorLog](),
            
            ExecuteWithMonitoring = () =>
            let
                ExecutionResult = try queryOperation(),
                
                ExecutionLog = ErrorLogger[LogTryResult](ErrorLog, ExecutionResult, 
                    "Query execution: " & queryName),
                
                EndTime = DateTime.LocalNow(),
                Duration = EndTime - StartTime,
                
                HealthMetrics = [
                    QueryName = queryName,
                    StartTime = StartTime,
                    EndTime = EndTime,
                    Duration = Duration,
                    Success = not ExecutionResult[HasError],
                    ErrorCount = Table.RowCount(ExecutionLog),
                    MemoryUsed = null, // M doesn't expose memory metrics
                    RowsProcessed = if ExecutionResult[HasError] then 0 
                                  else try Table.RowCount(ExecutionResult[Value]) otherwise null
                ],
                
                Result = [
                    Data = if ExecutionResult[HasError] then null else ExecutionResult[Value],
                    Success = not ExecutionResult[HasError],
                    ErrorLog = ExecutionLog,
                    HealthMetrics = HealthMetrics,
                    ErrorSummary = ErrorLogger[GenerateErrorSummary](ExecutionLog)
                ]
            in
                Result
        in
            ExecuteWithMonitoring
    in
        MonitorFunction
in
    QueryHealthMonitor

Performance Impact Analysis

Error handling adds overhead to your queries. Here's how to measure and optimize that impact:

let
    PerformanceBenchmark = (operation as function, iterations as number) as record =>
    let
        RunIteration = (iterationNumber) =>
        let
            StartTime = DateTime.LocalNow(),
            Result = try operation(),
            EndTime = DateTime.LocalNow(),
            Duration = EndTime - StartTime
        in
            [
                Iteration = iterationNumber,
                Success = not Result[HasError],
                Duration = Duration,
                DurationMs = Duration * 24 * 60 * 60 * 1000
            ],
        
        IterationResults = List.Transform(List.Numbers(1, iterations), RunIteration),
        SuccessfulRuns = List.Select(IterationResults, (run) => run[Success]),
        
        Stats = if List.Count(SuccessfulRuns) > 0 then
            let
                Durations = List.Transform(SuccessfulRuns, (run) => run[DurationMs]),
                AvgDuration = List.Average(Durations),
                MinDuration = List.Min(Durations),
                MaxDuration = List.Max(Durations),
                MedianDuration = List.Median(Durations)
            in
                [
                    Iterations = iterations,
                    SuccessfulRuns = List.Count(SuccessfulRuns),
                    SuccessRate = List.Count(SuccessfulRuns) / iterations,
                    AvgDurationMs = AvgDuration,
                    MinDurationMs = MinDuration,
                    MaxDurationMs = MaxDuration,
                    MedianDurationMs = MedianDuration,
                    StandardDeviation = List.StandardDeviation(Durations)
                ]
        else
            [
                Iterations = iterations,
                SuccessfulRuns = 0,
                SuccessRate = 0,
                Message = "All iterations failed"
            ]
    in
        [
            Statistics = Stats,
            DetailedResults = Table.FromRecords(IterationResults)
        ]
in
    PerformanceBenchmark

Advanced Error Recovery Strategies

Beyond basic try-otherwise patterns, production systems need sophisticated recovery strategies. These patterns help your queries adapt to changing conditions and degrade gracefully when things go wrong.

Circuit Breaker Pattern

When external services are failing consistently, you don't want to keep hammering them. The circuit breaker pattern helps protect both your system and external services:

let
    CreateCircuitBreaker = (serviceName as text, failureThreshold as number, timeoutMinutes as number) as record =>
    let
        // In a real implementation, this state would need to be persisted
        // For this example, we'll use a simplified in-memory approach
        CircuitState = [
            ServiceName = serviceName,
            FailureCount = 0,
            LastFailureTime = null,
            State = "Closed", // Closed, Open, HalfOpen
            FailureThreshold = failureThreshold,
            TimeoutDuration = #duration(0, 0, timeoutMinutes, 0)
        ],
        
        CallService = (serviceFunction as function, currentState as record) as record =>
        let
            Now = DateTime.LocalNow(),
            
            // Check if circuit should transition from Open to HalfOpen
            ShouldTryHalfOpen = currentState[State] = "Open" and 
                               currentState[LastFailureTime] <> null and
                               Now >= currentState[LastFailureTime] + currentState[TimeoutDuration],
            
            NewState = if ShouldTryHalfOpen then 
                currentState & [State = "HalfOpen"] else currentState,
            
            Result = if NewState[State] = "Open" then
                [
                    Success = false,
                    Data = null,
                    Error = "Circuit breaker is OPEN for " & serviceName,
                    CircuitState = NewState
                ]
            else
                let
                    ServiceResult = try serviceFunction(),
                    
                    UpdatedState = if ServiceResult[HasError] then
                        let
                            NewFailureCount = NewState[FailureCount] + 1,
                            CircuitShouldOpen = NewFailureCount >= NewState[FailureThreshold]
                        in
                            NewState & [
                                FailureCount = NewFailureCount,
                                LastFailureTime = Now,
                                State = if CircuitShouldOpen then "Open" else "Closed"
                            ]
                    else
                        // Success - reset the circuit
                        NewState & [
                            FailureCount = 0,
                            LastFailureTime = null,
                            State = "Closed"
                        ]
                in
                    [
                        Success = not ServiceResult[HasError],
                        Data = if ServiceResult[HasError] then null else ServiceResult[Value],
                        Error = if ServiceResult[HasError] then ServiceResult[Error][Message] else null,
                        CircuitState = UpdatedState
                    ]
        in
            Result
    in
        [
            State = CircuitState,
            Call = (serviceFunction as function) => CallService(serviceFunction, CircuitState)
        ]
in
    CreateCircuitBreaker

Graceful Degradation Patterns

When primary data sources fail, you often want to fall back to cached data, summary data, or alternative sources:

let
    GracefulDegradationLoader = (config as record) as record =>
    let
        PrimarySource = config[PrimarySource],
        FallbackSources = config[FallbackSources]? ?? {},
        CacheSource = config[CacheSource]?,
        MaxCacheAge = config[MaxCacheAge]? ?? #duration(1, 0, 0, 0),
        
        LoadWithDegradation = () =>
        let
            // Try primary source first
            PrimaryResult = try PrimarySource(),
            
            Result = if not PrimaryResult[HasError] then
                [
                    Data = PrimaryResult[Value],
                    Source = "Primary",
                    Quality = "Full",
                    Timestamp = DateTime.LocalNow()
                ]
            else
                // Try fallback sources
                let
                    TryFallbacks = List.Transform(Record.FieldNames(FallbackSources), (sourceName) =>
                        let
                            SourceFunction = Record.Field(FallbackSources, sourceName),
                            SourceResult = try SourceFunction()
                        in
                            [
                                SourceName = sourceName,
                                Success = not SourceResult[HasError],
                                Data = if SourceResult[HasError] then null else SourceResult[Value],
                                Error = if SourceResult[HasError] then SourceResult[Error][Message] else null
                            ]
                    ),
                    
                    SuccessfulFallback = List.First(
                        List.Select(TryFallbacks, (fb) => fb[Success]), 
                        null
                    ),
                    
                    FallbackResult = if SuccessfulFallback <> null then
                        [
                            Data = SuccessfulFallback[Data],
                            Source = SuccessfulFallback[SourceName],
                            Quality = "Degraded",
                            Timestamp = DateTime.LocalNow()
                        ]
                    else if CacheSource <> null then
                        // Try cache as last resort
                        let
                            CacheResult = try CacheSource(),
                            CacheTimestamp = try CacheResult[Value][Timestamp] otherwise DateTime.LocalNow() - #duration(10, 0, 0, 0),
                            CacheAge = DateTime.LocalNow() - CacheTimestamp,
                            CacheValid = CacheAge <= MaxCacheAge
                        in
                            if not CacheResult[HasError] and CacheValid then
                                [
                                    Data = CacheResult[Value][Data],
                                    Source = "Cache",
                                    Quality = "Cached",
                                    Timestamp = CacheTimestamp,
                                    CacheAge = CacheAge
                                ]
                            else
                                error "All data sources failed and cache is " & 
                                      (if CacheResult[HasError] then "unavailable" else "expired")
                    else
                        error "All data sources failed: " & PrimaryResult[Error][Message]
                in
                    FallbackResult
        in
            Result
    in
        [
            Load = LoadWithDegradation,
            Config = config
        ]
in
    GracefulDegradationLoader

Adaptive Retry Patterns

Different types of errors require different retry strategies. Here's a comprehensive adaptive retry system:

let
    AdaptiveRetryExecutor = (config as record) as function =>
    let
        DefaultConfig = [
            MaxRetries = 3,
            BaseDelayMs = 1000,
            MaxDelayMs = 30000,
            BackoffMultiplier = 2,
            JitterPercent = 0.1,
            RetryableErrors = {"DataSource.Error", "WebContents.Timeout", "Expression.Error"}
        ],
        
        MergedConfig = DefaultConfig & config,
        
        ExecuteWithRetry = (operation as function) as any =>
        let
            AttemptOperation = (attemptNumber as number, lastError as record) as any =>
            let
                ShouldRetry = attemptNumber <= MergedConfig[MaxRetries] and
                             (lastError = null or 
                              List.Contains(MergedConfig[RetryableErrors], lastError[Reason])),
                
                Result = if not ShouldRetry then
                    error lastError
                else
                    let
                        // Add delay before retry (except first attempt)
                        DelayBeforeAttempt = if attemptNumber = 1 then null else
                            let
                                BaseDelay = MergedConfig[BaseDelayMs] * 
                                           Number.Power(MergedConfig[BackoffMultiplier], attemptNumber - 2),
                                CappedDelay = Number.Min(BaseDelay, MergedConfig[MaxDelayMs]),
                                
                                // Add jitter to prevent thundering herd
                                Jitter = Number.RandomBetween(
                                    -CappedDelay * MergedConfig[JitterPercent],
                                    CappedDelay * MergedConfig[JitterPercent]
                                ),
                                FinalDelay = Number.Max(0, CappedDelay + Jitter)
                            in
                                #duration(0, 0, 0, FinalDelay / 1000),
                        
                        AttemptResult = try operation(),
                        
                        FinalResult = if AttemptResult[HasError] and attemptNumber < MergedConfig[MaxRetries] then
                            AttemptOperation(attemptNumber + 1, AttemptResult[Error])
                        else if AttemptResult[HasError] then
                            error AttemptResult[Error] & [AttemptsMade = attemptNumber]
                        else
                            AttemptResult[Value]
                    in
                        FinalResult
            in
                Result
        in
            AttemptOperation(1, null)
    in
        ExecuteWithRetry
in
    AdaptiveRetryExecutor

Hands-On Exercise

Let's build a complete data integration pipeline that demonstrates all the error handling patterns we've covered. This exercise simulates a real-world scenario where you're aggregating sales data from multiple sources with different reliability characteristics.

let
    // Configuration for our data integration pipeline
    PipelineConfig = [
        Sources = [
            PrimaryAPI = [
                Url = "https://api.primary-sales.com/data",
                Timeout = 30,
                RetryCount = 3
            ],
            BackupAPI = [
                Url = "https://backup.sales-system.com/api/data", 
                Timeout = 45,
                RetryCount = 2
            ],
            LocalCache = [
                FilePath = "C:\DataCache\sales_cache.json",
                MaxAge = #duration(2, 0, 0, 0)
            ]
        ],
        
        DataQuality = [
            RequiredColumns = [
                [Name = "TransactionId", Required = true, Type = Int64.Type],
                [Name = "Amount", Required = true, Type = Currency.Type, DefaultValue = 0],
                [Name = "Date", Required = true, Type = Date.Type],
                [Name = "CustomerId", Required = false, Type = Text.Type, DefaultValue = "Unknown"]
            ],
            MaxErrorRate = 0.05,
            MinRowCount = 100
        ]
    ],
    
    // Initialize our error handling framework
    Logger = ErrorLogger,
    ErrorLog = Logger[CreateErrorLog](),
    
    // Create specialized data loaders
    APILoader = (apiConfig as record, errorLog as table) as record =>
    let
        LoadOperation = () =>
        let
            WebResult = Web.Contents(apiConfig[Url], [
                Timeout = #duration(0, 0, 0, apiConfig[Timeout])
            ]),
            JsonData = Json.Document(WebResult),
            TableData = Table.FromRecords(JsonData)
        in
            TableData,
        
        // Use our adaptive retry system
        RetryExecutor = AdaptiveRetryExecutor([
            MaxRetries = apiConfig[RetryCount],
            BaseDelayMs = 2000,
            RetryableErrors = {"DataSource.Error", "WebContents.Timeout"}
        ]),
        
        LoadResult = try RetryExecutor(LoadOperation),
        
        UpdatedErrorLog = Logger[LogTryResult](errorLog, LoadResult, 
            "API Load: " & apiConfig[Url]),
        
        Result = [
            Success = not LoadResult[HasError],
            Data = if LoadResult[HasError] then null else LoadResult[Value],
            ErrorLog = UpdatedErrorLog,
            LoadTime = DateTime.LocalNow()
        ]
    in
        Result,
    
    // Enhanced cache loader
    CacheLoader = (cacheConfig as record, errorLog as table) as record =>
    let
        LoadResult = try [
            FileContents = File.Contents(cacheConfig[FilePath]),
            JsonData = Json.Document(FileContents),
            CacheData = JsonData[Data],
            CacheTimestamp = DateTime.From(JsonData[Timestamp]),
            TableData = Table.FromRecords(CacheData)
        ],
        
        CacheAge = DateTime.LocalNow() - (LoadResult[Value][CacheTimestamp]? ?? 
                   DateTime.LocalNow() - #duration(10, 0, 0, 0)),
        
        CacheValid = not LoadResult[HasError] and CacheAge <= cacheConfig[MaxAge],
        
        UpdatedErrorLog = if not CacheValid then
            Logger[LogError](errorLog, [
                Message = if LoadResult[HasError] then LoadResult[Error][Message] 
                         else "Cache expired",
                Reason = if LoadResult[HasError] then LoadResult[Error][Reason] 
                         else "CacheExpired",
                Context = "Cache validation",
                Category = "Cache",
                Severity = "Warning"
            ])
        else errorLog,
        
        Result = [
            Success = CacheValid,
            Data = if CacheValid then LoadResult[Value][TableData] else null,
            ErrorLog = UpdatedErrorLog,
            CacheAge = CacheAge
        ]
    in
        Result,
    
    // Main data loading with graceful degradation
    LoadDataWithDegradation = () =>
    let
        // Try primary API
        PrimaryResult = APILoader(PipelineConfig[Sources][PrimaryAPI], ErrorLog),
        
        FinalResult = if PrimaryResult[Success] then
            [
                Data = PrimaryResult[Data],
                Source = "Primary API",
                Quality = "Full",
                ErrorLog = PrimaryResult[ErrorLog]
            ]
        else
            // Try backup API
            let
                BackupResult = APILoader(PipelineConfig[Sources][BackupAPI], PrimaryResult[ErrorLog])
            in
                if BackupResult[Success] then
                    [
                        Data = BackupResult[Data],
                        Source = "Backup API", 
                        Quality = "Degraded",
                        ErrorLog = BackupResult[ErrorLog]
                    ]
                else
                    // Fall back to cache
                    let
                        CacheResult = CacheLoader(PipelineConfig[Sources][LocalCache], 
                                                BackupResult[ErrorLog])
                    in
                        if CacheResult[Success] then
                            [
                                Data = CacheResult[Data],
                                Source = "Cache",
                                Quality = "Cached",
                                ErrorLog = CacheResult[ErrorLog],
                                CacheAge = CacheResult[CacheAge]
                            ]
                        else
                            error "All data sources failed"
    in
        FinalResult,
    
    // Load and process the data
    DataLoadResult = try LoadDataWithDegradation(),
    
    ProcessedData = if DataLoadResult[HasError] then
        error DataLoadResult[Error]
    else
        let
            RawData = DataLoadResult[Value][Data],
            
            // Apply schema validation and adaptation
            SchemaAdapter = AdaptiveColumnSelection,
            SchemaResult = SchemaAdapter(RawData, PipelineConfig[DataQuality][RequiredColumns]),
            
            // Apply type conversions with error tracking
            TypeConverter = SafeTypeConversion,
            ConversionSpecs = List.Transform(PipelineConfig[DataQuality][RequiredColumns], 
                (col) => [
                    ColumnName = col[Name],
                    TargetType = col[Type],
                    DefaultValue = col[DefaultValue]?
                ]),
            
            ConversionResult = TypeConverter(SchemaResult[Data], ConversionSpecs),
            
            // Quality validation
            ErrorRate = ConversionResult[QualityMetrics][ErrorRate],
            RowCount = ConversionResult[QualityMetrics][TotalRows],
            
            QualityCheck = [
                PassedErrorRate = ErrorRate <= PipelineConfig[DataQuality][MaxErrorRate],
                PassedRowCount = RowCount >= PipelineConfig[DataQuality][MinRowCount],
                OverallQuality = ErrorRate <= PipelineConfig[DataQuality][MaxErrorRate] and 
                               RowCount >= PipelineConfig[DataQuality][MinRowCount]
            ],
            
            FinalData = if QualityCheck[OverallQuality] then
                ConversionResult[Data]
            else
                error "Data quality check failed: " & 
                      (if not QualityCheck[PassedErrorRate] then 
                       "Error rate " & Text.From(ErrorRate) & " exceeds threshold. " else "") &
                      (if not QualityCheck[PassedRowCount] then 
                       "Row count " & Text.From(RowCount) & " below minimum. " else ""),
            
            ProcessingReport = [
                DataSource = DataLoadResult[Value][Source],
                DataQuality = DataLoadResult[Value][Quality],
                RowsProcessed = Table.RowCount(FinalData),
                ConversionErrors = ConversionResult[QualityMetrics][ErrorCount],
                ErrorRate = ErrorRate,
                QualityPassed = QualityCheck[OverallQuality],
                ProcessedAt = DateTime.LocalNow()
            ]
        in
            [
                Data = FinalData,
                ProcessingReport = ProcessingReport,
                SchemaReport = SchemaResult[SchemaReport],
                ErrorLog = ConversionResult[ErrorLog]
            ],
    
    // Final result with comprehensive monitoring
    PipelineResult = [
        Success = ProcessedData is record,
        Data = if ProcessedData is record then ProcessedData[Data] else null,
        ProcessingReport = if ProcessedData is record then ProcessedData[ProcessingReport] else null,
        ErrorSummary = Logger[GenerateErrorSummary](
            if ProcessedData is record then ProcessedData[ErrorLog] else 
            Table.FromRecords({[
                ErrorId = Text.NewGuid(),
                Message = if DataLoadResult[HasError] then DataLoadResult[Error][Message] else "Unknown error",
                Timestamp = DateTime.LocalNow(),
                Category = "Pipeline",
                Severity = "Critical"
            ]})
        )
    ]
in
    PipelineResult

This exercise demonstrates:

• Multi-level fallback strategies with different data sources
• Comprehensive error logging throughout the pipeline
• Adaptive schema handling for changing data structures
• Data quality validation with configurable thresholds
• Performance monitoring and reporting

Practice modifying this pipeline by:

1. Adding additional fallback sources with different characteristics
2. Implementing custom data quality rules for your domain
3. Adding data transformation steps with error handling
4. Building monitoring dashboards from the error logs and reports

Common Mistakes & Troubleshooting

Even experienced M developers make predictable mistakes with error handling. Here are the most common issues and how to avoid them:

Mistake 1: Over-Broad Error Suppression

Problem: Using otherwise null or generic fallbacks that hide important errors:

// DON'T DO THIS
let
    BadPattern = try Web.Contents("https://api.example.com") otherwise null,
    ProcessedData = if BadPattern = null then #table({"col1"}, {}) else Json.Document(BadPattern)
in
    ProcessedData

Why it's bad: You lose all diagnostic information and can't distinguish between network issues, authentication problems, or data format changes.

Better approach:

let
    ApiCall = try Web.Contents("https://api.example.com"),
    Result = if ApiCall[HasError] then
        let
            ErrorReason = ApiCall[Error][Reason],
            ErrorMessage = ApiCall[Error][Message]
        in
            if ErrorReason = "DataSource.Error" then
                // Log the specific network issue and try alternative
                error "API unavailable: " & ErrorMessage & ". Check network connectivity."
            else if Text.Contains(ErrorMessage, "401") then
                error "Authentication failed. Verify API credentials."
            else
                error ApiCall[Error] // Propagate unexpected errors
    else
        Json.Document(ApiCall[Value])
in
    Result

Mistake 2: Incorrect Error Boundary Placement

Problem: Placing try expressions at the wrong level in the evaluation chain:

// PROBLEMATIC
let
    Source = Web.Contents("https://api.example.com"),
    ParsedJson = Json.Document(Source),
    ProcessedData = Table.FromRecords(ParsedJson),
    // Error handling too late in the chain
    Result = try Table.TransformColumnTypes(ProcessedData, {{"Amount", Currency.Type}}) 
             otherwise ProcessedData
in
    Result

Why it's problematic: If the API call fails, the error won't surface until the type transformation, and you'll lose context about what actually failed.

Better approach:

let
    // Handle errors at appropriate boundaries
    ApiCall = try Web.Contents("https://api.example.com"),
    
    Result = if ApiCall[HasError] then
        error "API call failed: " & ApiCall[Error][Message]
    else
        let
            ParseResult = try Json.Document(ApiCall[Value]),
            
            ProcessedData = if ParseResult[HasError] then
                error "JSON parsing failed: " & ParseResult[Error][Message]
            else
                let
                    TableData = Table.FromRecords(ParseResult[Value]),
                    TypeConversion = try Table.TransformColumnTypes(TableData, 
                        {{"Amount", Currency.Type}})
                in
                    if TypeConversion[HasError] then
                        // Keep original data but log the conversion issue
                        TableData
                    else
                        TypeConversion[Value]
        in
            ProcessedData
in
    Result

Mistake 3: Ignoring Performance Impact

Problem: Adding error handling without considering evaluation overhead:

// PERFORMANCE PROBLEM
let
    SlowPattern = Table.TransformColumns(largeTable, 
        List.Transform(Table.ColumnNames(largeTable), (colName) =>
            {colName, (cellValue) => try Text.From(cellValue) otherwise "ERROR"}
        ))
in
    SlowPattern

This pattern creates a try expression for every cell in every column, which can dramatically slow down large datasets.

Optimized approach:

let
    // Handle errors at column level, not cell level
    OptimizedPattern = List.Accumulate(Table.ColumnNames(largeTable), largeTable,
        (currentTable, columnName) =>
        let
            ConversionResult = try Table.TransformColumns(currentTable, 
                {{columnName, Text.From, type nullable text}})
        in
            if ConversionResult[HasError] then
                // Log the column-level error and use fallback
                Table.ReplaceValue(currentTable, null, "ERROR", 
                    Replacer.ReplaceValue, {columnName})
            else
                ConversionResult[Value]
    )
in
    OptimizedPattern

Mistake 4: Inadequate Error Context

Problem: Error messages that don't provide enough information for debugging:

// UNHELPFUL ERROR HANDLING
let
    ProcessFiles = (filePaths as list) =>
        List.Transform(filePaths, (path) =>
            try Excel.Workbook(File.Contents(path)) 
            otherwise error "File processing failed"
        )
in
    ProcessFiles

Better approach:

let
    ProcessFiles = (filePaths as list) =>
        List.Transform(filePaths, (path) =>
            let
                FileResult = try File.Contents(path),
                
                Result = if FileResult[HasError] then
                    error "Failed to access file '" & path & "': " & FileResult[Error][Message]
                else
                    let
                        ExcelResult = try Excel.Workbook(FileResult[Value])
                    in
                        if ExcelResult[HasError] then
                            error "Failed to parse Excel file '" & path & "': " & 
                                  ExcelResult[Error][Message] & 
                                  ". File may be corrupted or not a valid Excel format."
                        else
                            ExcelResult[Value]
            in
                Result
        )
in
    ProcessFiles

Troubleshooting Evaluation Order Issues

When error handling doesn't behave as expected, it's often due to misunderstanding M's lazy evaluation. Use this debugging pattern:

let
    DebugEvaluation = (queryStep as any, stepName as text) =>
    let
        EvaluationResult = try queryStep,
        LogEntry = [
            StepName = stepName,
            Timestamp = DateTime.LocalNow(),
            Success = not EvaluationResult[HasError],
            ErrorReason = if EvaluationResult[HasError] then EvaluationResult[Error][Reason] else null,
            ErrorMessage = if EvaluationResult[HasError] then EvaluationResult[Error][Message] else null
        ],
        
        // Force evaluation and return both result and debug info
        Result = [
            Value = if EvaluationResult[HasError] then null else EvaluationResult[Value],
            Debug = LogEntry
        ]
    in
        Result
in
    DebugEvaluation

Memory and Resource Management

Large error logs can consume significant memory. Implement log rotation:

let
    RotateErrorLog = (errorLog as table, maxRows as number) as table =>
    let
        CurrentRowCount = Table.RowCount(errorLog),
        
        RotatedLog = if CurrentRowCount > maxRows then
            let
                SortedLog = Table.Sort(errorLog, {{"Timestamp", Order.Descending}}),
                TrimmedLog = Table.FirstN(SortedLog, maxRows)
            in
                TrimmedLog
        else
            errorLog
    in
        RotatedLog
in
    RotateErrorLog

Summary & Next Steps

Mastering error handling in M transforms you from someone who writes queries that work with clean data to someone who builds robust data pipelines that handle the messy realities of production systems. The patterns we've covered—from basic try-otherwise constructs to sophisticated circuit breakers and graceful degradation—form the foundation of enterprise-grade Power Query solutions.

Key takeaways from this lesson:

1. Error handling is about evaluation boundaries: Understanding when and where M evaluates expressions is crucial for effective error handling strategy.

2. Context is everything: Generic error suppression helps no one. Always preserve error context and provide actionable diagnostic information.

3. Design for degradation: Build systems that can operate with partial data rather than failing completely when individual components fail.

4. Monitor and learn: Error logs aren't just for debugging—they're valuable data about the health and evolution of your data sources.

5. Performance matters: Error handling adds overhead. Design patterns that provide robustness without sacrificing query performance.

The advanced patterns we've covered—circuit breakers, adaptive retry logic, and comprehensive error logging—represent production-grade approaches that will serve you well as your Power Query solutions grow in complexity and criticality.

Next steps to deepen your expertise:

• Implement monitoring dashboards: Create Power BI reports that visualize your error logs and quality metrics to identify patterns and trends in data source reliability.

• Build reusable error handling libraries: Package the patterns from this lesson into custom M functions that you can reuse across multiple queries and share with your team.

• Explore integration patterns: Learn how to coordinate error handling between Power Query and other parts of your data architecture, such as Azure Data Factory or custom applications.

• Study advanced M language features: Dive deeper into M's type system, lazy evaluation model, and advanced function patterns that can make your error handling even more sophisticated.

• Practice with real-world scenarios: Apply these patterns to your actual data sources and use cases. Each production system has unique failure modes that will teach you new aspects of robust error handling.

The difference between a data professional who can write M queries and one who can build reliable data systems lies largely in mastering these error handling patterns. As your data sources become more complex and your stakeholders more dependent on consistent data delivery, these skills become not just useful but essential.