Power BI Service refresh issue due to (misconfigure credential) credential authentication failure

[wheelerma55]

Hello Power BI Community,

I am having a Power BI Service dataset refresh failure.

I receive the following summary error: Data source error<pii>The credentials provided for the Web source are invalid. ...

I am loading in an Excel file that sits on a SharePoint site via Source = Excel.Workbook(Web.Contents("https:// ...

Under a Power BI Desktop configuration, using an Organizational Account (which is displayed), the refresh is successful.

When the PBIX file is pushed to Power BI Service and a refresh is triggered, the refresh fails.

When investigating credential configuration for the web connector, the service only displays/allows the Anonymous or Basic Authentication method.

My assumption is that because the Power BI Service is not using the PBIX file's Organizational Account config, the refresh fails.

Suggestions?

Best regards,

Mark

[GilbertQ]

Hi there

I think you might need to change the data source to be a SharePoint File which will then allow for the the organizational option for the data source.

[wheelerma55]

Hi Gilbert,

So your suggestion sent me to look for SharePoint options. My final solution wasn't exactly as I think you were indicating but it did lead me to a solution.

Thank You, Mark

----------

Background: Power BI report using Excel loaded data had been working for many months with a daily refresh successfully running.

Earlier in the week the souce location of an excel file changed which caused daily refresh to fail. When the location of the file was updated within the PBIX file and a Desktop refresh was run the dataset was properly updated. Once updated PBIX file was pushed to the Power BI Service dataset refresh failed.

The issue seemed to be the Web connector (SharePoint) functionality. Authentication on Power BI Service seems to work differently than on the Power BI Desktop version (or expect something different when authenticating). A subtle difference in the location string of the web connector changes the authentication options presented when views from the Power BI Service (Organizational option was not available as seen in Desktop version). As such Power BI Service could not apply the appropriate authentication credentials whereas Power BI Desktop could.

Solution: An Excel generated path structure entered into the Power Query (M) - Source entry enabled Power BI Service to authenticate correctly.

Here is the original string used (this entry was modified from original entry by hand). The Excel load worked on Desktop refresh but failed to authenticate on Power BI Service.

https://xxx.xxx.com/:x:/r/sites/collaboration2/xxx-xx-xxx/Documents/R%26D%20xxx%20Leaders%20Files/xx...

Here is the working string used in the ultimate solution (Excel string captured via process outlined in video link below).

https://xxx.xxx.com/sites/collaboration2/xxx-xx-xxx/Documents/R&D%20xxx%20Leaders%20Files/xxx%20Proc...

Video method: How to connect to specific file from SharePoint in Power BI 

https://www.youtube.com/watch?v=EDy4bCIov_s

[v-shex-msft]

HI @wheelerma55,

I'm glad to hear you find a workaround and share them here, it should help others who face a similar issue.

BTW, I think you can also consider reporting this scenario to power bi team to confirm if this is by design or known limitations.
Regards,

Xiaoxin Sheng

[GilbertQ]

Awesome thanks for letting everyone know how you solved your issue!