Forum Discussion
Connection Sharepoint folders via Dataflow
Hi masplin , thank you for reaching out to the Microsoft Fabric Community Forum.
You are correct, skipping the last file entry in the M code would allow you to run a function on the entire folder rather than targeting a specific file. And yes it is much faster than pointing to the root SharePoint site and filtering down thousands of files, especially when dealing with large repositories.
Please try below:
- Modified code for entire folder:
let
Source = SharePoint.Contents("https://consolidatedhealthltd.sharepoint.com/sites/PowerBIReporting"),
Folder = Source{[Name="Shared Documents"]}[Content],
Subfolder = Folder{[Name="Trinity Budget"]}[Content]
in
Subfolder
- If you want to combine all Excel files in the folder:
let
Source = SharePoint.Contents("https://consolidatedhealthltd.sharepoint.com/sites/PowerBIReporting"),
Folder = Source{[Name="Shared Documents"]}[Content],
Subfolder = Folder{[Name="Trinity Budget"]}[Content],
Files = Table.SelectRows(Subfolder, each Text.EndsWith([Name], ".xlsx")),
Content = Table.AddColumn(Files, "Data", each Excel.Workbook([Content], null, true)),
ExpandedData = Table.ExpandTableColumn(Content, "Data", {"Name", "Data"})
in
ExpandedData
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.
Impactful IndividualPresumably If I wanted to run a function on a whole folder just skip the last file entry?
I assume this is much faster than pointing the site and then filtering down the folders to the one you require if the site contains alot of folders and files?
Thanks very much for the advice
Community SupportHi masplin , thank you for reaching out to the Microsoft Fabric Community Forum.
You are correct, skipping the last file entry in the M code would allow you to run a function on the entire folder rather than targeting a specific file. And yes it is much faster than pointing to the root SharePoint site and filtering down thousands of files, especially when dealing with large repositories.
Please try below:
- Modified code for entire folder:
let
Source = SharePoint.Contents("https://consolidatedhealthltd.sharepoint.com/sites/PowerBIReporting"),
Folder = Source{[Name="Shared Documents"]}[Content],
Subfolder = Folder{[Name="Trinity Budget"]}[Content]
in
Subfolder
- If you want to combine all Excel files in the folder:
let
Source = SharePoint.Contents("https://consolidatedhealthltd.sharepoint.com/sites/PowerBIReporting"),
Folder = Source{[Name="Shared Documents"]}[Content],
Subfolder = Folder{[Name="Trinity Budget"]}[Content],
Files = Table.SelectRows(Subfolder, each Text.EndsWith([Name], ".xlsx")),
Content = Table.AddColumn(Files, "Data", each Excel.Workbook([Content], null, true)),
ExpandedData = Table.ExpandTableColumn(Content, "Data", {"Name", "Data"})
in
ExpandedData
If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.
alloowishusHelper I
I still have a problem using sub folders, I get this error:
Source = SharePoint.Files("https://[sharepoint-site-URL]", [ApiVersion = 15]),Folder = Source{[Name="Shared Documents"]}[Content],Subfolder = Folder{[Name="Data Item"]}[Content],Subfolder2 = Subfolder{[Name="Landspread"]}[Content],Subfolder3 = Folder{[Name="External"]}[Content],#"Filtered hidden files" = Table.SelectRows(Subfolder3, each [Attributes]?[Hidden]? <> true),Navigation = #"Filtered hidden files"{0}[Content]inNavigation
Expression.Error: The key didn't match any rows in the table.DetailsReason = Expression.ErrorErrorCode = 10061Key = [Name = "Shared Documents"]Table = #table({"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {})- v-hashadapu
Hi alloowishus , Thank you for reaching out to the Microsoft Community Forum.
This is because you’ve switched to SharePoint.Files instead of SharePoint.Contents that I suggested. SharePoint.Files returns a flat list of all files, so there’s no Shared Documents row to drill into, hence the key didn’t match any rows error.
If you want to navigate through subfolders the way you’re doing, stick with SharePoint.Contents and drill down step by step. Also, make sure each step continues from the previous one, your Subfolder3 is currently jumping back up a level. If you do want to use SharePoint.Files, then you’ll need to filter by [Folder Path] instead of navigating by name.