Power Query: try … otherwise on list item expression

Question

Why does catching the following index-error fail:

let
    Source = "abc",
    letter_list = Text.ToList(Source),
    try_on_index_error = try letter_list{5}
in
    try_on_index_error

The try statement does not return an Error record. It keeps throwing the error as if no try was present.

In this case it works as expected, returning an Error record as a valid result of the query:

let
    Source = "abc",
    try_another_error = try Source + 1,
    Error = try_another_error[Error]
in
    Error

https://docs.microsoft.com/en-us/power-query/handlingerrors#catching-an-error-with-try-and-otherwise

https://docs.microsoft.com/en-us/powerquery-m/m-spec-consolidated-grammar#error-handling-expression

To me, this looks more like a bug than something with a good explanation. It seems like an index out of bounds should be something that can be handled with try rather than breaking the query with an "Unexpected error". — Alexis Olson
Take a look at Lazy Evaluation & Query Folding in Power BI / Power Query. If you buffer your list, it will work as expected: letter_list = List.Buffer(Text.ToList(Source)), — Ron Rosenfeld
Cheers @ron, I think that would help others as an answer, too. (Your solution works fine.) One thing I wonder about: I also ran my un-buffered code for a valid item access like try letter_list{1} and here the Error record is returned (of course with [Error][HasError] = false). — pylo

pylo pylo · Accepted Answer · 2021-08-24T17:24:15

It is naturally hard to answer questions on the behavior of closed-source software. However, the comment by Ron Rosenfeld solved my trouble:

letter_list = List.Buffer(Text.ToList(Source))

The reference to lazy evaluation gives an explanation for the unexpected behavior. My furher rationale is: If lazy evaluation kicks in, try statements retard to syntax checking, only.

Power Query: try … otherwise on list item expression

1 Answers