Custom Connectors: Introducing Table.ViewError & Terminal Errors

If a table view handler raises an error, Power Query’s default behavior is to suppress the error and instead perform the requested operation internally. In a nutshell, Power Query internally does whatever it was that the user requested, since the view was unable to do so.

But what if this isn’t the desired behavior—what if a view handler should stop an operation from taking place, so the user sees it dying with an error?

Background

Traditionally, raising an error is a view handler’s way of signaling Power Query that the handler declines the request to fold a particular operation.

Below, handler GetRowCount raises an error. When Power Query receives this error, it recognizes that the view doesn’t want to handle the row count operation, so Power Query switches to reading rows from the table and counting them itself.

let
  View = 
    Table.View(
      null,
      [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => #table({"A"}, {{123}}),
        GetRowCount = () => 
          error [Reason="Operation Not Allowed", Message="Rows may not be counted."]
          //error Table.ViewError([Reason="Operation Not Allowed", Message="Rows may not be counted."])
      ]
  )
in
   Table.RowCount(View) // returns 1, even though GetRowCount raised an error

The error causes the GetRowCount operation to not fold, but does not prevent rows from being counted. Rather, Power Query takes on the responsibility to perform the row count itself, which completes successfully.

Introducing Table.ViewError

But what if you want a view handler’s error to altogether terminate an operation? That is, when your handler raises a particular error, you do not want Power Query to take on performing the operation internally itself, but instead want the error surfaced to the user.

Up until recently, to my knowledge, there hasn’t been a way to do this—but that has now changed, thanks to the new function Table.ViewError.

In essence, Table.ViewError internally tags (or registers) an error definition record so that a special behavior occurs when it is raised: If a view handler raises a Table.ViewError-tagged error, the error will terminate the operation at hand instead of being interpreted as a signal for Power Query to take over processing the operation.

let
  View = 
    Table.View(
      null,
      [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => #table({"A"}, {{123}}),
        GetRowCount = () => 
          //error [Reason="Operation Not Allowed", Message="Rows may not be counted."]
          error Table.ViewError([Reason="Operation Not Allowed", Message="Rows may not be counted."])
      ]
  )
in
   Table.RowCount(View) // dies with an error, instead of Power Query internally performing the row count

Why?

Why might we want this special behavior? After all, the default of Power Query completing any operation that a view handler declines is normally desired: successful completion is normally the goal, whether or not folding takes place.

Turns out, the special Table.ViewError behavior is particularly useful when implementing native query support, a capability that is currently in the process of being made available (sounds like a good topic for another post…).

(Aside: If you need help with building a custom connector, please let me know….)