Invalid credentials when adding web datasource (for connecting to sharepoint)

[Anonymous]

Hi, I´ve read a lot of answers about this but can´t figure it out yet.

Desktop:

I´ve connected to an excel file located in sharepoint using the web connector.

Desktop

I can´t add it to the gateway. It gives an "Invalid credentials" error.

Invalid credentials

Any ideas? Is it just the credentials? I´ve tried DOMAIN\user, user@domain, etc.

I can refresh the report in desktop. 

I can refresh the report with a personal gateway.

I can connect to sharepoint in the browser.

I can´t add the datasource to the gateway 😞

[v-lionel-msft]

Hi @Anonymous ,

I suggest you use the SharePoint connector:

Create a report on a SharePoint List 

Also, have you tried this authentication method?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

web sites have to be added as a data source via the gear icon in Power BI, Manage Gateways, Add Data Source. 

Why not go to the Sharepoint file directly using the SharePoint Folders connection? this does not need to be added to the gateway unless it is an on-prem SharePoint server.

[Anonymous]

Thank you for the quick answer.

"web sites have to be added as a data source via the gear icon in Power BI, Manage Gateways, Add Data Source. " --> That´s what I´m doing.

"Why not go to the Sharepoint file directly using the SharePoint Folders connection?" --> I´ve always understood that this was the easiest way to read an excel file online. But I´ll try the SharePoint Folders connector, if it works thats fine with me 🙂

[edhans]

When you work with the Excel file as part of SharePoint folders, it works like a native Excel file from OneDrive or your local PC. Much more powerful.

Pro-Tip: if your excel file(s) are in one folder, when you create the initial connection in Power BI's Power Query, it will be
= SharePoint.Files("https://.....)

Change that to the following in the formula bar in Power Query:

= SharePoint.Contents("https://....)

it will let you navigate folders much faster than the .Files method will.

As to why you cannot add it via a web source, I think there is a permission issue going on with how you are logging in, and working with your IT person may be the best course to solve that.

[Anonymous · Accepted solution]

Thanks for the great tips, much appreciated. I´ve actually realised you don´t need to add web sites to the on-premises gateway... #facepalm It sais it is "optional". It is logical of course, the web is outside the on-premises server. But, what about the credentials? I guess it uses the credentials given to pbi desktop because it is refreshing ok.