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

[pmcnulty]

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

 

[Ron Coderre]

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?

 

[pmcnulty]

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?

 

[Ron Coderre]

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.

 

[Matt Allington]

What is your data source? If it is a database, you may find my article about query folding in power query helpful in addition to the good advice from Ron