Power BI Data Source with Refresh ability

2
votes

I am trying to setup a data set in Power BI which can be refreshed on a need basis or scheduled to refresh.

I am uploading an excel workbook which has a power query.

the power query is connecting to Replicon Service to get data via service. the query looks as below:

Source = Web.Contents(
    "https://na2.replicon.com/services/ClientService1.svc/GetActiveClients",
    [
        Headers=
        [
            #"Authorization"="Bearer *ValidToken*",
            #"Accept"="application/json",
            #"Content-Type"="application/json"
        ],  
        Content=Text.ToBinary("{}")
    ]
)

the request is a POST operation and hence Content field is used in Web.Contents Options argument. Authentication is via Bearer token.

Data source setting is Anonymous Credentials with privacy Level set to None.

This works fine and I am able to retrieve the results and even refresh form within workbook.

Once I upload this to PowerBI and attempt to refresh the newly created Dataset it says:

You cannot refresh yet because you need to provide valid credentials for your data sources in the dataset.

So I go to Manage Data sources. Click Edit Credentials. Select "Anonymous" Authentication method and click on Sign In and it says "Login Failed".

Why is that so? It appears that the Headers are lost when I upload the Excel workbook. How can I accomplish this? Is there any alternate ways of being able to set up a Data Set which can be refreshed - the source being a web service?

2
excelbearer-tokenpowerbipowerquery
see some more details for the same question on Microsoft Technet - social.technet.microsoft.com/Forums/en-US/…. i ended up building an intermediate layer which accepts requests from PowerBI and translates them to required service. – shamanth Gowdra Shankaramurthy

2 Answers

1
votes

this is what i ended up doing finally.

my scenario first:

  • my requests are POST
  • Authentication is via bearer token which needed to be passed via Request Header. this was the requirement of the replicon service i am trying to invoke which i couldn't change
  • the dataset that is created in PowerBI needed to be refreshable.

Since i couldn't get it to work directly from inside PowerBI i introduced an intermediate layer. this would interpret GET requests from PowerBI. process the token from query string. accept the service and operation also as query string parameters. it would then create POST request to the Real Service (replicon services in my case). the service name and operation names were also picked up from request URL. the token was pushed as part of request header

so the request from PowerBI would look as PowerBI needed i.e.

Web.Contents("https://intermediatelayer.com?access_token="*validtoken*"&ServiceName="ClientService"&Operation="GetActiveClients"")

not an ideal solution but works.

0
votes

Manage Data Sources is validating the Anonymous credentials with a GET request to the URL without the hardcoded headers, like you suggest. It's basically running

Web.Contents("https://na2.replicon.com/services/ClientService1.svc/GetActiveClients")

which fails with "(405): Method Not Allowed", and so Manage Data Sources thinks the credentials are wrong.

Short of making the service reply with a success response for the above M, I don't see any way to set up refresh on this mashup.