+ Reply to Thread
Results 1 to 11 of 11

Power Query from Excel source file takes too long

  1. #1
    Registered User
    Join Date
    06-06-2017
    Location
    Wimborne, England
    MS-Off Ver
    Office 365 Business
    Posts
    30

    Question Power Query from Excel source file takes too long

    I have an Excel Workbook with 21 worksheets. One sheet summarises the data into a table with 9 columns and 512 rows. I have set up a power query to retrieve this table into another workbook, but the query takes nearly 3 minutes to refresh. This is too long. The query appears to be reading all the sheets in the workbook. I have tried saving the single sheet as a CSV file and using this as the data source for my query. That query refreshes in 2 seconds. Before I set this up as 'the solution', do any of the esteemed power users in this forum have any suggestions for improving the refresh speed of the query from the original Excel workbook?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Power Query from Excel source file takes too long

    How are you summarising the data?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-06-2017
    Location
    Wimborne, England
    MS-Off Ver
    Office 365 Business
    Posts
    30

    Re: Power Query from Excel source file takes too long

    Columns A to H have text or numeric data. Column I is a SUMIFs formulae. It summarises 22,000 rows of data into 512, based on 3 criteria

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query from Excel source file takes too long

    Can you highlight "Source" in Applied Steps section of Query Settings pane (in PQ editor).
    Go into advanced editor and post what's shown in there? (the M query script).

    Normally, when bringing in specific sheet/table from workbook. Only the sheet is read. But if the sheet isn't specified in first step, it may be reading entire workbook.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  5. #5
    Registered User
    Join Date
    06-06-2017
    Location
    Wimborne, England
    MS-Off Ver
    Office 365 Business
    Posts
    30

    Re: Power Query from Excel source file takes too long

    The workbook is "Headcount.xlsm"
    The worksheet is "New Forecast DataList"

    Please Login or Register  to view this content.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query from Excel source file takes too long

    That looks fine. But try following and see if it makes any difference.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-06-2017
    Location
    Wimborne, England
    MS-Off Ver
    Office 365 Business
    Posts
    30

    Re: Power Query from Excel source file takes too long

    It didn't like it

    Whoops.png

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query from Excel source file takes too long

    I believe you forgot to change one of the section.

    #"New Forecast DataList1" in #"Promoted Headers" line should be changed to Source.

  9. #9
    Registered User
    Join Date
    06-27-2017
    Location
    Collegeville, PA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Power Query from Excel source file takes too long

    MrChipper,
    You wrote,
    Columns A to H have text or numeric data. Column I is a SUMIFs formulae. It summarises 22,000 rows of data into 512, based on 3 criteria
    . I am curious if you've tried to load the 22,000 rows into Power Query, not into a Excel table on a worksheet, but into a Data Query: then do the SUMIFs using Power Query or Power Pivot. Using functions available from the Power Query user interface, you can probably do all the transformations there, or if feeling adventurous, try working it out in DAX. If you post some sample data, perhaps 25 rows of the 22,000 and the 3 criteria to get the 512 rows of the intermediate result, I might be able to show you ways to generate the result in Power Query.

  10. #10
    Registered User
    Join Date
    06-06-2017
    Location
    Wimborne, England
    MS-Off Ver
    Office 365 Business
    Posts
    30

    Re: Power Query from Excel source file takes too long

    Quote Originally Posted by CK76 View Post
    I believe you forgot to change one of the section.

    #"New Forecast DataList1" in #"Promoted Headers" line should be changed to Source.
    Whoops! Ok. I changed it and refreshed. It worked, but it took a little longer, if I'm honest...

  11. #11
    Registered User
    Join Date
    06-06-2017
    Location
    Wimborne, England
    MS-Off Ver
    Office 365 Business
    Posts
    30

    Re: Power Query from Excel source file takes too long

    Quote Originally Posted by Dan Bliss View Post
    MrChipper,
    You wrote, . I am curious if you've tried to load the 22,000 rows into Power Query, not into a Excel table on a worksheet, but into a Data Query: then do the SUMIFs using Power Query or Power Pivot. Using functions available from the Power Query user interface, you can probably do all the transformations there, or if feeling adventurous, try working it out in DAX. If you post some sample data, perhaps 25 rows of the 22,000 and the 3 criteria to get the 512 rows of the intermediate result, I might be able to show you ways to generate the result in Power Query.
    The 22,000 records contain details which needs to be kept confidential. The 512 summary records removes this confidential detail. This is the reason for the query, i.e. to make the summary info available to subsequent processes, without compromising confidentiality.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel File Takes Long Time to Save
    By mikbsirhc in forum Excel General
    Replies: 6
    Last Post: 10-24-2019, 12:40 AM
  2. Dropbox Data Host/Source for Power Query
    By mielkew27 in forum Excel General
    Replies: 0
    Last Post: 04-09-2017, 11:49 PM
  3. [SOLVED] Opening CSV file through VBA takes unrealistically long
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2016, 09:54 AM
  4. Excel file takes a long time to open
    By MikeWard100 in forum Excel General
    Replies: 1
    Last Post: 02-14-2013, 09:57 AM
  5. Loading MS Query takes a long time
    By TonyL in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-14-2006, 03:35 AM
  6. [SOLVED] Edit query takes a long time
    By TonyL in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-11-2005, 06:06 PM
  7. [SOLVED] Excel file takes a long time to save
    By ben@bikecenturies in forum Excel General
    Replies: 1
    Last Post: 02-02-2005, 09:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1