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.
let
Source = Table.FromList({error "bad"}, Splitter.SplitByNothing(), {"Column1"}),
Data = Table.ExpandTableColumn(Source, "Column1", {"FirstName", "LastName"})
in
Data
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.
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.
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:
Table.Schema(Data)
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}?
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.
let
// 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 ...
in
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.
Hi Ben,
I’ve encountered a different kind of uncatchable error, and I’m wondering if you have any insight. I’m attempting to use a try expression on an undeclared and undefined variable. You can see this by putting just this code in the advanced editor:
try undefined_var
The mashup engine throws a top-level Expression Error instead of returning an error record. I’m hoping you can help me figure out how to catch this kind of error with try.
Thanks for looking into it, if you can! Your primer has been extremely helpful to me as a new PQ user.
Hi Dan,
When the mashup engine completes its evaluation of an expression, either a value is returned or an error is raised. These are the errors we can potentially catch.
However, if an expression contains a reference to a non-existent identifier, one way to look at it is that this problem keeps evaluation from completing. Since evaluation dies prematurely, it does not produce a value or raise a (catchable) error. Instead, the evaluation attempt terminates, so we cannot use code (e.g.
try
) to recover from this situation.