Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

ibarrau

[PowerQuery] Catch errors in a request http

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:

ibarrau_0-1698085879685.png

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:

ibarrau_1-1698085893811.png

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:

ibarrau_2-1698086004438.png

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

 

 

Comments