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 theferret , thank you for reaching out to the Microsoft Fabric Community Forum.
Fabric's Dataflows Gen2 currently operates differently from Power Query in terms of SharePoint connections, but there are ways to make the process more efficient.
Please Consider below:
- Instead of pointing directly to the SharePoint site, you can refine your query within Fabric’s Dataflows Gen2 using M code in the Advanced Editor.
- Go to your Dataflow in Fabric -> Power Query Editor -> Advanced Editor
- Example code: let
Source = SharePoint.Contents("https://consolidatedhealthltd.sharepoint.com/sites/PowerBIReporting"),
Folder = Source{[Name="Shared Documents"]}[Content],
Subfolder = Folder{[Name="Trinity Budget"]}[Content],
File = Subfolder{[Name="Budget Input FY25 May24.xlsx"]}[Content],
Excel = Excel.Workbook(File, null, true)
in
Excel
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.
masplinImpactful Individual
Presumably 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
- v-hashadapu
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.
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"}, {})