Power Query M Primer (Part 15): Error Handling

, , , ,

Your Power Query is skipping merrily along its mashup way. Then, bam! Something bad happens! Uh oh! What do you do when an error raises its ugly head? Or, for that matter, what if code you write detects an anomaly and you want to announce this fact in an informative manner?

Thankfully, M has error handling capabilities, allowing you to both raise and handle runtime errors. We’ll learn how to do both.

Important: If you’re familiar with the idea of an exception from other programming languages, Power Query’s error handling is different in at least one significant respect from what you may be familiar with.

Let’s get going!

Series Index

Announcing an Error

In Power Query, each expression must produce something. Ideally, this is the expected value. However, there’s an alternative: an expression can raise an error, which is a special way of indicating that the expression could not produce a value.

The main way to raise an error is by using keyword error.

An error may be created by passing error a record describing the problem. As far as the record goes, all that matters are three fields: Reason, Message and Detail (any other fields will be ignored).

error [
  Reason = "Business Rule Violated", 
  Message = "Item codes must start with a letter", 
  Detail = "Non-conforming Item Code: 456"
]

Technically, all three fields are optional. Special behavior applies to Reason: If this field is missing, the error that’s raised will have its reason defaulted to “Expression.Error” (at least, with the version of the mashup engine I’m using—technically, the language specification doesn’t mandate this defaulting).

A helper method is available to assist with creating this record. Error.Record simply creates the three-field error description record for you, with the function’s first argument mapped to field Reason, the second to Message and the third to Detail. Unlike the above build-your-own-record approach, Error.Record requires that you provide a Reason; its other two arguments are optional.

error Error.Record("Business Rule Violated", "Item codes must start with a letter", "Non-conforming Item Code: 456")

Using Error.Record is more idiomatic, but it’s up to you as to which you prefer.

Both of the above examples produce an equivalent error:

Business Rule Violated: Item codes must start with a letter 
Details: Non-conforming Item Code: 456

Looking at the above screenshot, it’s easy to see how the three fields map to the error messaging that’s displayed.

In lieu of a record, error also accepts a string. The resulting error will have its Message set to the provided string and its Reason set to “Expression.Error” (at least, that’s the default Reason with the mashup engine version I’m using—technically, the language specification doesn’t mandate this defaulting).

error "help!" 
Expression.Error: help!

Ellipsis Shortcut

There’s also a shortcut operator for raising errors which comes in handy during development.

Let’s say you want to test a mashup that’s under development where you haven’t yet implemented every branch of each expression. Of course, since each branch must either return a value or raise an error, you can’t test run your query without putting something as a placeholder in those unimplemented branches, but what should you use?

When you encounter a situation like this, consider the ellipsis operator (...). When invoked, ... raises an error something like “Expression.Error: Not Implemented” or “Expression.Error: Value was not specified” (the exact wording depends on your mashup engine version).

Here’s a bit of code where the developer hasn’t yet implemented the if statement’s else branch so is using ... as a placeholder:

if Value then DoSomething() else ... // when Value evaluates to false, "..." is called, which raises the placeholder error

(Notice how keyword error is not used. The ellipsis operator both defines and raises the error. Very short, sweet and simple to use.)

Special Behavior

What exactly happens when an error is raised? What special behavior does raising an error entail that sets it apart from simply returning an ordinary value?

Let’s start with an expression:

SomeFunction(GetValue())

When evaluated under normal circumstances, first GetValue() is executed. Then, the value it produces is passed into SomeFunction(). Lastly, SomeFunction()‘s result is returned as the expression’s output.

Heaven forbid, but suppose instead that GetValue() raises an error. Immediately, further execution of the expression stops. SomeFunction() is not called. Instead, GetValue()‘s error becomes the expression’s output: it is propagated (a.k.a. raised) to whomever or whatever invoked the expression.

What happens next depends on whether that whomever or whatever can hold a value: the error may be contained or may become the mashup’s top-level error. Only in case of the latter does the error cause the mashup as a whole to terminate.

Error Containment

If the error is encountered by an expression that defines something holding a value (like the expression for a record field, a table cell or a let variable), the error is contained by that something—its effects are limited to that something and any logic that attempts to access that something’s value.

Below, the effects of GetValue()‘s error are contained to the portion of the larger mashup affected by it. The error does not terminate the entire mashup; rather, the mashup completes successfully and returns a valid record. Only FieldB and FieldC are errored because they are the only “somethings” affected by the error.

let
  GetValue = () => error "Something bad happened!",
  DoSomething = (input) => input + 1,
  Result = [
    FieldA = 25,
    FieldB = DoSomething(GetValue),
    FieldC = FieldA + FieldB
  ]
in
  Result
[ FieldA = 25, FieldB = Error, FieldC = Error ]

This containment of errors brings with it another special behavior: When an error is contained, the error is saved into the something that contains it. Throughout the remainder of the mashup’s execution, any attempt to access that something’s value causes the saved error to be re-raised. When an access attempt occurs, the logic that originally caused the error is not re-evaluated to see if it now will produce a valid value; that logic is skipped and the previously saved error is simply re-raised.

Below, Data‘s GetDataFromWebService() is only evaluated once, even though Data itself is accessed twice. The second access attempt receives the error saved from the first access.

let
  Data = GetDataFromWebService() // raises an error
in
  { List.Sum(Data[Amount]), List.Max(Data[TransactionDate]) }

Top-Level Errors

When an error is encountered, if nothing contains it, the error is propagated from the mashup’s top-level expression (the mashup’s output clause) to the host environment as the mashup’s result. Execution of the mashup then stops.

This mashup’s top-level expression errors. Nothing is present to contain the error, so the mashup dies, outputting the error as its result:

let
  GetValue= () => error "Something bad happened!",
  SomeFunction = (input) => input + 1
in
  SomeFunction(GetValue())

The below mashup’s error is first contained in Result but then the top-level expression accesses Result which results in the error being re-raised to the top-level expression. Since nothing contains the error this time, it becomes the mashup’s output—like the preceding, the mashup dies with the error.

let
  GetValue= () => error "Something bad happened!",
  SomeFunction = (input) => input + 1,
  Result = SomeFunction(GetValue())
in
  Result

Containment vs. Exceptions

Power Query’s error containment behavior sets it apart from typical exception-based programming languages. In the world of exceptions, an error automatically propagates all the way to the host environment and so causes the program to die with an error—unless special handling is put in place. With M, an error is automatically contained, so long as something is present to contain it, allowing the mashup as a whole to complete successfully even if select data items could not be computed.

Error containment is a great behavior considering M’s target use case: processing data. Suppose the expression defining a table column value errors for one cell out of the entire table. In an exception-based world, this error might cause all processing to terminate. In M’s world, the error simply affects that single cell and any code that accesses that cell. Processing continues and the decision of whether the error is significant is left to whatever code consumes the cell’s value.

In fact, due to M’s laziness, if nothing ever uses that cell’s value, the error-raising expression may never be evaluated, and so the error never raised, because the output it would produce isn’t needed.

let
  Data = #table({"Col1"}, {{"SomeValue"}, { error "bad" }})
in
  Table.RowCount(Data)

Above, row and column values are not needed to produce the requested output (the count of rows), so the second row’s error expression has no effect.

While error containment is a great default behavior, what if it doesn’t suit your needs? In particular, with tables, what if it’s important to differentiate between rows with errors and those without? Perhaps you’re not accessing row contents directly, so aren’t doing anything that would trigger error propagation, but still want to know which rows have an error somewhere in them and which do not. Table.SelectRowsWithErrors and Table.RemoveRowsWithErrors are likely just what you need.

let
  Data = #table({"Col1"}, {{"SomeValue"}, { error "bad" }})
in
  [
    RowsWithErrors = Table.RowCount(Table.SelectRowsWithErrors(Data)),
    RowsWithoutErrors = Table.RowCount(Table.RemoveRowsWithErrors(Data))
  ]

Handling Errors

With an understanding of raising errors tucked away, what do you do if you’re handed an error? Surely there’s a graceful way to handle it—some way to try to resolve it!

That’s it—that’s the keyword: try. try allows you to attempt to handle an error by taking remedial action.

try comes in two variants.

try ExpressionToTry otherwise FallbackExpression
try ExpressionToTry

The first version, try with otherwise, tries to execute the expression to try. If that expression returns a value, try simply returns that value. If, instead, the expression errors, the otherwise expression is evaluated and whatever it outputs is returned. In essence, if the first expression errors, fallback to the second expression.

try Number.FromText(input) otherwise 0

If Number.FromText returns a value, then that value is returned from try. Instead, if Number.FromText raises an error, try handles that error, replacing it with the output produced by the otherwise expression (in this case, the value 0). So, if input can be parsed to a number, that number is returned; otherwise, a default value of 0 is returned.

Keep in mind that only the expression directly to the right of try will have its errors caught and replaced. If the otherwise expression returns an error, that error won’t be handled by the try coming before it. Of course, since the otherwise expression is itself just an expression, you could put a try inside that expression to handle errors raised from it.

try GetFromPrimary()
  otherwise try GetFromSecondary()
    otherwise "Having problems with both servers. Take the rest of the day off. "

Try with otherwise works well in a situations like text-to-number parsing, but it can leave something to be desired in more complex scenarios. Its catch is that it is indiscriminate: it replaces any error by evaluating the fallback expression. Sometimes, the desired remedial action differs based on the specifics of the error encountered. Plain vanilla try allows us to handle this possibility.

try SomeExpression

This form of try always returns a record.

If the tried expression completed successfully, this record is in the form of:

[
  HasError = false, 
  Value = (whatever the tried expression returned)
]

If the tried expression raised an error, the returned record looks like:

[
  HasError = true,
  Error = (record describing the error that was raised)
]

For example:

let
  DoSomething = () => 45,
  Result = try DoSomething()
in
  Result // [HasError = false, Value = 45]
let
  DoSomething = () => error "bad",
  Result = try DoSomething()
in
  Result // [HasError = true, Error = [Reason = "Expression.Error", Message = "bad", Details = null]

(Note: The record placed in field Error will contain exactly three fields: Reason, Message and Details. This is true even if the record originally used to define the error left off one or more of these fields [remember: they’re optional when defining the error] or if it included additional fields.)

The record returned by try can be used to implement conditional remediation logic. Below falls back to the secondary data source only if the primary errors because its server is unreachable.

let
  Primary = try GetDataFromPrimary(),
  Source = 
    if Primary[HasError] = false then Primary[Value] // if primary is good, use what it returns
    else if Primary[Error][Reason] = "External Source Error" and Primary[Error][Message] = "Server is unreachable" then GetDataFromSecondary() // if primary errored because it's source is unreachable, fall back to secondary
    else error Primary[Error] // if primary had problems but we decided not to fall back to secondary, re-raise the original error returned by primary
in
  Source

In contrast, using try with otherwise, we could fallback to secondary if primary raised an error—but we’d fallback on any error, not just when the primary server is unreachable:

try GetDataFromPrimary()
  otherwise GetDataFromSecondary()

Scope

In order have an effect, error handing must occur at a level where the error is encountered. Error handling has no effect on errors that are contained at a different level.

let
  Data = #table({"Amount"}, {{10}, {error "help!"}, {error "save me!"}})
in
  try Data otherwise 0

Result‘s try doesn’t do anything for this mashup. Apparently, the developer hoped it would replace any column errors with zero, but that’s not how it was applied. The way things were wired up, if the expression defining Data raises an error, try will replace that error with zero. However, in this case, Data returns a valid table. True, there are cells in that table with errors, but those errors are contained at the cell level. Since they do not affect Data‘s table-level expression, the try at the table expression level has no practical effect.

try does help with the following, but its effect may not be what the developer intended.

let
  Data = #table({"Amount"}, {{10}, {error "help!"}, {error "save me!"}})
in
  try List.Sum(Data[Amount]) otherwise 0

Above, List.Sum iterates through the values in Data[Amount], adding them up. If an expression defining an item value raises an error, that error is propagated out of List.Sum, causing the summation as a whole to abort. try handles this error, returning 0 in place of the total List.Sum would have output in error-free circumstances.

If that was the intention, great! However, if the intention was to replace any erroring items with 0 while allowing the summation as a whole to complete, try must be applied so that it handles errors at the table cell level—it needs to be wired in to receive errors from column value expressions.

At first glance, Table.TransformColumns(Data, (input) => try input otherwise 0) might seem like an option. Perhaps surprisingly, this logic does not catch errors raised by column value expressions. Why not? A function’s arguments are eagerly evaluated before their values are passed into the function. If that evaluation results in an error, the function is not invoked so never sees the error; instead, the error is propagated out to the caller. In the case of Table.TransformColumns, if a column value expression raises an error, the transformation function (e.g. (input) => ...) is not called, so its try cannot handle the error; instead, the error is propagated back to Table.TransformColumns.

The problem is that the column value expression needs to be evaluated inside the try. To achieve this, try stepping back to the row level. Wire in a function that receives a reference to the entire row. Then, inside your function, use the row reference to access the column’s value, wrapped in a try expression. Now, any errors raised as a result of that evaluation will be propagated to your try expression which can then handle them appropriately.

It’s not simple, but one of the simplest ways to get a column’s value via a row reference, work with it, then save the resulting output back to the table is to replace the column of interest by using Table.AddColumn followed by Table.RemoveColumns + Table.RenameColumns

let
  Data = #table({"Amount"}, {{10}, {error "help!"}, {error "save me!"}}),
  ErrorsReplacedWithZero = Table.AddColumn(Data, "NewAmount", (row) => try row[Amount] otherwise 0),
  RemoveOldAmount = Table.RemoveColumns(ErrorsReplacedWithZero, {"Amount"}),
  RenameNewAmount = Table.RenameColumns(RemoveOldAmount, {"NewAmount", "Amount"})
in
  List.Sum(RenameNewAmount[Amount]) // returns 10

I agree with you—the above is a complex solution to achieve something that seems like it should be straightforward. If you want to use an elaborate try, unfortunately, some form of working with the table at the row level is required. However, if all you need is to simply replace any error in a particular column with a default value (which is all the above example’s try does), Table.ReplaceErrorValues is your fiend.

let
  Data = #table({"Amount"}, {{10}, {error "help!"}, {error "save me!"}}),
  ErrorsReplacedWithZero = Table.ReplaceErrorValues(Data, {{"Amount", 0}}) // replaces any errors in column Amount with 0
in
  List.Sum(ErrorsReplacedWithZero[Amount]) // returns 10

Applying similar behavior to items in a list is more complex. There’s no List.ReplaceErrorValues library function and List.Transform(Data, (input) => ...) doesn’t help for the same reason that Table.TransformColumns doesn’t help with tables. Instead, the simplest solution may be to turn the list into a table, handle the error appropriately, then convert the table back to a list.

let
  Data = {10, error "help!", error "save me!"},
  DataAsTable = Table.FromValue(Data),
  ErrorsReplacedWithZero = Table.ReplaceErrorValues(DataAsTable, {{"Value", 0}}),
  BackToList = ErrorsReplacedWithZero[Value]
in
  List.Sum(BackToList) // returns 10

Rule Violations

You may not find yourself raising errors that often. Typically, the errors you encounter may come from data connectors and library functions. Don’t forget, though, that you can use errors to announce violations of expectations, such as to signify that a particular data item failed to conform to a business rule.

Say you’re processing a CSV file where values in the ItemCode column should always start with an “A”. Early in your mashup, you could check values for conformance to this rule, replacing abnormal values with errors. Later processing steps which access the column will be alerted if they attempt to work with rule-violating values (because of the errors that will be raised).

let
  Data = GetData(), // for testing use: #table({"ItemCode"}, {{"1"}, {"A2"}})
  Validated = Table.TransformColumns(Data, {"ItemCode", each if Text.StartsWith(_, "A") then _ else error Error.Record("Invalid Data", "ItemCode does not start with expected letter", _) })
in
  Validated

This approach may be of particular interest when writing a base query that several other queries will pull from, as it allows you to centralize your validation (think: the DRY principle) while ensuring that attempted users of erroneous data are forcibly alerted to the presence of the anomalies.

By no means is this the only means of centralizing validation logic. Another option is simply to define an extra column for the rule, set to true or false, based on whether the rule is complied with:

let
  Data = GetData(), // for testing use: #table({"ItemCode"}, {{"1"}, {"A2"}})
  Validated = Table.AddColumn(Data, "ValidItemCode", each Text.StartsWith(_[ItemCode], "A"), type logical)
in
  Validated

With this option, logic that cares whether ItemCode is valid is responsible to check ValidItemCode. If the developer forgets to perform this check, invalid data may be treated as valid. In contrast, the replace invalid data with errors approach ensures that logic attempting to access an invalid value is forced to recon with its nonconformance (because the access attempt raises an error).

Whether either of these options is appropriate will depend on your context.

Next Time

That’s it! There you have M’s error handling. Hopefully you don’t encounter too many errors in your Power Query work, but when you do, you know know how to handle them. You also know how to raise your own errors, for cases where you detect an anomaly or need a development placeholder.

Soon the plan is to talk about the behind the scenes way Power Query organizes things (sections) and the faculty M provides to annotate values with additional information (metadata). Before then, though, it’s time to look at how the type system works in Power Query.

Until then, happy coding!

Revision History

2020-02-10: Updated section Scope, explaining how to use a try expressions to handle errors in table cells.

2 thoughts on “Power Query M Primer (Part 15): Error Handling

  1. Gavin

    Another really helpful article, helping me to build my understanding of M. However I can’t quite get my head around how I can best use this technique in my situation. I simply want to know if one ofd my queries does not complete due to an error.
    Currently I use 32Bit Excel 2013 with pq and pp as add-ons. So I have a set of queries that manipulate and clean my source data into a results dataset that will be used by other queries. My colleagues and I run the query using Data>>Refresh>>All or some VBA code.
    But if something fails to refresh (lets say a field name has changed) there is no alert. The only way I know of telling is by viewing the queries in the PowerQuery Pane and looking for the orange warning triangles. I would like to either return an error to a table or to detect the error in VBA.
    The next step might be to explore how I can use the info in your blog to get a more sophisticated message or handle the error.

    Reply
    1. Ben Gribaudo Post author

      Hi Gavin,

      Interesting question. I’m not sure on the VBA side. On the idea of returning “an error to a table”, are you hoping to have a separate table that lists errors from your various queries (so all errors in one place) or instead to have the table whose query encountered the error return say only one row stating that an error occurred?

      Ben

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *