Some time ago, I have published an article on my blog about best practices when using the Web.Contents function, based on a famous post by Chris Webb. In it, we talked about RelativePath and Query. The truth is that there are many more parameters that we can use within this function. One of them will allow us to let specified errors pass so that we can handle them later. I'm referring to:
ManualStatusHandlingUrl: if this value is specified as a list, it will prevent any built-in control of HTTP requests whose response
This parameter receives integers or a list of integers. If these values correspond to HTTP errors, such as 500 (Internal Server Error), the Web.Contents will not fail, and the result will be the API response (which is normally JSON) with the error message and other values configured by the developer. Let's see how it would look.
Source = Web.Contents("api.github.com",
[
RelativePath= "/repos/LaDataWeb/data-quality-report/commits",
ManualStatusHandling={404, 400}
])
The errors specified in the list will not return DataSource.Error but rather the error response (avoiding the interruption by exception).
Once we allow the desired error to pass, in this example 404 and 400, we must capture it because the format of the response may not be something that the subsequent steps of our script can interpret. We wouldn't have the data but rather other levels in the response.
To capture our error, we will make use of Value.Metadata(). This function returns a record that contains the metadata of the input. In the input, we can put a previous step or a variable we have. In our case, we will use "Source."
GetMetadata = Value.Metadata(Source)
The return value from the function would look like this:
As we can see, the metadata contains a lot of information about a request. In our case, we are interested in the value of Response.Status, which is normally 200 or 202 when it's correct, but if there's an error, it usually interrupts the entire execution. That's why we did the previous step, because ManualStatusHandling allows the execution to continue and obtain the metadata even when it fails. We will be able to capture it because we wrote it earlier. If we try to modify the RelativePath by entering anything, it wouldn't find the address, and we would reach this result:
From here, we can choose how to structure our solution. For example: "At the end of the execution, we could choose to return the same table structure if everything were correct but without values."
Something like this:
This way, the code wouldn't fail, and in our front-end, we could add a user alert using the message overlay technique we saw in a previous post. Users and developers would be aware of the situation. It would be sufficient to have a measure that checks if the sum of ALL rows is blank. This could be more detailed if we added a column with the error state to express various messages. The example shows 404, but we could create a comprehensive list with different conditions based on the error.
We could also generate a table of messages and error control. In addition to capturing the error and allowing the script to continue, it would be ideal to keep track of the failures that occurred in those requests. It's possible that our source is not composed of a single request to an API but many more, especially if it's iterative. So, we could generate a table of failures to have better control and not halt the process if we encounter an error in the middle of a row.
I hope this helps you gain control over actions and errors when working with web sources. You can copy the Power Query code from my GitHub.
Original Post in Spanish from LaDataWeb
- joseftantawi on: How to customize open-sourced custom visual.
- kalpeshdangar on: Creating Custom Calendars for Accurate Working Day...
- gwayne on: Embracing TMDL Functionalities in Power BI and Pre...
- jian123 on: Sharing Power Query tables
-
Martin_D
on:
From the Desk of An Experienced Power BI Analyst
-
ibarrau
on:
[PowerQuery] Catch errors in a request http
- Aditya07 on: How to import customised themes in Power BI - usin...
-
Martin_D
on:
Currency Conversion in Power BI: Enabling Seamless...
-
technolog
on:
Unveiling Top Products with categories: A Guide to...
-
Ritaf1983
on:
When Big Numbers Become Big Problems