Bubbling Up HTTP Status Errors In Power Query

Tony McGovern takes us through a method involving Power Query + M of giving end users useful information when a web request fails:

So how does this relate to error tables? Like most well-documented APIs, the U.S. Census Bureau API has a page devoted to listing and describing all the possible response codes that can be returned by their service. I take this information and build an internal table within the query that defines and describes these response codes in my own words. I’m now able to throw custom messages that make the difference between a 400 response code and a 404 response code more obvious.

For example, in the code below, I use the Error.Record function to create individual records that allow me to catch these unsuccessful requests and throw my own custom error messages to the user. I then create an extra field in each record called ‘Status’, which maps each HTTP response code returned by the API to a corresponding error message of my choosing:

There’s a bit of work, but the end result is a fairly simple explanation for end users.