Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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 by Ron Coderre; Dec 4th, 2018 at 03:08 PM.
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  4. #4
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  5. #5
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •