Web.Headers using MS Graph to handle (expected) HTTP-errors seems not possible. Any suggestions?

[ArjanvanLoon]

Have anybody been able to handle (expected) HTTP-errors using the Microsoft Graph API?

I was hoping to use Web.Headers in combination with 'try' 'otherwise', but it seems the web.headers() expression is not accepted yet by Graph. I get the following error:

DataSource.Error: Web.Contents failed to get contents from 'https://graph.microsoft.com/v1.0/users' (405): Method Not Allowed
Details:
DataSourceKind=Web
DataSourcePath=https://graph.microsoft.com/v1.0/users
Url=https://graph.microsoft.com/v1.0/users

My expression contains the following:

Web.Headers("https://graph.microsoft.com", [RelativePath="/v1.0/users", Headers = [Method="GET",Authorization=[Bearertoken]]])

If I am right, hopefully there is another way to handle HTTP-errors before stopping my query in Power BI.

[ArjanvanLoon]

I understand your opiniun. The problem is I have a multiple queries retrieving data for multiple customers. It depends on the customer which query will give a result or the error record. So in preventing administrating which customers to use which queries I would like to simply ignore the error. But I agree, maybe Power Query does not have a solution for that.

If anybody do have a workaround, i would really appriciate your help!

@ams1 , thanks for all your reply's on this matter!

[ArjanvanLoon]

The situation for now. I have 2 rows from web.contents. One results with a binary containing a usefull JSON, the second row results in 'Expression.Error: Access to the resource is forbidden'. I would like to filter that one out, so the rest of the query steps will have not a problem from that record.

I tried your suggestion but using web.headers always returns a 405 error, so also for the 'healthy' records from which a web.contents would generate a usefull JSON. So in my opinion Graph does not seem to support the web.headers function at all. From there filtering on web.headers is not possible.

But using web.contents generate an expression.error for some records, from which I cannot proceed with the other query step. I asked chat.openai who advised me to use the following code, which looks very logical. But this check returns an unexpected TRUE also for the record containing the expression.error. So it is a valid JSON, but the error is stopping any further steps.

    #"Added Custom" = Table.AddColumn(Source, "ValidBinary", each try Value.Type([JSON2]) = type binary otherwise false)

[ams1]

Hi @ArjanvanLoon 

I don't think you can filter the "forbidden" error record out, because I THINK whenever PQ tries to evaluate the record for filtering purposes, it will hang in the credentials...

IMO you have to make sure there is no forbidden record there.

Others are of course free/invited to jump in anytime and help fix the issue

[ArjanvanLoon]

Unfortunately that does not solve the problem, as ManualStatusHandling is not working from Power Query. So the query halts completely, before I am able to handle the error.

Still hoping there is anybody who nows a workaround for handling HTTP errors from Power Query.

[ams1]

Me again with a clarification - @others please correct me/step-in anytime.

NOT all HTTP errors "halt" the query:

• 401 (and 403) will halt the query
• 405 (etc.) will NOT halt the query

Example of 405 NOT halting the query (I tested below on a local webserver that returned 405 on purpose):

let
    WebContentsWrapper = (url as text, responseFn as function) => 
    let
        response = Web.Contents(url),
        ret = try responseFn(response)
    in
        ret,
    wrappedResponse = WebContentsWrapper("http://127.0.0.1:5000/test", Json.Document),
    usageExample = if wrappedResponse[HasError] = true then "BOOM" else wrappedResponse[Value]
in
    usageExample

The above query did NOT halt, but returned as expected = BOOM:

And you CAN extract the error from the wrappedResponse record, if you want:

This could be a partial solution for handling NON 401&403 errors.

P.S.: If I would need to save my professional life by handling 401&403 errors in PowerBI 1) I'd implement a custom connector OR 2) I would use a simple "proxy server" hosted somewhere (internally or maybe pipedream.com etc.) that would always return 200 (so that the query doesn't halt) and a json record with actual HTTP error.

Specifically for MS Graph, I see there is also an integration https://pipedream.com/apps/microsoft-graph-api (haven't used it).

Please mark this as answer if it helped.

[ArjanvanLoon]

I have a loop to import Graph datasets from different 365 tenants, like a list of all users for all tenants. Because not all tenants support the same data, some response with HTTP-error. I would like to ignore those errors and continue with the loop for retrieving data of the other tenants. That keeps me from administrating which GET-connectors I shouldn't address for certain tenants (I hope you understand my poor english 🙂

So again, the question is how to handle (and skip) HTTP-errors from Power Query using Microsoft Graph.

[ArjanvanLoon]

Thanks @ams1 for your reply! 

The first link confirms using web.contents is not capable of manual handling error statusses using ManualStatusHandling. Therefore I hope using an alternative way will solve my problem, like using Web.Headers. But again, it seems Graph does not support the use of the Web.Headers-function... but I don't understand why, or am I doing something wrong?

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 a custom data connector (that is, directly from Power Query).

Also the second link does not give an answer to the issue. It refers to the following site, where in the comments Ricardo writes the exact same situatie 😞

[ams1]

Yeah, with the links I wanted to confirm if that is the feature you want - if that's the case I also think it's not possible in normal PowerQuery.

Often when someone asks "do you support that feature", you tend to say "no" without asking "why do you need that feature/what is the problem you want to solve using that feature?".

So if you can share a bit more details regarding the problem you want to solve with "web.headers"/error handling, maybe the community will say "Oooo, if you actually want that, then you can do it with another feature from PowerQuery..." 😊

BTW there was a recent question ref MS Graph, don't know if you saw it/if it might help you somehow: 

https://community.powerbi.com/t5/Power-Query/MS-Graph-ODatalink-paging/m-p/3079980#M98042

And finally: MS Graph and PowerQuery is doable, but...  -> https://learn.microsoft.com/en-us/power-query/connecting-to-graph

[ams1]

Hi,

You mean something like:

https://learn.microsoft.com/en-us/power-query/handling-status-codes

https://www.tonymcgovern.com/blog/web-api-error-handling/

?

P.S.: if the only goal is to handle oauth, the current recommended way I think is a custom connector