cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
damz Frequent Visitor
Frequent Visitor

Refreshing a Dynamic Web Query in PBIRS

Hi guys,

 

Scenario:

I'm trying to build a report that queries the same REST API that lives on a number of servers. This list of servers is stored in a SQL database.

 

Problem:

I've built the Power Query, and it works fine. The issue is when publishing the report on Report Server, I get a "Credentials are required [...]" error message.

 

So my question is, where can I enter the credentials (in this case anonymous is good enough)?

  • When refreshing from PowerBI Desktop RS, the credentials are asked for when refreshing and it then works
  • When refreshing from PowerBIRS, the following error message is logged: 
    0] -1055784934: Credentials are required to connect to the Web source. (Source at https://server1:8080/Service/api/). The exception was raised by the IDataReader interface.

But there's nowhere to enter the credentials for each of the service dynamically hit by the report - the only DataSource known to ReportServer is the SQL Server.

 

Is it possible to hardcode it in PowerQuery?

 

Details

This is a simplified version of my Power Queries:

 

Get-Health function:

 

(Server) =>
let
    Source = Json.Document(Web.Contents("https://" & Server & ":8080/Service/api/"))
in
    Source

 

Health query:

 

let
    Source = Sql.Database("DBServer", "DB", [Query="SELECT ServerName FROM Servers"]),
    #"Invoked Custom Function" = Table.AddColumn(Source, "Health", each #"Get-Health"([ServerName])),
    #"Expanded Health" = Table.ExpandRecordColumn(#"Invoked Custom Function", "Health", {"timeToLive", "data"}, {"Health.timeToLive", "Health.data"}),
    #"Expanded Health.data" = Table.ExpandListColumn(#"Expanded Health", "Health.data"),
    #"Expanded Health.data1" = Table.ExpandRecordColumn(#"Expanded Health.data", "Health.data", {"isHealthy", "lastSuccessfulHealthCheck", "name", "state", "version"}, {"Health.data.isHealthy", "Health.data.lastSuccessfulHealthCheck", "Health.data.name", "Health.data.state", "Health.data.version"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Health.data1",{"Health.timeToLive"})
in
    #"Removed Columns"

 

 

4 REPLIES 4
Moderator v-qiuyu-msft
Moderator

Re: Refreshing a Dynamic Web Query in PBIRS

Hi @damz,

 

1. Regarding web.contents() function, you can follow this blog to set credential: https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/

 

2. Also when we get data use web data source in Power BI desktop, we can specify Authorization value: 

 

q6.PNG

 

3. After publish the report to Power BI report server, please go to Data Source tab of the report to set corresponding credential. 

 

q7.PNG

 

Best Regards,
Qiuyun Yu 

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
damz Frequent Visitor
Frequent Visitor

Re: Refreshing a Dynamic Web Query in PBIRS

Hi Qiuyun,

 

Unfortunately, I've already tried these. My issue actually not that I want to set credentials, but that I want to make PBIRS understand that it can use anonymous logon.

 

To answer your points

  1. I've tried that, but because there's no login and password necessary for these endpoints, it doesn't work. I've tried forcing a header with no authorization attribute, with no luck
  2. As I mentionned in my post, the calls to the webservices are dynamically generated. The list of endpoints is retrieved from SQL and PowerQuery queries one after the other using a funciton. Therefore I don't have the option to use the UI to configure the headers. That being said, by manually configuring them, it doesn't work because what I need is to convince PBI RS to use anonymous.
  3. Yes, that would work if the endpoints I call were a finite list, but because they are dynamically generated, they don't appear here.

 

Note that functions are not supported by the PBI Service - probably because it makes credentials complicated in the case a login/password is needed by each.

So I suppose it might be the same with PBI RS, except that there's nothing that explicitely says so. 

 

But if it defaulted to Anonymous, for endpoints that don't require authentication like mine, at least it would work for this case.

Grumelo Regular Visitor
Regular Visitor

Re: Refreshing a Dynamic Web Query in PBIRS

You can pass Header Parameters in a Web.Contents() call

 

Example:

 

= Json.Document(Web.Contents("<URL>", [Headers=[Accept="application/json"]]))

 

I have the same issue as you with my dynamic queries.

I've not tried yet to pass the Authorization in the Header. I'll let you know if it works.

 

In Power BI Desktop you can define the permission as Global Permissions in the Data Source Setting but not on PBIRS...

mpontrelli Frequent Visitor
Frequent Visitor

Re: Refreshing a Dynamic Web Query in PBIRS

Hello,

 

Did you ever get your web queries working with PBIRS.  I have them working fine in my reports, they get the token, connect, and retrieve the data no problem, however when I load them up to PBIRS, it says I do not have access to the data, and I do not see any option to add a web data source.

 

Any help would be appreciated.

 

Thanks

 

Mike 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 54 members 979 guests
Please welcome our newest community members: