Authentication Problems with SharePoint Folders

[sommerp]

Hi all,

I'm currently facing issues while trying to connect a SharePoint folder to my PowerBi Report.

Set-up is as follows:

PowerBi Report is stored in a Teams Channel(private) (but opened in the PowerBI Desktop app)

The folder I am trying to connect to is stored in the same Team, but in another Channel (also private).

I tried connecting the path (looks like this: https://COMPANYNAME.sharepoint.com/teams/Team-YYYYYY/Shared%20Documents/ZZZZZZ/FOLDER) via "Web" option in PowerBI but always get stuck while trying to authenticate. 

Neither Windows Login nor organizational account work. Error Message simply states: "Could not authenticate with given credentials. Please try again"

When I try to connect to a single file (*.csv) in this exact folder I can log on with the organizational account and the connection works as expected. Also, connections to various other *.xlsx files in the same Team but in other (private) Channels work completely fine. Just the connection to a folder is making problems.

I tried connecting the same folder via my personal OneDrive link path (C:\Users\xxxx\OneDrive - Companyname\....) and this would work. Major downside of this solution is that other users would not be able to refresh the connection at all.

I also tried setting up the folder in a public channel in the same Team, but got the same results.

Other steps I already did: clear all permissions and connect againg - same result.

Created a completely new *.pbix file and just tried connectint to the one sharepoint folder - same result.

Are there any solutions to this problem? Or maybe other possible options/workarounds to connect a folder stored in a Teams Team/SharePoint to a PowerBI Report?

Thanks for any help!

[Prodingero]

further drill down to only a single folder?Answer filter the column Folder with "ends with"

[Anonymous]

Hi @sommerp ,

One possible reason why you are getting the authentication error is that you are using the Web connector instead of the SharePoint Folder connector. The Web connector is designed to connect to a single file or web page, not a folder. The SharePoint Folder connector is the one that can combine multiple files from a SharePoint folder into one query. To use the SharePoint Folder connector, you need to enter the root URL of the SharePoint site, not the folder path. For example, if your folder path is https://COMPANYNAME.sharepoint.com/teams/Team-YYYYYY/Shared%20Documents/ZZZZZZ/FOLDER, then you need to enter https://COMPANYNAME.sharepoint.com/teams/Team-YYYYYY as the site URL.

Another possible reason why you are getting the authentication error is that you are using the wrong credentials or privacy level for the SharePoint site. You need to use the same credentials and privacy level that you use to access the SharePoint site from your browser. You can check and edit your data source settings in Power BI Desktop by going to File > Options and settings > Data source settings.

Power Query SharePoint folder connector - Power Query | Microsoft Learn

Quick steps for connecting to SharePoint using Pow... - Microsoft Fabric Community

Create a report on a SharePoint List in Power BI Desktop - Power BI | Microsoft Learn

Why can't I connect SharePoint and Power BI? (windowsreport.com)

Best Regards
Community Support Team _ Rongtie

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

[sommerp]

Hi!
Thanks for the tips!

When connecting with the SharePoint connector and Site URL it works, but is there a possibility to further drill down to only a single folder?

In my File structure there is the possibility for more than one folder from a Teams/Sharepoint page that I want to connect as two seperate data connections. Also, there are *.xlsx files in the same filepath as the folder(s). 

When I connect via the sharepoint connector, I only get all possible files that are stored in the Sharepoint structure as one connection and the connection assumes (I suppose) the first found datasource as the data structure.
There is the possibility to go to the combine and transform data step and then filter for folder path in the first transformation step. But this doesn't work if there is an unknown file typ to PowerBI in one of the folders as it then doesn't let me proceed to the transformation steps.

Also I would then have to change following steps depending on which data structure PowerBI assumed in the first place.

Is there a more elegant solution that could be more "end-user" friendly?

Again, thanks for the help, this brought me at least one step closer to a solution!