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:

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

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