Power Query Slowness

Jakub

New member
Joined
Mar 29, 2022
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
I have an issue when I'm working with power query on excel which is using file from share drive compared to the local drive.
When using the same file but on network instead of locally the refresh time is 12 minutes vs 2. Another issue is that loading times of any step when creating query is significantly longer (15-30 minutes vs 3-4 minutes when it's using file on local drive as a source). Are there any ways to shorten that time?
 

alansidman

Member
Joined
Oct 1, 2018
Messages
365
Reaction score
1
Points
18
Location
Steamboat Springs
Excel Version(s)
O365
This may be a connection issue that needs to be discussed with your IT people to see what is causing transmission slowness.
 

Jakub

New member
Joined
Mar 29, 2022
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
If I can do nothing about it(nobody really cares about issues like that and is not going to fix them) what would be the best way to optimize working on Power Query?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
If the network is the problem, I doubt that optimising the Power Query will gain much.

2 minutes on local seems quite a time, but even if you reduce it to a half, it will still be 6 minutes on the network drive. Without knowing the data or the query it is difficult top advise on any optimisation.
 

pinarello

Member
Joined
Jun 21, 2019
Messages
191
Reaction score
2
Points
18
Location
Germany
Excel Version(s)
Office 365
As a workaround, you can try the following: Read the file with a simple macro from the share drive to save it locally. then you can assign the local file as source to the Power Query. If this has the desired effect, you should create another macro that first calls the previously created macro and then executes the Power Query. You can then assign this macro to a button so that it is executed at the push of a button.
 
Top