Step level error in Power BI / Power Query

In a previous blog post (here) I covered the types of errors that you might most likely find when using Power BI / Power Query.

In this Conditional Logic series, I’ve covered the try keyword in Power Query and how to use basically an IFERROR in your own code, but this was mainly done to handle errors at the “row” level.

What if we wanted to handle errors at the step level? That’s where we’ll focus in this blog post

I do highly recommend that you read this (url) and the previous blog post in this series before reading further.

Why do we get step level errors?

When we create a query with Power BI / Power Query, we commonly use our own environment, credentials and local system. This means that our query has been tested specifically for us, but this doesn’t necessarily mean that it’ll work for everyone.

See, each “step” that you see in the “Applied Steps” section is basically a variable that is dependent of the previous step. This means that in order to reach the “last step”, you first need to trace all of its dependencies and calculate those in order to reach the desired “step”.

Now, what happens if within any of those dependent steps you find out that there’s an error? For example, the step tries to do a transformation on a column “Name”, but that column name simply doesn’t exist in our query.

This will throw a step level error and simply halt (stop completely) the process. Nothing beyond that point will be evaluated and the process will be simply stopped – the only output of the query will be a error message specifying why the error occurred.

Can we have our own error messages?

This is something that I thought only made sense for Custom Connectors since you can even specify some custom handling based on the response header that you get from your REST API, but I recently had a chat with Reid Havens (website) and he’s actually testing it with his own Power BI Templates, which is something pretty neat.

In short, yes – we can have our own Error messages.

But…How do we create our own error messages?

Well, for that we can use a function called Error.Record
which will create a record with the necessary field that the error needs. Here’s an example of that:

How do we use it? Well, the way to use it’s pretty simple. All you need to do is simply put the keyword error before it and voilá:

The reality is that we don’t really need the Error.Record function. It just helps us with the creation of the record, but you can manually create it like this:

Where do I even use that error? What do I gain out of it?

While I would recommend just using the native error messages that come with Power Query, there are some situations where crafting your own Error message would be a good idea. Again, it makes A LOT of sense when you’re dealing with a Power BI Custom Connector since you can say that when you receive a 424 error code could be translated into something meaningful and understandable by a human being.

This is something that should only be used by advanced users in specific scenarios.

You can read the documentation about how to use your own error message from here and I’ll be using it as the reference.

The example: Old School File Combination

Back in the day (2014-2015), Power Query used to combine “binaries”. They’ve changed that wording to be “files” since the mechanism and set of operations are different.

Back then they used a code similar to this:

Translated into our language, this is what you used to do:

1. Connect to your source
2. Remove all the columns but the one that contains the Binaries
3. Navigate to that column of binaries and transform it into a list
4. Use the Binary.Combine so you can combine all of your binaries into just one big binary
5. Read the newly combined binary using the Csv.Document function

The main issue with that approach is that it only works with flat files. Not really with more complex files such as xml, Excel, json and others. This meant that if you tried to use this approach, you’d hit an error, but many people didn’t understood this error.

What if we were to create a function that would do that binary combination but with a more descriptive error?

It’ll look like this:

A few caveats

Once you define your own custom error this way, it’ll supersede any other errors that might halt the operation. This means that if any errors occur, this new error will be the one that will be thrown, but you can manage this on a per step level basis, which would be time consuming to create, but you do have the ability to create it.

Don’t forget that Power Query loves to have some of its values lazily evaluated. This means that they could affect the try operation as those values need to be fully evaluated in order to yield an error or not. You could enforce this evaluation by using any of the Buffer functions (Table.Buffer, List.Buffer, Binary.Buffer) depending on what type of value you’re handling.

Again, I can’t stress this enough, you’d only use this technique if it’s strictly necessary and I’d encourage you to think twice before implementing it. Unless it’s specifically for a Power BI Custom Connector – in that case it usually has a REALLY good reasoning behind it.