John - Thanks for introducing me to the 'ManualStatusHandling' parameter within
Web.Contents
. I've never used it before but a quick test has me convinced that it should work for your purposes. (It's also convinced me to start incorporating this into my custom connectors in the future!)
In the following test, I'm requesting data from the U.S. Census Bureau's public API and I'm purposefully introducing a malformed URL. The API should respond with a 404 error code. If I add "404" to the list of values for the 'ManualStatusHandling' parameter, the request should fail silently, allowing me to catch the 404 error code in a subsequent step using the
Value.Metadata
function. Once caught, I can then throw a custom error message to the user.
Here's the test:
let
errors.badRequest = Record.AddField(Error.Record("Bad Request", "this dataset does not exist. It's most likely a misspelling in the value for 'vintage' or 'dataset'. Double check these values and make sure they're spelled correctly."), "Status", 404),
errors.serviceUnavailable = Record.AddField(Error.Record("Service Unavailable", "the server is too busy right now and needs a short break. Don't worry, you can try again in a bit."), "Status", 503),
errors.table = Table.FromRecords({errors.badRequest, errors.serviceUnavailable}) meta [Reason = type text],
variables.url = "https://api.census.gov/data/2015/BADURL/variables.json",
variables.response =
Web.Contents(
variables.url,
[
ManualStatusHandling={404}
]
),
variables.responseMetadata = Value.Metadata(variables.response),
variables.responseCode = variables.responseMetadata[Response.Status],
variables.responseHeaders = variables.responseMetadata[Headers],
variables.json = if variables.responseCode <> 200 then error errors.table{List.PositionOf(errors.table[Status],variables.responseCode)} else Json.Document(variables.response)
in
variables.json
I hope this helps.
------------------------------
Tony McGovern
co-Founder & Data Scientist
Emdata Inc.
------------------------------
Original Message:
Sent: 01-11-2019 09:27 AM
From: Tony McGovern
Subject: Error handling with OData.Feed
John -
A few questions to consider:
- Which OData version does the endpoint implement?
- Are you using Fiddler to monitor the traffic?
- If so, what's the error response code returned by the endpoint for your use case?
- Are you capturing these error response codes in an Error.Record?
- What headers are you passing to the endpoint?
- Are you using the
IncludeAnnotations
and IncludeMetadataAnnotations
options in the request?
- If so, what annotations terms are you providing?
If your OData endpoint uses v4, the specification states that error codes in the 4xx range indicate a client error, such as a malformed request, and that error codes in the 5xx range indicate service errors. My guess is that if a query requests data that is inaccessible due to permissions, then that would return error codes in the 5xx range.
Also, can you show the M functions you use to query the OData endpoint?
Lastly, there is a known issue with Power BI's implementation of OData v4 endpoints, which may not have anything to do with your question:
No Query Support for OData URLs
------------------------------
Tony McGovern
co-Founder & Data Scientist
emdata Inc.
Original Message:
Sent: 01-10-2019 05:29 AM
From: John Dawson
Subject: Error handling with OData.Feed
Have been using OData.Feed, which works very well; we now use it in a Data Connector; but am having difficulty working out how to introduce error handling; the particular scenario I'm on is that a query might request data, and perhaps some of the data might be inaccessible, due to say permissions and I want the query to detect that, and return the data it can, and not fail completely. The documentation gives no clue and I could find no useful blogs. Any ideas anyone?
------------------------------
John Dawson
Software Engineer
876899077
------------------------------