v-kakarlab1's avatar
v-kakarlab1
Microsoft Employee
3 months ago
Status:
New

Ability to Select Specific Files from SharePoint Without Loading All Files

We would like to propose an enhancement to improve efficiency when working with SharePoint data sources.

Currently, when connecting to a SharePoint site, all available files are loaded, and users must then apply filters to identify and work with the required files. This process can be time-consuming and resource-intensive, especially in environments with a large number of files.

We are requesting the ability to directly select or specify the required files from the SharePoint site at the time of connection, without the need to load and filter the entire dataset.

Benefits of this enhancement:

  • Improved performance by reducing unnecessary data loading
  • Enhanced user experience with more streamlined file selection
  • Reduced resource consumption for large-scale SharePoint environments

This capability would significantly improve usability and efficiency for enterprise scenarios leveraging SharePoint with Fabric/Power BI.

power bi

1 Comment

  • matthias_vc's avatar
    matthias_vc
    Frequent Visitor
    2 months ago

    Hey I 100% agree with this idea. Now it is unnecessarily difficult to get data from Large Sharepoint Sites. For the original Poster: There ARE quicker (but not easier) ways to get data from Sharepoint in a quick way. I've created this Query that basically reads files from a sharepoint folder using the API (note if you want a single file you can use only the last bit). This Query should refresh as well in the PBI Service so you can auto-refresh (which other solutions I found online didn't). Anyway you can try it:

    let 
SharepointURL = "https://[mycompany].sharepoint.com", 
SharepointSite = "/sites/[myCompanySharepointSite]/", 
RelativeFolderPath = "[FirstFolderName]/[SubFolderName]/.../[LastSubfoldernameAndNoTrailingSlashes]", 
Source = Xml.Tables( Web.Contents( SharepointURL&SharepointSite&"_api", [ RelativePath = "web/GetFolderByServerRelativeUrl('"&SharepointSite&RelativeFolderPath&"')/Files", IsRetry = true ] ) ), entry = Source{0}[entry], 
#"Changed Type" = Table.TransformColumnTypes( entry, { {"id", type text}, {"title", type text}, {"updated", type datetime} } ), 
#"Renamed Columns" = Table.RenameColumns( #"Changed Type", { {"title", "Name"}, {"updated", "Date modified"} } ), 
#"Added ServerRelativeUrl" = Table.AddColumn( #"Renamed Columns", "ServerRelativeUrl", each Text.BetweenDelimiters([id], "decodedurl='", "')"), type text ), 
#"Added Folder Path" = Table.AddColumn( #"Added ServerRelativeUrl", "Folder Path", each SharepointURL&SharepointSite&RelativeFolderPath&"/", type text ), 
#"Added Extension" = Table.AddColumn( #"Added Folder Path", "Extension", each if Text.Contains([Name], ".") then "." & Text.AfterDelimiter([Name], ".", {0, RelativePosition.FromEnd}) else null, type text ), 
#"Added Custom" = Table.AddColumn(#"Added Extension", "FileName", each Text.AfterDelimiter([id],"/",{0, RelativePosition.FromEnd})), 
#"Replaced Value" = Table.ReplaceValue(#"Added Custom","')","",Replacer.ReplaceText,{"FileName"}), #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([FileName] <> "PAKKETTEN v0.1.xlsx")), 
#"Added Attributes" = Table.AddColumn( #"Filtered Rows", "Attributes", each [Hidden = false], type record ), 
#"Added Content" = Table.AddColumn( #"Added Attributes", "Content", each Web.Contents( SharepointURL&SharepointSite&"_api", [ RelativePath = "web/GetFileByServerRelativePath(decodedurl='" & [ServerRelativeUrl] & "')/$value", IsRetry = true ] ), type binary ) in #"Added Content"


    Note I'm not 100%sure on the 'isRetry = true' part. I think it might lead to unneccesary extra API calls but I thought it prevented with stale data. (Maybe someone Else in the community can help with this)

Recent ideas