Refresh the Sharepoint Excel file Dataset in Power BI Service

[SuryaVee]

Hi,

I use an Excel file in Sharepoint as datsource to my Dashboard.

In Power Bi Desktop , I use the 'Web' option of datasource to connect this file and provide the Sharepoint URL until the path of the excel file. I use Organizational Account as credentials to connect to the file.

It connects properly and the dashboard refreshes without any issues when I refresh it through the desktop.

The Dashboard is published to Power BI Workspace and when I check in the Settings for Scheduled refresh, I get error message as the Datsource Credentials is invalid.

When I try to Edit the Credentials, I have 3 options Anonymous, Windows and Basic.

As my organization uses SSO, I'm trying to use Windows as I dont have any other credentials to access that Sharepoint.

I get error as "Failed to Update Credentials"

Help me in sorting out this issue as I want the dashboard dataset to be set for scheduled refresh.

Thanks

SVee

[GilbertQ]

Hi there

What happens if you try the On-Premise Data Gateway in personal mode?

Do you get the option for OAuth?

[SuryaVee]

Hi Gilbert,

No, I dont have a Personal gateway setup.

Thanks

Svee

[GilbertQ]

Hi there

I just had a look at one of my connections and it appears that you should not need the gateway when connecting to an Excel file stored in SharePoint online.

If you can confirm that your SharePoint is either SharePoint Online or OneDrive for Business?

[SuryaVee]

Hi Gilbert,

It is Sharepoint Online, but when I access it Power BI Dekstop, I'm selecting the Datasource type as 'WEB'. 

Is that creating the issue?

Thanks

SVee

[GilbertQ]

Hi there

That should be fine, just make sure that it is using the SharePoint.com in the URL?

[SuryaVee]

Hi Gilbert,

Yes it uses https://____ .sharepoint.com.................. in the beginning of the URL.

Thanks

SVee

[GilbertQ]

hi @SuryaVee 

Make sure it starts in the Advanced Editor with the following below

Source = Excel.Workbook(Web.Contents("https://domain-my.sharepoint.com/personal/gilbert_fourmoo_com/Documents/Presentations/Dataflows/Dates%20tables.xlsx"

[SuryaVee]

Hi Gilbert,

Please find below the screenshot of the Advanced Editor,

[GilbertQ]

Hi there, that should then prompt you under the credentials for the different authentication