Connection Sharepoint folders via Dataflow

[theferret]

I'm an expeirenced Power BI user so first time trying to use Fabric to build a new model

I just wanted ot check if i'm being stupid or its by design. I want to connect to a sharepoint subfolder 

In power query I use this as the source (slightly edited)

= Excel.Workbook(Web.Contents("https://consolidatedhealthltd.sharepoint.com/sites/PowerBIReporting/Shared%20Documents/Trinity%20Bud..."), null, true)

This goes directly to the folder or file required

If Fabric I created a data flow and found the all I could put in the url was https://consolidatedhealthltd.sharepoint.com which then listed every sinlge file in the Sharepoint (thousands). Once it had finished creating I can filter down to the folder i need, but seems incredible waste of resources? i tried ot specify tighter and it just failed. 

Is there no way to specify the actual folder or file PowerBIReporting/Shared Documents/Trinity Budget/Budget Input FY25 May24.xlsx???

Thanks for any advice

Mike

[theferret]

t's great thanks very much

[v-hashadapu]

Hi @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:

1. 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.
2. Go to your Dataflow in Fabric -> Power Query Editor -> Advanced Editor
3. 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.

[masplin]

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 · Accepted solution]

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.

[alloowishus]

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]

in

  Navigation

Expression.Error: The key didn't match any rows in the table.

Details

Reason = Expression.Error

ErrorCode = 10061

Key = [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.