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.
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.
Helper II still have a problem using sub folders, I get this error:
Expression.Error: The key didn't match any rows in the table.
- 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.