Handling 404–Not Found Errors With Web.Contents() In Power Query And Power BI

One strange feature of the Web.Contents() function in Power Query and Power BI is that it doesn’t respond in a consistent way to the standard error handling techniques used in M. I don’t know if this is a bug or a feature, but it’s certainly something I’ve run into a few times so I thought I would share a description of the problem and a way of working around it.

First of all, what’s the problem? Imagine that you wanted to import a list of training courses from that fine UK Microsoft BI and SQL Server training company Technitrain into Power Query or Power BI. You could do so using an M query that uses the Web.Contents() function to get the course RSS feed, like so:

let
    Source = Web.Contents("http://technitrain.com/feed/")
in
    Source

 

But what happens if you get the URL wrong, or there’s some other problem with the site? For example, the following URL will give a 404 – Not Found error because the page doesn’t exist:

http://technitrain.com/blahblah

If you use it in an M query, like so:

let
    Source = Web.Contents("http://technitrain.com/blahblah")
in
    Source

Unsurprisingly you get an error:

DataSource.Error: Web.Contents failed to get contents from ‘http://technitrain.com/blahblah’ (404): Not Found

image

The real issue is, though, when you attempt to handle this error with a try/otherwise statement like so:

let
    Source = try 
                Web.Contents("http://technitrain.com/blahblah") 
             otherwise 
                "Error!"
in
    Source

…it doesn’t work and you get the same error! What’s strange is that in some cases a try/otherwise block in more complex code will work, so for example in:

let
    Source = try 
                Xml.Tables(
                 Web.Contents("http://technitrain.com/blahblah")
                )
             otherwise 
                "Error!"
in
    Source

… the error does get caught:

image

This thread on the Power Query forum suggests it’s something to do with lazy evaluation, but I haven’t been able to determine the situations when it does work and when it doesn’t.

Instead, it is possible to handle specific HTTP error codes using the ManualStatusHandling option in Web.Contents():

let
    Source = Web.Contents(
    	"http://technitrain.com/blahblah",
    	[ManualStatusHandling={404}])
in
    Source

The ManualStatusHandling option takes a list of numeric HTTP error codes, and if you run the above example you’ll see that the query no longer returns an error.

The next problem is, then, how do you know whether the request worked or not? It turns out that you can find out by looking at the metadata associated with the Source variable (for some background on getting metadata values see this post). So, for example, using Value.Metadata() on the Source variable like so:

let
    Source = Web.Contents(
    	"http://technitrain.com/blahblah",
    	[ManualStatusHandling={404}]),
    GetMetadata = Value.Metadata(Source)
in
    GetMetadata

Returns a record which, among other things, contains the HTTP response code:

image

Therefore you can use something like the following pattern to trap 404 errors:

let
    Source = Web.Contents(
        "http://technitrain.com/blahblah",
        [ManualStatusHandling={404}]),
    GetMetadata = Value.Metadata(Source),
    GetResponseStatus = GetMetadata[Response.Status],
    Output = if GetResponseStatus=404 then "Error!" else Source
in
    Output

17 responses

  1. Pingback: Dew Drop - August 10, 2016 (#2306) - Morning Dew

  2. Great job Chris. Quick question, while still on the Web.Contents function. Have you worked on scenarios where you require OAUTH 2 authentication (with anonymous login). And more importantly how to build M code to automatically renew the access_tokens for calls to Twitter Analytics API, Facebook’s Graph API and Linked In’s API?

    • Power BI/Power Query doesn’t handle OAUTH2 nicely yet. If you google you’ll find some examples of how to do it, but honestly my recommendation is not to try until the official support is available.

  3. An M binary value can be thought of as a Func in C# terms, if that helps. When you invoke File.Contents or Web.Contents, you don’t actually get the contents of the file or URL; you get a pointer to the contents. As long as no one looks at any of the bytes, we don’t actually try to access the data. Functions like Xml.Document or Json.Document actually need the bytes, so they force the data to be loaded.

  4. Pingback: Pushing Data From Excel To Power BI Using Streaming Datasets – Chris Webb's BI Blog

  5. Hi Chris
    Just starting to use Power BI and have come across your article.

    I have replicated your code but in some cases 200 is coming back when it should be 404.

    The code I have is

    let
    Source = Web.Contents(“https://www.google.com/shopping/ratings/account/metrics?q=hgdstye3.com&c=GB&v=2”, [ManualStatusHandling={404}]),
    GetMetadata = Value.Metadata(Source)[Response.Status]
    in
    GetMetadata

    Any ideas why

    Any help is much appreciated

  6. Quick question: I’m trying to use the metadata to determine the response code first before accessing the response body. However, this seems to trigger 2 http queries(1 from the metadata, the other from trying to access the response), it’s probably to do with the way how execution tree are evaluated in M. Are you seeing the same thing happening, and is there are good way to avoid the repeated queries?

  7. Exactly the solution I am looking for however I am running into an issue. I used your example but receive the error “An error occurred in the ‘’ query. Expression.Error: The field ‘Response.Status’ of the record wasn’t found”.

    Here is my code which is a function that is called for every record in a table using the “num” column. I blanked the url out with “xxx”. you will also see I have a delay added as I am looping this call through a table of records puling additional data from the API for each record and the delay prevents hitting the RateLimit.

    This all works but I find randomly I will receive a 404 error while it is looping through the table executing the function. I can only assume the API for some reason returns this error or maybe I am still hitting some kind of rate limit causing the error. I am looking to handle the error, replace the response with something to put into the table indicating it was not able to return the data, and then move on.

    1st issue, it doesn’t seem to find “GetMetadata[Response.Status]”
    2nd issue, When the query is successful it returns a list of JSON list of records which is expanded into a table, I would like to have it show “NA” or something for each value I pull into the table.

    (num as text) =>
    let
    url = “api/v3/xxxxxxxx/details?num=” & num,
    ApiCall = ()=> Json.Document(Web.Contents(“https://xxx.xxxxxxxxx.com/”,[RelativePath=url, ManualStatusHandling={404}])),
    Source = Function.InvokeAfter(ApiCall, #duration(0,0,0,0.002)),
    GetMetadata = Value.Metadata(Source),
    GetResponseStatus = GetMetadata[Response.Status],
    Output = if GetResponseStatus=404 then “Error!” else Source

    in
    Output

    • I think the problem is that the metadata is lost when you wrap Web.Contents in Json.Document. Can you split the call to Web.Contents into its own step, and then get the metadata from that step?

  8. Hi there,

    I have a function calling the api and checking the response with the code Value.Metadata(Source).
    If the code status is 200 I read the return with json.document, if no I return a json for the code status.
    However, I noticed the the code calls 2x the api (1 for Value.Metadata and 1 for Json.Document).
    How can I avoid it ?

    This api returns the code 403 when there is no row for the order.
    So I checked on MS docs https://docs.microsoft.com/en-us/power-query/handlingstatuscodes :
    says “Extensions are able to use the ManualStatusHandling option with status codes 401 and 403, which is not something that can be done in Web.Contents calls made outside of an extension context (that is, directly from Power Query).”

    Try catch is not able to handle the exception. Is there any workaround for it ?

    Thanks.

    Ricardo

Leave a Reply to Julian Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: