Handling HTTP Errors in Power Query and Power BI

If you are working with web servers, either because you are trying to scrape data or you are using a web based API, you will be sending and receiving data via HTTP.

HTTP is the Hypertext Transport Protocol - it's just the name of the system used by web sites to transfer data. You use it every time you visit a web site

If your request results in an error, the web server (or API) will generate an error which is returned to Power Query.

The default behaviour is for Power Query to then spit out a message like this

If you are not familiar with HTTP or this type of error then this can be confusing. What exactly is the problem?

Download Sample Files

 

If you could handle this type of error in your code and provide a little more information to the end user, perhaps that would help them troubleshoot the issue and resolve the problem.

What if your error message was this

The message gives the user some idea of things to check (spelling) and tells them a way to confirm the URL is correct (type it into the browser).

Looking at the query, the URL it's trying to access is

Typing this URL into my browser gives this error

You may have already spotted that the URL is incorrect. It should end with microsoft-365 not microsoft-356

By giving a more informative error message and some steps the user can take to troubleshoot, we can help them fix, or at least understand problems that may occur.

Manual Status Handling

Manual status handling means you are going to write your own code to deal with errors. You have to tell Power Query you're going to handle them and you do this by specifying the ManualStatusHandling value when you make the Web.Contents request.

For example to tell Power Query that you will deal with 400 (Bad Request) and 404 (Not Found) errors the request would look like this

Where the list of error codes you'll handle are specified as a list of numbers {400, 404}

Handling errors means that you need to write you own error messages. To store these I've created a static data table that stores the error codes and the messages I want to display should the associated error occur.

The table is stored in a query called HTTP_Errors and looks like this

The ErrorCode column is a list of the error codes I'm handling so I can change the Web.Contents request to reflect this by replacing the { 400, 404 } list.

To check if an error has occurred you can use the Value.Metadata function

This gives you data like this, and I'm interested in the Response.Status

You can see the web server has responded with a 404 error. To access this value directly you can do so like this

Now that we can get the response code from the web server, we need to check if it is an error we want to handle. To do this you can use List.Contains to check if the ResponseCode is in the ErrorCodes column of the HTTP_Errors table.

If the web server's response code is an error we want to handle then the code needs to display the associated error message.

To access the error message, first I'll use List.PositionOf to get the row number for the error code.

Because table columns are lists you can use list functions on them. Lists are indexed from 0 so error code 404 is on row 3.

If the web server's response is not an error I want to handle then the code will just return the response as it is.

Putting this all together the code looks like this

If an error occurs that isn't listed in my HTTP_Errors table then Power Query will deal with that in the default way.

If no error occurs then the Response step contains the web server's response and further transformations can be carried out on it.