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

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.