The Elusive, Uncatchable Error?

, , , ,

The error seems to escape catching. When the expression is evaluated, Query Editor displays the error. Try loading the query’s output into Microsoft Power BI or Excel and the operation dies with an error. Clearly, there’s an error—but if you wrap the expression with a try, the error isn’t caught! To the contrary, the record output by try reports HasError = false, even though if you access that record’s Value field, Query Editor again shows the error.

What’s going on?! Have you discovered an uncatchable error? Is this a Power Query bug?

The Problem

First, when does this strange error experience rear its ugly head? The particular case we’ll look at tends to show up sometimes when there is an error getting the first row of a table. For example, it could be a data connector that encounters an error as it attempts to retrieve the first row from a paged web API, perhaps because fetching the first page errored. Maybe it’s a use of Folders.Files(), where the path of the first file in the folder is so long it triggers a “DataSource.Error: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters” error. Perhaps it’s something else. Again, the key is that the problem relates to producing the first row of the table.

To help us investigate this quandary, here’s one way to demo this error condition. This example mimics a common pattern used to fetch paged data from an API. In a real-world implementation, Table.FromList‘s first argument would be a dynamically assembled list containing tables, one per page fetched from the API. For demo simplicity, however, we’ll instead use a hard-coded list. The expression for its first and only item raises an error instead of evaluating to a table—which is what this list would look like in the real world if the retrieval from the API died because the first page fetch errored.

  Source = Table.FromList({error "bad"}, Splitter.SplitByNothing(), {"Column1"}),
  Data = Table.ExpandTableColumn(Source, "Column1", {"FirstName", "LastName"})

Ask Query Editor to evaluate this expression and it displays an error (see below screenshot). As you might expect, attempting to load (refresh) the query into Excel or Power BI also fails.

UI rendering of error: 
"Expression.Error: bad"

Trying to catch this error by changing the let expression’s results clause from Data to try Data doesn’t work. The try does not catch the error (as indicated by the error record’s HasError = false), yet interestingly (and annoyingly!) if we attempt to access field Value, we encounter the error.

Error record showing unsuccessful attempt at catching the error using try

No doubt, there is an error. Clearly, try didn’t catch it. Is try broken? Does Power Query have a bug? Is this some kind of uncatchable error? What is going on?!?!

The Investigation

Is Data Raising an Error?

Now that we have a simple, reproducible problem case, time to investigate!

To start, let’s remind ourselves of a key M fact:

  • When evaluated, every expression either returns a value or raises an error.

I know. You’re utterly convinced (for obvious reasons) that Data is raising an error—but is it really? The alternative to an expression raising an error is for it to return a value. Is there any chance that Data is actually outputting a value?! Is there a way we can check?

Absolutely. If Data is returning a value, we will be able to get its type by using Value.Type, something that we can’t do with an error. Let’s try:

Value.Type(Data) // Query Editor displays: table

In fact, we can even read the table schema for Data:

Schema table screenshot

Based on the above tests, Data evaluates to a value (specifically, a value of type table). As this expression outputs a value, it isn’t propagating an error (an expression can output a value or raise an error, but not both simultaneously). This is why try hasn’t helped us: Since Data isn’t raising an error, try Data doesn’t catch the error.

More Confused Now?

“But there is an error,” you say. Yes, there is—but that error is not being triggered by the evaluation of Data. That is, the error is not being raised by, or propagated out of, Data.

Another relevant M fact that will prove itself most valuable to our investigation:

  • Streamed data types do not contain actual materialized data items. Instead, they contain a handle (i.e. an enumerator) which can be invoked to request that the appropriate data items be produced on demand and streamed one at a time.

When the expression Data is evaluated, a valid table value is returned. Since table is a streamed data type, this value does not contain any table rows.

This value is handed off to the consumer of the expression’s output, which in our demo is Query Editor (but could also be Power BI, Excel, etc., or even another M expression). Query Editor’s internal logic uses the handle contained in the table value to start an enumeration of rows. This triggers the appropriate Power Query logic to run to produce those rows, which causes the item defined as error "bad" to be read out of the list and evaluated. This evaluation results in the error being raised. Query Editor sees this error and renders it to screen.

To recap: Evaluating Data doesn’t raise the error, but instead returns a valid table value. Since Data doesn’t raise an error, try Data doesn’t catch the error. Instead, the error occurs when the consumer of the table (in this case, Query Editor) attempts to enumerate rows, which triggers the M code that raises the error.

So, the error is legit, as is the fact try Data doesn’t catch it. There’s no Power Query bug here.

But How to Catch It?

This error is also not uncatchable. Like any Power Query error handling scenario, the key is to put the try at a point where the error is actually being raised or propagated so that the try can “see” the error and so catch it.

How do we do this? There are a number of options. Perhaps one of the simplest is simply to ask for the table’s first row. This will cause an enumeration of rows to be started—the same thing Query Editor was doing internally when it encountered the error. However, this time, since our M code (not Query Editor) is performing these activities, any errors raised will propagate through our code, offering us the opportunity to catch them.

Let’s use the optional item access operator to request the first row, preceding it with a try:

try Data{0}?
Screenshot showing the error record (reflecting a caught error) produced by "try Data{0}?"

Yay! The error is indeed catchable—and has been caught. try isn’t broken. There’s no Power Query bug. We just needed for the error to occur in a place where it propagates through code we could surround with a try.

Putting a Solution All Together

This is great, except there’s a missing piece. We now know a way to catch the error—but when there is no error, we get just the first row. How do we get the full table?

Sounds like a good place to use an if statement—albeit a very unusual one: if Data{0}? is any then Data else ...

If the Data{0}? part raises an error, it will be immediately propagated, resulting in the rest of the if statement being skipped. Instead, if Data{0}? outputs a value, that value is tested with the assertion is any, which always computes to true, leading to the if statement’s then branch being chosen, which in turn returns the full table (Data). The if‘s else clause will never run, as the is any assertion will never evaluate to false. Nevertheless, M requires else to be present, so the error-raising shortcut ... is used for its body. As the else will never run, this ... will never raise an error; instead, it quietly sits there just to keep M happy.

  // error-raising example
  Source = Table.FromList({error "bad"}, Splitter.SplitByNothing(), {"Column1"}),

  // good data example
  // Source = Table.FromList({#table({"FirstName", "LastName"}, {{"Joe", "Jones"}})}, Splitter.SplitByNothing(), {"Column1"}), 

  Data = Table.ExpandTableColumn(Source, "Column1", {"FirstName", "LastName"}),
  Catchable = if Data{0}? is any then Data else ...
  try Catchable // is able to catch the first-row error
  // Catchable // returns the full table when no error occurs

leturns the full table when no error occurs

Yay! We now have a way to catch the seemingly uncatchable error! We’ve added logic which mimics what the consumer of the expression’s output (e.g. Query Editor) was doing internally when it encounters the error. Since our new logic was inserted so it runs before the consumer gets the table, it is positioned to see the error and can respond to it appropriately.

A Loophole

Did you notice a corner case where an error can slip by the above solution? This solution involves two enumerations of rows being started (one for Data{0}? and the other by the consumer when it reads Data‘s rows). In a real-world scenario, Data likely will be coming from a data connector pulling from an external data source. Depending on factors like query folding and whether the persistent query cache is in play, the two enumerations could lead to separate requests being sent to the external data source. If the error-causing situation happens between when the first and second request are answered, the first request (for Data{0}?) will complete successfully, resulting in the subsequent full enumeration of rows from Data erroring, and so not being caught by the try.

The chances of this error scenario may be quite small, but it does represent a corner case where errors can potentially slip by. To close this loophole, an option is to reduce the number of enumeration starts from two to one by replacing the fancy if statement with a call to Table.Buffer. This will perform just one enumeration of rows from its upstream, caching what it reads for sharing with all downstream consumers. When Table.Buffer performs its single enumeration, if a first-row enumeration stopping error like the one we’ve been chasing occurs, that error will be propagated out of Table.Buffer and so be catchable.

try Table.Buffer(Data) // catches the error we've been chasing

However, buffering can have performance and resource utilization impacts. As a general practice, for error handling, buffering is best saved for those situations where the alternatives are unacceptable. If we can live with the slight risk of an error slipping by and so being uncaught, we are probably better off using something like the fancy if statement instead of buffering.

Leave a Reply

Your email address will not be published.