SharePoint to Power BI API 15

[MarkFarquhar]

Hi,

I've created a set of visuals based on data from a SharePoint list, and also data from multiple files (payroll) saved in SharePoint however when I refresh in power query the refresh times are painfully slow. As an example, it's currently refreshing data from the SharePoint files at a rate of 1 MB per 5 seconds. 

The volume of the data in the files is small (about 400 rows across 25 files) but despite speaking with the IT team for guidance and help, there's no improvement in performance. Does anyone have any ideas on how I can improve performance? 

The query for the SharePoint files is using 'SharePoint.Files. Does anyone have any ideas on how I can improve performance as I can't really release it for end user use at present.  

[Shubham_rai955]

It sounds like your slow refresh is mostly because Power BI is loading all SharePoint files first and then filtering them, which wastes time. To speed things up, try to connect in a way that only pulls the files you need from the start. For example, use Web.Contents or SharePoint.Contents with a filter at the first step, instead of SharePoint.Files. Also, filter early in Power Query so you don’t load extra data and metadata. If possible, combine your small files into one table before loading, maybe using Power Automate. You can also enable incremental refresh so only new data is fetched. These small changes can make refresh times

[v-karpurapud]

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.

[johnbasha33]

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 !!

[MarkFarquhar]

Also on the Sharepoint content, if the file path is below I assume I need to reflect each level of folder and sub folder instead of just referencing the bottom level folder with the files in it? 

So if the path is FinanceUK - General\POWER BI Data Sources\Headcount Reports\Monthly Payroll Reports and the target files are in the Monthly Payroll folder, so I need to reference all levels? 

[MarkFarquhar]

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?