Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Web API data source refresh issue

Hi Team,

 

I have created a web api which validates the http header and gives the response in Json format. I have added this api as a data source for a report in power bi desktop which is working fine. But when i publish the report to powerbi.com it fails the data refresh with an exception "Invalid Credentials" and the configured header value is not available in the data connection. need help in fixing this issue

 

Thanks 

Sowjanya

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @SowjanyaK,

 

After you publish the report to Power BI service, please go to the Dataset Settings page. If the Power Query to get data contains web.page function, you will need a gateway for the dataset. You can configure a on-premise data gateway and create a web data source with the same URL used in desktop. Then set the dataset use this data gateway. 

 

q3.PNG

 

Best Regards,
Qiuyun Yu 

SowjanyaK
Advocate I

I am using Json.Documnt(web.content()) and this api is hosted in cloud. Gateway is showing as "Connect Directly" but in the connection it is giving me the invalid credentials. To give more context, my api is under API management which is always expecting the subscription details for authorization. When i access the api from power bi desktop i was able to provide the http header value , but when i publish the report to powerbi.com then eader values are not available and in the data source connection authentication only three options are provided(i.e annonymus/basic/windows),in Power bi desktop connection to api is created as below, it is working fine, data is loaded.

let
Source = Json.Document(Web.Contents(URL,[Query=[accountId= AccountIdvalue],
Headers=[#"MS-CorrelationId"= MSCorrelationIdValue,
#"MS-RequestId"=MSRequestIdvalue,
#"MS-Contract-Version"=MSContractVersionValue,
#"Ocp-Apim-Subscription-Key"=OcpApimSubscriptionKeyValue,
#"SecretKey"=SecretKeyValue ]])),

 

  when i publish the same report to powerbi.com data source connection is failing with invalid credentials

PI.JPG

 

 

 

SowjanyaK
Advocate I

Hi @v-qiuyu-msft

 

I am using Json.Documnt(web.content()) and this api is hosted in cloud. Gateway is showing as "Connect Directly" but in the connection it is giving me the invalid credentials. To give more context, my api is under API management which is always expecting the subscription details for authorization. When i access the api from power bi desktop i was able to provide the http header value , but when i publish the report to powerbi.com then eader values are not available and in the data source connection authentication only three options are provided(i.e annonymus/basic/windows),in Power bi desktop connection to api is created as below, it is working fine, data is loaded.

let
Source = Json.Document(Web.Contents(URL,[Query=[accountId= AccountIdvalue],
Headers=[#"MS-CorrelationId"= MSCorrelationIdValue,
#"MS-RequestId"=MSRequestIdvalue,
#"MS-Contract-Version"=MSContractVersionValue,
#"Ocp-Apim-Subscription-Key"=OcpApimSubscriptionKeyValue,
#"SecretKey"=SecretKeyValue ]])),

 

  when i publish the same report to powerbi.com data source connection is failing with invalid credentials

PI.JPG

 

 

 

SowjanyaK
Advocate I

Hi @v-qiuyu-msft,

 

I am using Json.Documnt(web.content()) and this api is hosted in cloud. Gateway is showing as "Connect Directly" but in the connection it is giving me the invalid credentials. To give more context, my api is under API management which is always expecting the subscription details for authorization. When i access the api from power bi desktop i was able to provide the http header value , but when i publish the report to powerbi.com then eader values are not available and in the data source connection authentication only three options are provided(i.e annonymus/basic/windows),in Power bi desktop connection to api is created as below, it is working fine, data is loaded.

let
Source = Json.Document(Web.Contents(URL,[Query=[accountId= AccountIdvalue],
Headers=[#"MS-CorrelationId"= MSCorrelationIdValue,
#"MS-RequestId"=MSRequestIdvalue,
#"MS-Contract-Version"=MSContractVersionValue,
#"Ocp-Apim-Subscription-Key"=OcpApimSubscriptionKeyValue,
#"SecretKey"=SecretKeyValue ]])),

 

  when i publish the same report to powerbi.com data source connection is failing with invalid credentials

PI.JPG

 

 

 

v-qiuyu-msft
Community Support

Hi @SowjanyaK,

 

I can reproduce the issue on my side. And I already reported this issue internally, will update here once I get any information. 

 

Best Regards,
Qiuyun Yu 

danilm
Regular Visitor

Do you have any update? I have the same issue - Web API authentication works in the desktop, but fails auto-refresh when published.

 

I am using Json.Document(Web.Contents("...",  [ApiKeyName="Api-Token")) to retrieve data from the REST API call, Cloud Power BI connection authentication does not even provide the "Web API" option.

Writing authentication key value directly into Web.Content (authentication header) has the same problem. Works in Power BI desktop, but fails refreshes when published into the cloud.

 

Please let me know if this is a known issue, and if so, any timeline for the fix?

 

Thank you!

SowjanyaK
Advocate I

 This is the response that i got from Power BI team, 

I don’t think optional records are supported in PowerBI service yet. Is there a way to put all the info inside the url instead of composing it using []?

Need to raise a support ticket with PowerBI team to get the dedicated support,

danilm
Regular Visitor

Hi @SowjanyaK,

 

I moved the authentication token inside the query string, as you suggested.

Changed

Json.Document(Web.Contents("...",  [ApiKeyName="Api-Token"))

to

Json.Document(Web.Contents("...&Api-Token=xxxxxxxxxx"))

 

Same problem. Works in PowerBI Desktop, but fails in cloud (Authentication Method: Anonymous):

Failed to update data source credentials: The credentials provided for the Web source are invalid.

 

Can you confirm this is a known issue, or am I doing something wrong?

 

Thank you,

Danil

 

SowjanyaK
Advocate I

I think then this is an issue with power bi, you can raise a support request with power bi team. 

cecyarmida
Frequent Visitor

Hi @SowjanyaK,

I ran into the same issue and was able to bypass the problem using Azure Fuctions Proxy, here is a blog I wrote about that issue:

 

https://blog.kloud.com.au/2018/07/23/use-anonymous-authentication-on-web-data-sources-with-a-api-key...

 

I hope it helps