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