Forum Discussion

AlexR_DE's avatar
AlexR_DE
Frequent Visitor
1 year ago
Solved

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([...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    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.

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
1 year ago

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's avatar
AlexR_DE
Frequent Visitor
1 year ago

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