- Get link
- X
- Other Apps
This article explains in depth how to implement robust error handling in Power Query using the TRY...OTHERWISE pattern so that you can build reliable data transformations in Excel and Power BI, avoid refresh failures, and create self-healing queries that scale in production.
1. Why error handling matters in Power Query
Power Query is designed to connect to diverse data sources, reshape data, and load it into Excel or Power BI models, but real-world data is often messy and inconsistent, which easily leads to errors during refresh.
Without proper error handling, a single invalid value, division by zero, missing column, or failed web call can cause the entire Power Query refresh to fail, blocking downstream reports and dashboards.
To prevent this, the M language provides a dedicated construct for error handling, the try ... otherwise expression, which allows you to catch errors, inspect them if needed, and provide safe fallback values or alternative logic.
2. Understanding TRY...OTHERWISE in M language
The try keyword wraps an expression and returns a record that indicates whether the expression succeeded or failed, and the otherwise keyword provides a default value to use when an error occurs.
2.1 Basic syntax
try <expression> otherwise <fallback_value>When the expression runs successfully, Power Query returns its value directly, because the otherwise part is ignored.
When the expression throws an error, Power Query returns the value defined after otherwise instead of stopping the query.
2.2 What TRY returns internally
When you use try without otherwise, the result is a record with two fields.
[HasError]is a logical value indicating whether an error occurred.[Value]contains either the successful result or an error record.
let Result = try 1 / 0 in ResultIn this example, Result is a record with [HasError] = true and [Error] containing detailed error information.
3. Practical examples of TRY...OTHERWISE
3.1 Safe division example
A classic scenario is dividing by zero in calculated columns or custom columns.
let Numerator = 100, Denominator = 0, SafeResult = try Numerator / Denominator otherwise null in SafeResultHere the query returns null instead of raising a division-by-zero error, allowing the rest of the query to continue normally.
3.2 Replacing errors with default values in a column
You can apply try ... otherwise row by row using Table.AddColumn or within the custom column dialog.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}), #"Safe Division" = Table.AddColumn( #"Changed Type", "Divided", each try [Value] / [Factor] otherwise 0, type number ) in #"Safe Division"In this example, any error in the division (such as missing [Factor], zero, or non-numeric text) is replaced with 0.
3.3 Fallback logic for text parsing
When parsing dates or numbers from text, invalid formats are common, and try allows you to downgrade invalid rows instead of failing the entire query.
let Source = Excel.CurrentWorkbook(){[Name="RawText"]}[Content], #"Parsed Dates" = Table.AddColumn( Source, "Date", each try Date.From([DateText]) otherwise null, type date ) in #"Parsed Dates"Invalid date strings are converted to null, which you can later filter, replace, or log.
4. Inspecting error details with TRY
Sometimes you need to know why an expression failed, not only to replace errors but also to debug or categorize them.
4.1 Using TRY without OTHERWISE
let Source = { "100", "ABC", "200" }, ToNumber = List.Transform( Source, each try Number.From(_) // no otherwise yet ) in ToNumberThe result is a list of records because each try returns a record.
You can then expand the records to inspect [HasError] and [Value].
4.2 Converting TRY records to safe values
let Source = { "100", "ABC", "200" }, ToNumber = List.Transform( Source, each try Number.From(_) ), ToTable = Table.FromList(ToNumber, Splitter.SplitByNothing(), {"Result"}), #"Expanded Result" = Table.ExpandRecordColumn( ToTable, "Result", {"HasError", "Value"}, {"HasError", "Value"} ) in #"Expanded Result"You now have a table with two columns, one indicating whether an error occurred and one containing either the numeric value or an error record.
You can use this to build advanced error diagnostics, such as counts per error type.
5. Using TRY...OTHERWISE in common Power Query workflows
5.1 Handling missing columns safely
When schemas change (for example, a column is removed from a CSV or database view), queries may fail because a referenced column no longer exists.
You can wrap column access in try ... otherwise to prevent failures.
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Add Safe Column" = Table.AddColumn( Source, "OptionalColumn", each try [NewColumn] otherwise null, type any ) in #"Add Safe Column"If [NewColumn] does not exist, Power Query sets OptionalColumn to null instead of throwing an error.
5.2 Handling web requests and API responses
External web APIs are prone to intermittent failures, timeouts, or unexpected payloads, so wrapping them with try ... otherwise is critical.
let Urls = { "https://example.com/api/1", "https://example.com/api/2" }, GetJson = (url as text) as any => let Response = try Json.Document(Web.Contents(url)) otherwise null in Response, Results = List.Transform(Urls, each GetJson(_)) in ResultsThis pattern keeps the full refresh from failing if a single request fails and allows you to later filter out null responses.
5.3 Combining TRY with query folding
Wrapping expressions with try sometimes affects query folding, because the Power Query engine may decide not to push down such logic to the source.
To balance robustness and performance, apply try ... otherwise as late as possible in the query, ideally after the main filtered and projected steps that fold back to the source.
Note : Applying TRY...OTHERWISE early on a database source may break query folding, which can significantly reduce performance, so always check the query diagnostics or native query view after adding error handling.
6. Patterns for table-level error handling
6.1 Replacing all errors in a column
Power Query provides a built-in command Replace Errors that automatically wraps the column in a try ... otherwise expression under the hood.
let Source = Excel.CurrentWorkbook(){[Name="Numbers"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}), #"Replaced Errors" = Table.ReplaceErrorValues( #"Changed Type", {{"Value", 0}} ) in #"Replaced Errors"This is equivalent to transforming the column with try [Value] otherwise 0.
6.2 Custom reusable function for safe conversion
You can encapsulate the try ... otherwise pattern in reusable M functions for consistent error handling.
let SafeNumberFromText = (txt as any, optional default as nullable number) as nullable number => let Fallback = if default = null then null else default, Result = try Number.From(txt) otherwise Fallback in Result in SafeNumberFromTextYou can store this function in a dedicated query and invoke it wherever conversions may fail.
6.3 Logging errors to a separate table
In enterprise scenarios, instead of simply replacing errors with blanks or zero, it is often better to log them into a separate error table.
let Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content], #"Add Try Record" = Table.AddColumn( Source, "TryResult", each try Number.From([RawValue]) ), #"Expanded Try" = Table.ExpandRecordColumn( #"Add Try Record", "TryResult", {"HasError", "Value"}, {"HasError", "Converted", "Error"} ), #"SuccessfulRows" = Table.SelectRows(#"Expanded Try", each [HasError] = false), #"ErrorRows" = Table.SelectRows(#"Expanded Try", each [HasError] = true) in #"SuccessfulRows"The #"ErrorRows" table can be loaded separately for monitoring and root cause analysis.
7. Design principles for robust TRY...OTHERWISE usage
7.1 Do not silently swallow critical errors
While it is tempting to convert all errors to null or zero, doing so can hide data quality issues that should be addressed at the source or upstream process.
Use try ... otherwise to avoid blocking refreshes, but also surface aggregated error statistics or log them for visibility.
7.2 Use meaningful fallback values
Fallbacks should be chosen with care; for example, use null for unknown values, a sentinel constant where appropriate, or a text value such as "Error" for categorical columns.
7.3 Keep business rules separate from error handling
Try to separate business transformations (such as normalization, lookups, or mapping tables) from technical error handling; this makes your queries easier to maintain, test, and reuse.
7.4 Document your error handling strategy
For large models, always document where and how you use try ... otherwise, especially when using silent fallbacks, so that future maintainers understand the implications on data quality.
8. Worked example: full error handling pattern
The following end-to-end example demonstrates a robust pattern that loads raw data, attempts type conversion with try, separates successful and failed rows, and exposes a final clean table for reporting.
let // 1. Load raw data Source = Excel.CurrentWorkbook(){[Name="SalesRaw"]}[Content],
// 2. Add try records for critical columns
#"Try Amount" = Table.AddColumn(
Source,
"AmountTry",
each try Number.From([Amount])
),
#"Try Date" = Table.AddColumn(
#"Try Amount",
"DateTry",
each try Date.From([DateText])
),
// 3. Expand try results
#"Expand Amount" = Table.ExpandRecordColumn(
#"Try Date",
"AmountTry",
{"HasError", "Value"},
{"AmountHasError", "AmountValue"}
),
#"Expand Date" = Table.ExpandRecordColumn(
#"Expand Amount",
"DateTry",
{"HasError", "Value"},
{"DateHasError", "DateValue"}
),
// 4. Derive clean table and error table
#"Clean Rows" = Table.SelectRows(
#"Expand Date",
each [AmountHasError] = false and [DateHasError] = false
),
#"Error Rows" = Table.SelectRows(
#"Expand Date",
each [AmountHasError] = true or [DateHasError] = true
),
// 5. Project final clean columns
#"Final Clean" = Table.SelectColumns(
#"Clean Rows",
{"Customer", "AmountValue", "DateValue"}
)
in
#"Final Clean"This pattern scales well to complex models and helps ensure that reporting tables are free of technical errors while still preserving failed rows for investigation.
9. Summary checklist for Power Query TRY...OTHERWISE
| Goal | Recommended TRY...OTHERWISE pattern | Notes |
|---|---|---|
| Avoid refresh failure on bad data | Wrap risky expressions with try ... otherwise null | Log counts of nulls in key columns. |
| Replace errors with default value | Use Table.ReplaceErrorValues or try ... otherwise <default> | Choose defaults that reflect real business semantics. |
| Debug and categorize errors | Use try without otherwise and expand [HasError], [Error] | Build a separate error table for monitoring. |
| Protect against schema changes | Wrap column references in try [Column] otherwise null | Especially helpful when upstream teams modify views or files. |
| Protect web and API calls | Wrap Web.Contents and parsing in try ... otherwise null | Filter out null responses and track failures separately. |
FAQ
Does TRY...OTHERWISE always prevent a Power Query refresh from failing?
No, TRY...OTHERWISE only protects the specific expression where it is applied.
If other steps or expressions outside that scope fail, the query can still fail during refresh, so error handling must be applied consistently.
Is TRY...OTHERWISE bad for performance in Power Query?
TRY...OTHERWISE itself is not inherently slow, but it can affect query folding and prevent certain operations from being pushed down to the data source, which can lead to performance degradation.
For best performance, keep TRY...OTHERWISE close to the final steps in your query and monitor folding status.
Can I see the actual error message when using TRY?
Yes, when you use TRY without OTHERWISE, the resulting record contains an Error field with detailed information such as the error message and sometimes additional context.
Expand the record in a table to inspect the error description and use it for logging or categorization.
When should I use null versus a numeric default like 0 as a fallback?
Use null when the value is unknown or invalid and you want to distinguish it from a legitimate numeric value, and use 0 only when business rules explicitly treat missing or invalid values as zero.
Using 0 as a fallback without proper justification can distort aggregates and KPIs in your reports.
Is it better to handle errors in the source system or in Power Query?
Whenever possible, address systematic data quality problems in the source system, because this improves all downstream consumers, not only Power Query.
However, TRY...OTHERWISE in Power Query is still essential as a defensive layer for transient issues, schema drift, and integration across multiple systems that you cannot fully control.