Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

power query reading sharepoint

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Power query reading sharepoint files takes 20mins to refresh.

Power query reading sharepoint files takes 20mins to refresh.

ResolvedVersion 365

Thomas has attended:
Excel Intermediate course
Excel Advanced course

Power query reading sharepoint files takes 20mins to refresh.

Hi All
I have a power query running in Excel,

The functions are as follows.
-Find a specified folder path
-Find all of the excel created files within that folder and all subfolders (not deep diving, its 1 level of folders, and pulling aprox 100 files)
-Find the specified sheet in each of the found excel files (same sheet name each time)
-Extract data from a specified name range on this sheet (same range each time)
-Paste the extracted data into a combined table to act as a directory of all of the files
-Various table formatting functions

I used to have this query running via my OneDrive folders, and it worked fine, however the pathing Via onedrive and various shortcuts meant that nobody else in the org can use the document and refresh the query, as they do not have the same file path structure, even if they have access to the document via their own onedrive.

I have changed the pathing of the query to read via the company sharepoint instead, directly to the required folders. This does mean that anybody within the company sharepoint access can now refresh the query, however it takes 20 MINUTES for the query to find the pathing and refresh the values.

Is this normal? Is there a fix? Have I blundered?
I will attach the query script.

RE: Power query reading sharepoint files takes 20mins to refresh

Hi Thomas,

Thank you for the forum question.

It sounds like you've put a lot of effort into setting up your Power Query, and it's frustrating to see it slow down so much after switching to SharePoint. Here are some potential reasons and solutions for the slow performance:

Potential Reasons for Slow Performance
Data Source Connection: SharePoint connectors can be slower compared to local or OneDrive connections due to network latency and the way data is accessed.
Complex Data Transformations: If your query involves multiple steps and transformations, it can slow down the refresh process.
Large Data Volume: Handling a large number of files and rows can significantly impact performance.
Tips to Improve Performance
Filter Early: Apply filters as early as possible in your query to reduce the amount of data being processed.
Use Table.Buffer: Buffering tables can help improve performance by reducing the number of times data is read from the source.
Optimize Data Types: Ensure that you are using the correct data types for your columns to avoid unnecessary conversions.
Disable Auto Date/Time: Turn off the auto date/time feature in Power Query settings to speed up processing.
Use SharePoint.Contents: Instead of using the default SharePoint folder connection, try using SharePoint.Contents which can sometimes be faster.
Steps to Implement Changes
Filter Early:

let
Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/yoursite"),
FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")),
// Apply other transformations here
in
FilteredFiles
Use Table.Buffer:

let
Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/yoursite"),
BufferedSource = Table.Buffer(Source),
// Apply other transformations here
in
BufferedSource
Disable Auto Date/Time:

Go to File > Options and Settings > Options.
Under Current File > Data Load, uncheck Auto Date/Time.
By implementing these optimizations, you should see an improvement in the performance of your Power Query. If the issue persists, it might be worth exploring other data storage solutions or consulting with your IT department for further assistance

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 6 Feb 2025: Automatically marked as resolved.

 

Training courses

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Power BI tip:

Scheduled Refresh and Power BI Service

If you're using Power BI Service, set up scheduled refresh for your datasets. This ensures that your reports are always up-to-date with the latest data. Understand the refresh limits imposed by Power BI Service and optimize your data model and queries to stay within those constraints.

View all Power BI hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.