Excel on Sharepoint Data Source Creds Error

[Minsen]

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

[GilbertQ]

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.

[Minsen]

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

[GilbertQ]

Hi @Minsen 

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

[Minsen · Accepted solution]

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

[GilbertQ]

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!