Checking request status and using the result without doubling server calls

[LPGiroux]

Hi,

I'm developping a custom connector to connect to our rest API. I use ManualStatusHandling to properly manage a few types of HTTP error codes (400, 429, 500) using Web.Contents. I folow the Wait-Retry Pattern example from the official documentation and this blog post https://4pbi.com/handling-http-errors-with-m-language/ plus a few tweeks to return the status code and the result. Everything seem to work well until I check the connections to the server. 

This is the code:

 CallWebService = (baseUrl as text, options) =>
    let
        Result = Value.WaitFor(
            (iteration) =>
                let
                    content = Web.Contents(baseUrl,options),
                    buffered = Binary.Buffer(content),
                    status = Value.Metadata(content)[Response.Status], 
                    actualResult = 
                        if status = 429 then null
                        else {status, Text.FromBinary(buffered)}
                in
                    actualResult,
            (iteration) => #duration(0, 0, 0, 5), // wait for 5 seconds
            5// retry
         )
    in
        Result;

Power BI decides that the best way to use the result and check the metadata is to get the content twice.

So more specifically, Value.Metadata(content)[Response.Status] and Text.FromBinary(buffered) cause a double call.
But if 1 call works and 1 call fail, they are both wrong... 

Also, calling a web service twice is not always supposed to yield the same result, making twice the calls to check for error 429 (Rate limiting error) augment the risk of causing error 429...

This also double the work the server has to do and the load on the network.

So, a problem on many level...

Anyone know how to prevent this behavior?

How to use the result of Web.Contents with 2 built-in functions without causing a double call?

I also notice the problem when I was using Json.Document twice to get different parts of the result, both function call resulted in a separate call to the server.

Why is it so hard to have Power BI execute only what is written and all that is written...?

Thanks for the help!

[v-yingjl]

Hi @LPGiroux ,

If not want to use a double call, you can try to handle with these errors manually by creating an error table in Power Query.

Refer:

1. Handling HTTP Errors in Power Query and Power BI 
2. Handling 404–Not Found Errors With Web.Contents() In Power Query And Power BI 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LPGiroux]

Hi v-yingjl,

Thank you for taking the time to answer.

The technique from the first video is using ManualStatusHandling, this is already what I’m doing (set in the options variable before calling my function). Then, we are both calling Value.Metadata to get the status code. The handling of the errors with a static table or custom code doesn’t change the fact that if the call is valid, I still need to call Text.FromBinary if I want to du something with the result. Calling Text.FromBinary and Value.Metadata causes the double call.

Any other idea?

[LPGiroux]

To give more precision, if status code is not 200, the calling function will extract the error message and build a custom response base on both the message and static values. The retry loop show in the code is only to handle the rate limiting error 429.

Thanks again for trying!