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

[AlexR_DE]

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?

[MarkLaf]

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

[AlexR_DE]

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.

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

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.

[AlexR_DE]

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

[AlexR_DE]

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?

[BA_Pete]

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

[AlexR_DE]

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