Forum Discussion
SharePoint to Power BI API 15
Hi MarkFarquhar
Thank you for reaching out to the Microsoft Fabric Community Forum.
The performance issue is caused by using SharePoint.Files(), which scans the entire SharePoint document library before applying filters. This results in unnecessary API calls and metadata processing, slowing down refresh times even for small datasets. To improve efficiency, we recommend switching to SharePoint.Contents() and navigating step-by-step to your target folder (Documents ➜ General ➜ POWER BI Data Sources ➜ Headcount Reports ➜ Monthly Payroll Reports). Apply early filters in this staging query to select only items where [Kind] is "File" and the file extension is .xlsx or .csv. This approach retrieves only the necessary files and avoids scanning the full library. Please disable loading for the staging query and use it as the source for your transformation queries, replacing their SharePoint.Files() step while keeping your existing “Combine Files” logic. For a single dataset, process CSV files with Csv.Document() and Excel files with Excel.Workbook(), then append them as needed.
We hope this information is helpful. If you have additional questions, please let us know and we will be happy to assist further.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
Hi MarkFarquhar
You're absolutely not alone — slow refresh performance when using SharePoint.Files in Power BI is a well-known issue, especially when pulling multiple small files from SharePoint or OneDrive.
Even though your dataset is small (400 rows across 25 files), the SharePoint.Files connector performs API-level file scanning and metadata evaluation for the entire document library, which can dramatically slow things down.
Let’s walk through why this happens and how you can fix or work around it.
Filter Early — On the Path
Apply hard filters as early as possible in the query:
let
Source = SharePoint.Files("https://yourtenant.sharepoint.com/sites/yoursite", [ApiVersion = 15]),
Filtered = Table.SelectRows(Source, each Text.Contains([Folder Path], "/Shared Documents/Payroll/")),
ExcelFiles = Table.SelectRows(Filtered, each Text.EndsWith([Extension], ".xlsx"))
in
ExcelFiles
Apply folder path and file type filters before expanding or transforming — this drastically reduces load time.
Avoid Reading the Whole Library
If you can, change to SharePoint.Contents() with a more direct path to your folder:
let
Source = SharePoint.Contents("https://yourtenant.sharepoint.com/sites/yoursite"),
Folder = Source{[Name="Shared Documents"]}[Content],
PayrollFolder = Folder{[Name="Payroll"]}[Content]
in
PayrollFolder
SharePoint.Contents is faster because it doesn’t scan the whole site like SharePoint.Files() does.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
thanks for this, I'll give it a try. One point is the fact that I have both xlsx and csv files so presumably I'd need to adapt the above to then ensure that it filters the csv files too?