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.  

[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?