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