Power BI Service - Refresh of data on API using POST method

[dawidvh]

 Hi everyone.

We have data that is accessed through a web API using a POST method with a api-key in the header in order to authenticate. This works well from the Power BI desktop, but as soon as the dataset is published to the online Power BI service, the anonymous authentication to the API fails.

Two issues I suspect:

a) The online service might not be passing headers to the API in order to satisfy the api key authentication.

b) The api method that is used by the Power BI Service uses Get by Default, and does not use the Content parameter in the web.contents function to see that it should use a POST request.

Does anyone have any workarounds for the above?

Below is an example of the query:

Below is the error we are getting on the Power BI service:

[LukeM]

Hi Dawidvh,

I am getting exactly the same problem with the Power Bi Service saying that the credentials are not valid yet everything works fine with Power BI Desktop,  I was wondering if you could explain to me how you solved this problem?

Thanks and regards,

Luke

[dawidvh]

Hi Luke.

To get around it you have to use create an intermediatery API that allows anonymous access at root level, that would then in turn connect to your actual data source that doesn't allow anonymous access at root level. Node-Red was perfect for this. Here is a video that you could use to setup Node-Red. https://www.youtube.com/watch?v=zz-jyOih6GA

I will try and make a video about connecting Power BI to this in the next week or two. If this is very urgent, let me know so that we can see how we can help or setup a call.

Twitter: @dawidvh

YouTube: https://www.youtube.com/davestechtips

[v-frfei-msft · Accepted solution]

Hi @dawidvh,

Based on my research, specify root URL in the url part, and place the remaining URL part into RelativePath parameter, So you can update your code as below. As long as you can use anonymous authentication to access root url of your api, you are able to use anonymous authentication to refresh dataset in Power BI Service.

let
authKey = “****”,
url =”root url of your api”,
body = “{}”,
Source = Json.Document (Web.Contents(url,[
      Headers = [#”api-key” = authKey,
                           *********
                         #”Content-Type” = “ “application/json”],
      Content = Text . ToBinary(body),
      RelativePath="/api/….."
                    ]
       ) ),
Data = Source[data],

For more details, please check this blog.

Regards,

Frank

[v-frfei-msft]

Hi @dawidvh,

Does that make sense? If so, kindly mark my answer as a solution to close the case please.

Regards,

Frank

[dawidvh]

Hey @v-frfei-msft,

I came right, thank you. The problem turned out to be the anonymous access to the root of the API that was not allowed. I have used Node-Red as an API proxy which worked well, but I think this change might be required on the Power BI Service to NOT check the anonymous access against against the root of the API at the time of authenticating from the online service.

Have a great day!