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
SourceBut 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
SourceUnsurprisingly you get an error:
DataSource.Error: Web.Contents failed to get contents from ‘http://technitrain.com/blahblah’ (404): Not Found
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:
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
SourceThe 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
GetMetadataReturns a record which, among other things, contains the HTTP response code:
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
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.
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.
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
Maybe something to do with the caching that Power BI does? I would try adding the IsRetry=true option (see Curt’s comment on this post https://blog.crossjoin.co.uk/2017/01/06/web-contents-caching-and-the-excludedfromcachekey-option-in-power-bi-and-power-query/) to ensure that no caching takes place.
Cool will do I’ll let you know
Thank you
Sorry to be a pain , does the IsRetry go before the ManualStatusHandling e.g
“,[IsRetry = true],[ManualStatusHandling={404}]
as I get the following error
Expression.Error: 3 arguments were passed to function which expects between 1 and 2.
Details:
Pattern=
Arguments=List
It needs to be in the same record, so:
[IsRetry = true, ManualStatusHandling={404}]
Sorted it but still doesn’t sort the issue
I think you’ll need to use Fiddler to debug this, similar to what I show here: https://blog.crossjoin.co.uk/2018/05/03/troubleshooting-data-refresh-performance-issues-with-odata-data-sources-in-power-bi-and-excel-using-fiddler/
Thank you
Will have a look
Thank you again for your help
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?
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?
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
Hello,
Any update from anyone on the double api call when checking metadata?
I am facing exactly the same problem right now. I need HTTP 403 to fail silently instead of aborting to the modal Power Query auth settings.
Anybody seen a workaround?
Thank you so much.
Vitaly
Vitaly,
I also wanted my custom function to fail silently. This seems to be working for me:
URL = whatever,
Contents = Web.Contents(URL),
Metadata = Value.Metadata(Contents),
ResponseStatus = Metadata[Response.Status],
Source = if ResponseStatus>=200 and ResponseStatus<300 then Table.FromColumns({Lines.FromBinary(Contents, null, null, 65001)}) else null,
Charley
Charley,
Are you sure the server part of your URL is new and unique each time, so there is no settings associated with it?
Same problem here. It would be great if there is a workaround dealing with the fact ManualStatusHandling is not working from Power Query.
Hi, I’ve been running the refresh from a web data source (Jira Rest API filter query) with no issues, but it has suddenly stopped working. I can’t pinpoint exactly what might have caused if but am wondering whether it’s related to me changing the authentication method, as I wanted to see if using the web token would work in place of my credentials. Unfortunately, now even when I try to use my credentials again, I am unable to refresh the data. I’ve already tried clearing the local/global permissions within Power Query and PowerBI. I’ve also renamed the user.zip file so it would create a new one. Creating a new file with a new query (same data source) hasn’t worked either. I have also checked that I can still access the filter results using the browser window. Nothing I’ve tried has worked. Do you have any ideas as to how I can investigate further? Do you think I should reinstall Excel/PowerBI?
I’m curious how you would handle a (307): Temporary Redirect. The URL I’m using works fine if I use Web.BrowerContents, but then I have to create a big Html.Table statement. I’d rather use Web.Contents so that I can pass the ApiKeyName in the credentials. I tried to use [ManualStatusHandling={307}] but get an Expression.Error: ‘307’ is not supported by the ManualStatusHandling option.