Web.Contents and 404 page not found in Power BI

The M language (empowering Power Query and Power BI) supports the try ... otherwise ... feature. It is handy to catch errors when processing some of the rows and handle these cases in alternative paths.

Unfortunately this feature is not intuitive when surrounding a call to the function Web.Contents(). Intuitively you could expect that an answer 404: page not found would start the otheriwse path. But it’s not the case, an error will throw!

To correctly manage this kind of error, you’ll need to add a second parameter to your Web.Contents call (after the url): [ManualStatusHandling={404}]). This parameter specifies to Web.Contents() that you don’t want to apply the default behavior of Power BI when receiving a 404 error: you’ll handle this case by yourself and not stop the load of the table.

The pragmatic option is probably to continue your transformation flow independently of the response received from the web server. Your next step will (probably) miserably fail and you’ll be able to add a try ... otherwise ... on this step to handle an error.

But, if you really want to do this cleanly, you’ll have to parse the metadata of the result of Web.Contents. The function Value.Metadata gives you this opportunity. In the metadata, you’ll find a Response.Status field where you’ll be able to check the value received: 404, 100, … Based on this value you can decide which path to follow with a simple “ìf … then … else“`

  Source = Web.Content(
    , [ManualStatusHandling={404}])
  ResponseCode = Value.Metadata(Source)[Response.Status],
  NextStep = if ResponseCode=404 then ... else ...

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s