Forum Discussion
Sharepoint Authentication fails when using Web.Contents with RelativePath on files
Hi all,
I am trying to build a report that reads a bunch of Excel Files in a Sharepoint document library.
They are stored in the same Sharepoint site.
If I load them directly via Web.Contents([URL]), they load perfectly after I authenticate once.
Unfortunately, this does not allowe refresh online. Hence I modified it to use the same, static base_url and put the dynamic part into [RelativePath]. But now the process is stuck whenever it loaded a file - it always asks for authentication, but fails each time.
I have attached 2 screenshots that show the result. Any advice on how I can upload a demo pbix?
Hi AlexR_DE,
Thank you for the follow-up and for clearly outlining the issue. You are right to be concerned about performance when dealing with a large number of files in a SharePoint document library.
To avoid the performance overhead of scanning all files in the folder (as is the default
behaviour with SharePoint.Files), and since you already know the exact file path, you can directly access the file using either:
Option1: SharePoint.Contents() with direct file targeting.
This method allows you to retrieve a specific file from the library without pulling metadata for all other files:let SiteURL = "https://yourtenant.sharepoint.com/sites/yoursite/", FilePath = "Shared Documents/FolderName/YourFile.xlsx", File = SharePoint.Contents(SiteURL){[Name="YourFile.xlsx", FolderPath=SiteURL & "Shared Documents/FolderName/"]}[Content], ExcelData = Excel.Workbook(File) in ExcelData
Option2: Web.Contents() for direct file access.
Alternatively, use Web.Contents() if you have a static link to the file:let FileURL = "https://yourtenant.sharepoint.com/sites/yoursite/Shared%20Documents/FolderName/YourFile.xlsx", Source = Excel.Workbook(Web.Contents(FileURL)) in Source
Make sure that the authentication method in Data Source Settings is set to Organizational Account.If you are using Power BI Service, confirm that the SharePoint connector is configured using OAuth in the dataset settings, especially if the file is protected under Microsoft 365 authentication policies.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.
11 Replies
Hi AlexR_DE ,
Is there a specific reason you're not using the SharePoint Folder connector? It's designed to do exactly what it appears you're trying to achieve.
Power Query SharePoint folder connector - Power Query | Microsoft Learn
Pete
Yes, I am using a custom REST query to extract all files, because I expect loads of files (10.000+) in that folder and that will not work with the connector in a performing way...
Hi AlexR_DE,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to BA_Pete, for those inputs on this thread.Thanks for sharing the details and screenshots they really helped clarify the issue. You're right that using the full URL with Web.Contents() works, but unfortunately, switching to RelativePath causes authentication to fail, even when credentials are entered correctly.
This happens because Web.Contents() with RelativePath does not fully support OAuth2 authentication for SharePoint Online. Power BI is unable to apply the correct auth context when only a partial path is passed this is a known limitation.
To get around this and ensure your report can refresh in the Power BI Service, I’d recommend switching to SharePoint.Contents. This connector is designed to work smoothly with SharePoint authentication and online refresh scenarios.
Here is how the structure would look using SharePoint.Contents:
let siteUrl = "https://yourtenant.sharepoint.com/sites/yoursite", files = SharePoint.Contents(siteUrl), folder = Table.SelectRows(files, each [Folder Path] = siteUrl & "/Shared Documents/your-folder-name/"), excelFiles = Table.SelectRows(folder, each Text.EndsWith([Name], ".xlsx")), loadBinary = Table.AddColumn(excelFiles, "GetBinary", each [Content]) in loadBinary
You can then filter dynamically by filename or path without needing to hardcode URLs or use RelativePath.If you’d like more detail on the differences between Web.Contents and SharePoint.Contents, the following articles may help:
SharePoint.Contents - PowerQuery M | Microsoft Learn
Handling status codes with Web.Contents for Power Query connectors - Power Query | Microsoft Learn
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Hi AlexR_DE,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
I now tried to use SharePoint.Contents - approach.
However, it seems to only scan the level you explicitely indicate - so it does not display all the elements nested on the folders below.
let siteUrl = SiteCollection, files = SharePoint.Contents(siteUrl), Documents = files{[Name="Documents"]}[Content], General = Documents{[Name="General"]}[Content], #"1_Workstreams" = General{[Name="1_Workstreams"]}[Content] in #"1_Workstreams"any way to tell the connector to show all files under a specific level (in my example above, I'd like to show all entries from all folders underneath? So essentially recursively? Also I expect probably thousands of files (that was the reason for my REST approach).
Have you tried SharePoint.Files? Is there some hang-up with that?
Of course I tried.
The issue is performance. I expect thousands of files and that simply takes to long to load all of them first and then do filtering.
