Power query обработка ошибок

In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error.

Power Query doesn’t have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it’s called try otherwise

In this post I’ll show you how to use try otherwise to handle errors when loading data, how to handle errors in your transformations and how to handle errors when your query can’t locate a data source.

Download Sample Excel Workbook

First up, let’s load data from this table.

I’ve already generated a couple of errors in this table, and of course I can obviously see them and I could fix them before loading into Power Query.

But when using Power Query this isn’t always the situation. Your query will be loading data without knowing what it is so how would it handle these errors?

Let’s load the data into Power Query and call it Errors from Sheet

Straight away you can see the errors in the column.

Now of course you could use Remove Errors but that would remove the rows with the errors and that’s not what I want.

Or I could use Replace Errors, but this doesn’t give me any idea what the cause of the error is.

I want to see what caused the error and to do this I’ll add a Custom Column and use try [End]

This creates a new column with a Record in each row

In this record are two fields. HasError states whether or not there’s an error in the [End] column

If there is an Error then the 2nd field is another record containing information about that error

If there isn’t an error, then the 2nd field is the value from the [End] column

If I expand the new column I get 3 new columns containing the HasError value which is boolean, and either an Error or a Value

Checking what’s in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query

There’s the Message, which is the error from the Excel sheet, and some errors give extra Detail, but not in this case.

If I expand this Error column I can see all of these fields.

I’ve ended up with a lot of extra columns here and it’s a bit messy so let’s tidy it up. In fact I’ll duplicate the query and show you another way to get the same information in a neater way

The new query is called Errors from Sheet (Compact) and I’ve deleted all steps except the first two.

What I want to do is , check for an error in the Try_End column, and if there is one I want to see the error message from Excel.

If there isn’t an error I want the value from the [End] column.

I can do all of this in a new column using an if then else

Add a new Custom Column called Error or Value and enter this code

What this is saying is:

• If the boolean value [HasError] in the [Try_End] column is true then
• return the [Message] in the [Error] record of the [Try_End] column
• else return the [Value] from the [Try_End] column

With that written I can remove both the End and Try_End columns so the final table looks like this

Checking for Errors and Replacing Them With Default Values

In this scenario I don’t care what the error is or what caused it, I just want to make sure my calculations don’t fail.

I duplicate the original query again, calling this one Error in Calculation, and remove every step except the Source step

I add a new Custom column called Result and what I’ll do here is divide [Start] by [End]

this gives me an error as I know it will in rows 1 and 3

so to avoid this, edit the step and use try .. otherwise

now the errors are replaced with 0.

Errors Loading Data from A Data Source

I’ll create a new query and load from an Excel workbook

Navigating to the file I want I load it

and loading this table

I’m not going to do any transformations because I just want to show you how to deal with errors finding this source file.

I’ll open the Advanced Editor (Home -> Advanced Editor) and change the path, so that I know I’ll get an error. Here I change the drive letter to X.

I don’t have an X: drive so I know this will cause the workbook loading to fail.

So that’s what happens when the file can’t be found so let’s say I have a backup or alternate file that I want to load if my main file can’t be found.

Open the Advanced Editor again and then use try otherwise to specify the backup file’s location

close the editor and now my backup file is loaded.

IMPORTANT: You can read the official documentation that Microsoft has on this topic from the following link (url).

If you haven’t read the first two posts (Part 1 | Part 2) in this series yet, I welcome you to do so before reading this one.

I also recommend that you check out this post on Query Error Auditing so you get a better understanding of what types of errors you can find in Power BI / Power Query.

This is a post on how to use error handling, similar to an IFERROR in DAX and Excel, but for Power Query (using its M language).

How does Error handling works in Excel & DAX?

In Excel and in DAX we have the IFERROR function which works like this:

=IFERROR( value, value_if_error)

Taken directly from the official DAX documentation:

Evaluates an expression and returns a specified value if the expression returns an error; otherwise returns the value of the expression itself.

It’s a pretty simple and straightforward function in DAX and Excel, where you can enter your formula in the “value” parameter and then, if you get an error from it, you can define what should be the output value in the “value_if_error” parameter.

The whole idea is that you can “catch” an error and use a different value when it finds an error.

How does Error handling works in Power BI / Power Query?

In Power Query the code is a bit different. Let’s see it in action and then talk more about it.

Imagine that we have an Excel workbook with a table like this

:

What we would like to create is a new column that should multiply the values from the [Price] and [Amount] columns to create a new Subtotal column.

One caveat, as you can probably see, is that this spreadsheet has some cells with errors on the [Price] column. In the event that we find an error on the Price column, we need to use the value from the [List Price] instead of the [Price] value.

The first thing that we need to do is import that table from Excel. If you’d like to follow along, you can download the workbook by clicking the button below:

Importing data from the Excel Workbook

I’ll be using Power BI Desktop for this, but you can use Excel as well.

The first thing that we need to do is select the Excel connector and connect to our file:

and once you get the “Navigator” window, you can select the table that reads “Sample”:

Notice how there’s a bunch of errors in that [Price] column just in the preview. Let’s hit the “Edit” button so we can go to the Power Query Editor Window.

Using error handling in Power BI /Power Query

Now that we have our data in the Power Query Editor window:

what we want to do is create a Custom Column, so we simply go to the “Add Column” menu and hit on “Custom Column”.

In there, we try do create a simple column that will multiply the [Price] column by the [Amount] column:

and as you can see, our [Subtotal] column has some errors.

We know that in Excel and DAX you can use IFERROR, but what can you use in Power Query ?

For Power Query, we need to hit modify that Custom Column code (just click the gear icon next to the Added Custom step) and add the following pieces to it:

try [Price]*[Amount] otherwise [Amount]*[List Price]

We need to use the keywords “try” and “otherwise”. It’s pretty easy to read, but it just says to try and evaluate the expression ([Price] * [Amount]) and if that gives an error, use the expression defined after the otherwise statement.

The result of that will look like this:

pretty simple! almost as simple as the IFERROR function in DAX and Excel where intellisense does explain you a bit how to use that function, but in Power Query you need to understand how this works in order to use it. Is nowhere in the User Interface of Power Query, so you need to write this code manually.

Understanding Errors

The workbook sample that I’m using is fairly simple. I’ve had experiences where some users / customers absolutely need to know when a specific error is found from an Excel Workbook.

What happens with Power Query is that it just flags any errors found as “Error” but, what if you needed to know WHY it shows as an error?

Let’s go back to our initial load of the file. Remember that in most cases Power Query will automatically try to add a “Changed Type” step, so what if we remove that step?

Well, I removed the step and I’m still seeing the errors and that’s because the error wasn’t triggered by a data type conversion, but rather it’s a source error, meaning that the error comes directly from the Excel Workbook.

In Workbook with vast amounts of rows, it’s hard to tell if there are any errors at all and doing a “Replace Errors” will not tell us why those errors occurred. We NEED to know what is the error from the source because we want to handle each type of error differently.

Error Message and Error Reason

To figure out what’s the reason why there’s an error, we need to use the “try” statement again.

Note how I only use “try” and not the “otherwise” statement. This will give me a new column with record values. We can expand those records like this:

the most important field from those records it’s the “Error” field which can be either a null or a record value:

and after expanding that column and deleting some others that we don’t need, I end up with this:

I’ve highlighted the most important field after this whole process which is the “Message” which tells me exactly the reason why this is an error.

I can later use this to my advantage and target specific errors differently or get a report of ALL the errors found on a series of files that my department / group uses. This is extremely helpful if you’re trying to validate everything and make sure that we don’t have any errors at the source.

Don’t forget that these same principles work for both Step and cell Value level errors.

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

• Introduction, Simple Expressions & let (part 1)
• Functions: Defining (part 2)
• Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3)
• Variables & Identifiers (part 4)
• Paradigm (part 5)
• Types—Intro & Text (Strings) (part 6)
• Types—Numbers (part 7)
• Types—The Temporal Family (part
• Types—Logical, Null, Binary (part 9)
• Types—List, Record (part 10)
• Tables—Syntax (part 11)
• Tables—Table Think I (part 12)
• Tables—Table Think II (part 13)
• Control Structure (part 14)
• Error Handling (part 15) [this post]
• Type System I – Basics (part 16)
• Type System II – Facets (part 17)
• Type System III – Custom Types (part 18)
• Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors (part 19)
• Metadata (part 20)
• Identifier Scope & Sections (part 21)
• Identifier Scope II – Controlling the Global Environment, Closures (part 22)
• Query Folding I (part 23)
• Query Folding II (part 24)
• Extending the Global Environment (part 25)
• More to come!

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 accompanied with a record describing the problem.

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

In the error definition record, five fields are relevant: Reason, Message, Message.Format, Message.Parameters and Detail. Technically, all these fields are optional, and any extra fields included in the error definition record will be ignored.

Special behavior applies to field Reason and the Message* trio of fields:

• Reason—If this field is missing, the error that’s raised will have its reason defaulted to “Expression.Error” (at least, this is true with the version of the mashup engine I’m using—technically, the language specification doesn’t mandate this defaulting).
• Message* Fields—Two options are available for defining the error’s message: Directly specify a Message, or use Message.Format + Message.Parameters to define a structured error message (see New M Feature: Structured Error Messages for more details).

As an alternate to creating the error definition record by hand, helper method Error.Record can be used to build the record. The function’s first argument maps to field Reason. The second to either field Message or, if a list is passed as Error.Record‘s forth argument, to Message.Format. Arguments three and four map to Detail and Message.Parameters, respectively. Unlike the above build-your-own-record approach, Error.Record requires that you provide a Reason; its other arguments are optional.

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

It’s up to you as to whether you prefer to create error definition records using [...] syntax or with Error.Record. In either case, ultimately, a record is being created which you hand over to error when you’re ready for the error to be raised.

Both of the above examples produce an equivalent error:

Looking at the above screenshot, it’s easy to see how the three values that were provided 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).

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:

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

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 attempts to use that cell’s value, its expression may not be evaluated, and so the error never raised. Why should the mashup engine waste effort computing something that will just be thrown away untouched?

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 three main variants:

// try otherwise
try ExpressionToTry otherwise FallbackExpression

// try catch
try ExpressionToTry catch (e) => FunctionBody
try ExpressionToTry catch () => FunctionBody

// plain try
try ExpressionToTry

try otherwise

The first version, try otherwise, tries to execute the expression to try. If that expression returns a value, try simply returns that value. If, instead, the expression errors, that error is ignored, the otherwise expression is evaluated and whatever that expression produces becomes the output of the try otherwise expression. In essence, if the first expression (the “to try” expression) errors, fallback to the second expression (the “otherwise” 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 otherwise works well in a situations like text-to-number parsing but it can leave something to be desired in more complex scenarios. Why? The catch is that the otherwise 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.

try catch

try catch allows us to handle this possibility. If the tried expression completes successfully (i.e. it returns a value), the value it produces is output. If, instead, the expression being tried raises an error, the catch function is invoked. This sounds very much like try otherwise, and it is—except for one very significant difference.

The catch function can be defined as accepting zero arguments or one argument. If a zero-argument function is used, then try catch is identical in behavior to try otherwise.

// both are equivalent in behavior
try Number.FromText(input) catch () => 0
try Number.FromText(input) otherwise 0

On the other hand, if the catch function is defined as accepting an argument, then when that function is invoked, it will be passed a record with details about the error that just occurred. This presents the possibility to dynamically adapt how the error is handled based on its specifics—a significant ability not possible with try otherwise.

let
  Source = 
    try GetDataFromPrimary()
    catch (e) =>
      // if the error is because primary is unreachable, fall back to secondary
      if e[Reason] = "External Source Error" and e[Message] = "Server is unreachable" 
      then GetDataFromSecondary() 

     // if primary had a different problem, reraise the error instead of falling back to secondary
      else error e 
in
  Source

An error details records (passed into the one-parameter catch function above, and also included in plain try‘s output, as we’ll learn shortly) will contain the five fields that make up an error in M: Reason, Message, Message.Format, Message.Parameters 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 that record included extra fields.

try catch has some interesting syntax rules for the catch function:

• It must be defined inline. Defining the function elsewhere and then simply referencing it by name isn’t allowed.
• Its parameter list must be defined using parenthesis syntax. The each shortcut isn’t allowed.
• Type assertions may not be used in the definition.

These rules make all of the following illegal:

// Not allowed -- catch needs to be defined inline; it cannot be a reference to a function defined elsewhere
let
  ErrorHandler = (e) => ...some code...
in
  try SomeFunction() catch ErrorHandler

// Not allowed -- each cannot be used here
try SomeFunction() catch each ...some code...

// Not allowed - type assertions may not be used here
try SomeFunction() catch (e as record) as any => ...some code...

try

Last but not lease, plain vanilla try evaluates the provided expression, then returns a record with details about the expression’s result.

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

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

For example:

let
  DoSomething = () => 45,
  Result = try DoSomething()
in
  Result // [HasError = false, Value = 45]

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

[
  HasError = true,
  Error = (error details record)
]

Example:

let
  DoSomething = () => error "bad",
  Result = try DoSomething()
in
  Result
// [
//    HasError = true, 
//    Error = [
//       Reason = "Expression.Error",
//       Message = "bad",
//       Detail = null,
//       Message.Format = "bad,
//       Message.Parameters = null
//    ]]
//  ]

Prior to try catch being added to M, implementing conditional remediation logic required using try with some boilerplate code, resulting in verbose expression like:

let
  Primary = try GetDataFromPrimary(),
  Source = 
    // if primary is good, use what it returns
    if Primary[HasError] = false 
    then Primary[Value] 

    // if the error is because primary is unreachable, fall back to secondary
    else if Primary[Error][Reason] = "External Source Error" and Primary[Error][Message] = "Server is unreachable" 
    then GetDataFromSecondary()

    // if primary had a different problem, reraise the error instead of falling back to secondary
    else error Primary[Error]
in
  Source

try catch achieves the same effect with less code, as this example (repeated from earlier) demonstrates:

let
  Source = 
    try GetDataFromPrimary()
    catch (e) =>
      // if the error is because primary is unreachable, fall back to secondary
      if e[Reason] = "External Source Error" and e[Message] = "Server is unreachable"
      then GetDataFromSecondary() 
 
     // if primary had a different problem, reraise the error instead of falling back to secondary
      else error e 
in
  Source

Again, try catch didn’t used to be part of M. Today, you may still find plain try used to implement adaptive error handling in legacy cases, but hopefully new adaptive handling logic will use the more succinct try catch syntax instead.

Moving forward, you (likely) will see plain try used much less.

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, {"Col1", (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 friend.

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

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

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

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

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.
• 2022-06-09: Revised to reflect M’s new try catch syntax, as well as to incorporate rudimentary mentions of M’s new structured error message functionality.

In this post, I am going to talk about the IFERROR function in Power Query. There is no explicit IFERROR function in Power Query but we have try and otherwise statement which works similar to the IFERROR function in Excel.

IFERROR in Power Query (try and otherwise) – Video

Power Query try and otherwise – Example 1

1. Consider this sample data. I have Employee ID, Total Pay, and Hours.
2. I created a Custom Column to calculate Pay per Hour

= [Total Pay] / [Hours]

Note – Emp ID 6 returns an error because 200 is divided by a text = “nil”. To correct the above error, I am going use try and otherwise within the formula

= try [Total Pay]/[Hours] otherwise null

As an output, I got a null instead of an Error.

Power Query try and otherwise – Example 2

The try and otherwise statement can also be applied to the entire step.

1. I deleted the Custom Column for Pay/Hours
2. And Changed Type, results in an error since it wasn’t able to find the previous step

This is a step-level error that happened at the Changed Type Step.

To fix this error I will surround my entire formula with the try and otherwise statement in the formula bar.

= try
    Table.TransformColumnTypes(Source, {{'Pay/Hours', type number}})
otherwise
    #'Removed Columns'

If the above formula (which is trying to change the type of Pay/Hours) returns an error then the otherwise statement returns the previous step – Removed Columns. The query throws no errors.

Power Query try and otherwise – Example 3

This time let’s write the try statement (without otherwise). Let’s calculate Pay/Hours but this time with only the try keyword.

Create a new Custom column with the following formula,

= try [Total Pay]/[Hours]

As a result, it gives me a column that contains records.

Expanding Custom column is going to return 3 more columns.

1. HasError – Shows TRUE if the formula resulted in an Error.
2. Value – Is the output of the formula with no errors.
3. Error – Again contains records that will describe the error upon expanding.

Upon further expanding the Error column.

We again get 3 more columns,

1. Reason – This tells the reason for the error.
2. Message – This shows what actually the error is.
3. Detail – Tells the information of the error, message about the error.

On further expanding Detail column we get even more details about the error on that particular row.

So this is a very crude way of doing some very basic error reporting on your data in case your Power Query steps result in any errors.

More on Power Query

Promote Double Headers in Power Query

5 Tricks to Reduce Steps in Power Query

Remove Top Rows and Combine Data from Multiple Excel Files

Quick VLOOKUP in Power Query

Dynamic Unpivoting in Power Query