topic Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files in Power Query

Sharepoint Authentication fails when using Web.Contents with RelativePath on files

AlexR_DE — Fri, 18 Jul 2025 06:49:33 GMT

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?

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

BA_Pete — Fri, 18 Jul 2025 07:18:38 GMT

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

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

AlexR_DE — Fri, 18 Jul 2025 08:39:00 GMT

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...

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

v-kpoloju-msft — Fri, 18 Jul 2025 11:44:43 GMT

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.

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

v-kpoloju-msft — Mon, 21 Jul 2025 01:03:25 GMT

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.

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

AlexR_DE — Mon, 21 Jul 2025 13:46:23 GMT

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).

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

AlexR_DE — Mon, 21 Jul 2025 19:34:01 GMT

Apparently, you can chain the folders on the Sharepoint Folder connector.

Any idea, how I can pass this based on a given URL?

So this works:

let siteUrl = SiteCollection, files = SharePoint.Contents(siteUrl), Documents = Excel.Workbook(files{[Name="Documents"]}[Content]{[Name="General"]}[Content]{[Name="1_Workstreams"]}[Content]{[Name="9_Rollout"]}[Content]{[Name="excel_file.xlsx"]}[Content]) in Documents

Any idea, how I can get the above query to work with a path stored in a column?

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

MarkLaf — Tue, 22 Jul 2025 02:21:04 GMT

Have you tried SharePoint.Files? Is there some hang-up with that?

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

AlexR_DE — Tue, 22 Jul 2025 07:20:39 GMT

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.

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

v-kpoloju-msft — Wed, 23 Jul 2025 12:07:04 GMT

Hi AlexR_DE,

Thanks for the update. You're right when using SharePoint.Contents(), it only shows one folder level at a time. So, if you are working with a deep folder structure, it can get a bit tricky (and tedious) to navigate manually through each level.

If your goal is to access a file based on a full path (especially when the path is stored in a column or might change), a better option would be to use the SharePoint.Files() connector instead. This connector pulls all files from the SharePoint site, including those in subfolders and gives you the full folder paths, which makes it much easier to filter dynamically.

Here’s a basic example:

let siteUrl = "https://yourcompany.sharepoint.com/sites/yoursite", allFiles = SharePoint.Files(siteUrl, [ApiVersion = 15]), desiredPath = "https://yourcompany.sharepoint.com/sites/yoursite/Shared Documents/General/1_Workstreams/9_Rollout/excel_file.xlsx", file = Table.SelectRows(allFiles, each [Folder Path] & [Name] = desiredPath), output = Table.TransformColumns(file, {"Content", Excel.Workbook}) in output

This way, you don’t need to manually chain folder levels, and you can easily filter for specific files based on the full path either hardcoded or coming from a column.

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.

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

AlexR_DE — Wed, 23 Jul 2025 15:23:09 GMT

But this is leading exactly to the initial problem again: I expect a huge number of files in the same Sharepoint. If the connector now pulls first all files (even if only the meta-datA) it takes ages to refresh only to filter down to a few afterwards?!

So I need a away to pull individual files (for which I know the path though) AND extract the contents with working authentication...

Re: Sharepoint Authentication fails when using Web.Contents with RelativePath on files

v-kpoloju-msft — Mon, 28 Jul 2025 04:06:54 GMT

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.