Forum Discussion
Connection Sharepoint folders via Dataflow
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%20Budget/Budget%20Input%20FY25%20May24.xlsx"), 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
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.
6 Replies
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:
- 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.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
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.
t's great thanks very much
