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

Excel on Sharepoint Data Source Creds Error

Hi there

 

I have an Excel Workbook sitting on Sharepoint which I connect using:

 

=Excel.Workbook(Web.Contents("https Link to *.xlsx File on Sharepoint"), null, true)

 

In Desktop, I can successfully connect & refreseh using the "Organisational Account" option & publish to the service.

 

On the service, I cannot refreseh due to invalid creds:

 

Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

 

When trying to update the creds, I can only select from "Anonymous" or "Basic" (but not "Organisational Account"), both of which do not work neither in Desktop, nor on the Service.

 

Additionally it's probably worth mentioning that the organisational account I am using has two factor authentication turned on.

 

Many thanks in advance!

 

-m

1 ACCEPTED SOLUTION
Minsen
Frequent Visitor

Hi @GilbertQ,

 

I have found a solution by tweaking the link to the Excel file on Sharepoint as follows:

 

From https://[companyname].sharepoint.com/:x:/r/sites/[RestOfLink]

To https://[companyname].sharepoint.com/sites/[RestOfLink]

 

Essentially just leaving out /:x/r. With this type of link + =Excel.Workboook(WebContents()) I have the option to use "OAuth2" without gateway and (!) it works with my account which has both MFA & PW changes over time (for the latter I will need to re-sign in after every change, of course).

 

I scheduled an automatic refresh for last night and it worked like a charm.

 

Thank you so much once more for your help.

 

-m

View solution in original post

5 REPLIES 5
GilbertQ
Super User II
Super User II

Hi @Minsen 


Can you make sure that you have got the latest version of PBI Desktop and the PBI Gateway installed.

 

Further more I do think you might have it tough with MFA on your account to be used for authentication. I would suggest getting a user account that does not have MFA or the password being changed over time.


This same account would then be added to the SharePoint group.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Minsen
Frequent Visitor

Hi @GilbertQ 

Thank you for your help. I have updated PBI Desktop and the gateway software. However, I was not aware I needed an on-premise gateway to connect in this case? On PBI Service under Dataset Settings -> Gateway Connection it reads:

You don't need a gateway for this dataset, because all of its data sources are in the cloud, but you can use a gateway for enhanced control over how you connect.

 

I can use the Gateway though and add the Web data source, but then again I cannot seem to connect to it for invalid creds. I can select from "Anonymous", "Basic", "Windows" & "OAuth2".

 

Will try to get an account w/o MFA and password changes over time, but don't think IT guidlines will allow for that. Is this a must to solve the issue?

 

-m

Hi @Minsen 

 

I would suggest trying that first with the account without MFA and see if that works?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Minsen
Frequent Visitor

Hi @GilbertQ,

 

I have found a solution by tweaking the link to the Excel file on Sharepoint as follows:

 

From https://[companyname].sharepoint.com/:x:/r/sites/[RestOfLink]

To https://[companyname].sharepoint.com/sites/[RestOfLink]

 

Essentially just leaving out /:x/r. With this type of link + =Excel.Workboook(WebContents()) I have the option to use "OAuth2" without gateway and (!) it works with my account which has both MFA & PW changes over time (for the latter I will need to re-sign in after every change, of course).

 

I scheduled an automatic refresh for last night and it worked like a charm.

 

Thank you so much once more for your help.

 

-m

View solution in original post

Thanks for letting us know and I have to say I thought that you had got the link via the file in Excel and not via the browser


Glad to hear you got it working!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.