Power Query takes 25 minutes to load: Am I setting this up correctly or should I make a change to imporove load time.

pmcnulty

New Member
Joined
Oct 5, 2018
Messages
3
I have the following queries set up:

1) FY131415 (357,000 rows)
2) FY151617 (6,146,235 rows)
3) AllFY (6,503,415 rows) This is just a combination of query 1 and 2

It takes a total of about 25 minutes to load this power query workbook and the data is updated daily so I need to update this every morning. Is there a best way to set this up in order to have this load quicker. This is only my first power query I have set up so I am very new to this. Any help would be greatly appreciated.

Patrick
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Start here...

Go to Power Query Options
GLOBAL section
Data Load:
- Specify custom default load settings: UN-check the options
- Check: Fast Data Load
Privacy:
- Check: Always ignore Privacy Level settings
CURRENT WORKBOOK section
Data Load:
- UN-check: Allow data preview to download in the background

Does that help?
 
Last edited:
Upvote 0
Ron,

I can't thank you enough. This has cut the time to load in half. I am very grateful for your reply. I am curious if this has any negative consequences for changing these settings. I am wondering why these aren't the default settings. Does changing these have any effect on the accuracy of the data load? Could you elaborate on what each one of these settings means?
 
Upvote 0
Sure
GLOBAL section
Data Load:
- Specify custom default load settings: UN-check the options
This is more for convenience...The default is to port the returned data immediately to a worksheet. If you aren't done with the query, you don't want that to happen. Also, most of my queries are retained as connections that pivot tables access directly. That avoids duplication of data.

- Check: Fast Data Load
Does what it says...speeds up the data. I believe it commits more PC resources to complete the query, but the the PC may become unresponsive for a few seconds. That's a price I gladly pay.

Privacy:
- Check: Always ignore Privacy Level settings
This refers to privacy levels you might set for your power queries. I've had no use for custom privacy levels. It has no impact on security set by your DBA's


CURRENT WORKBOOK section
Data Load:
- UN-check: Allow data preview to download in the background
When this is CHECKED...Power Query refreshes the 1000 record sample for every query in the workbook whenever ANY query is edited. BAD BAD BAD! You'd think it meant to only refresh the data preview for the current query, but: NO.
Also, the preview for the current query is unaffected by this setting...it will always update when edited.

I hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top