Power Query Super Slow

El Conquistador 1 Reputation point
2020-11-18T15:59:28.947+00:00

I have the latest version of Excel 365 Proplus 64-Bit Desktop Version. I have a file that is 25MB and has several worksheets in it. I opened a new workbook and used get data from Excel File to attempt to connect to one of the tables from this 25MB workbook. The connection takes about 6 minutes to complete. Once I have it in PQ Editor using Transform Data it takes another 6 minutes. Then when I select to delete the first two rows from table, alas, I wait another 6 minutes. Then Select use first row as header and guess what, you bet wait another 6 minutes.
Each time I sit and patiently wait I notice in the PQ Editor at the bottom right corner of the screen it cycles through every worksheet in that file during that 6 minutes. So, I can only guess that is what is slowing this query down. How can I tell PQ to ignore those other sheets and just concentrate on the table I need to connect to? I have already made changes in the PQ Settings that will enable faster load times but still no luck. My research on this shows that this has been an issue for a very long time without any resolution. Does anyone have a work-around?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,191 questions

8 answers

Sort by: Newest
Most helpful Newest Oldest
  1. SMD 1 Reputation point
    2022-07-25T15:13:36.84+00:00

    I had the same issue and was wrestling with it for a few months. 'Refresh All' in Power Query would not complete and would not finish loading data. 30 minutes later and Power Query was still loading data. I tried several different potential solutions that other blogs suggested, such as disabling hardware graphics acceleration, enabling multi-threaded processing, and a few other actions that I have since forgotten. None worked. It was very frustrating.

    As a temporary workaround, I ended up displaying 'Queries & Connections' and started refreshing each of my 9 queries one at a time. This worked but was still annoying since I had to refresh each query manually.

    I was worried that I might accidentally skip one of the queries during the manual refreshes, so I recorded a macro of all 9 of the sequential manual refreshes.

    The macro works great and all 9 queries refreshed in around 1 minute.

  2. Jim 1 Reputation point
    2022-05-13T16:50:09.75+00:00

    I've had the same problem off and on and it was driving me nuts. I was running through a VPN to a file server. When I saved the files I was working on to my local machine, I saw a significant improvement in speed. However, I also have a fast computer too (i9, 64 GB RAM). May not fix everyone's issues, but it is worth a shot.

    0 comments
  3. Fabian Besche 1 Reputation point
    2021-02-02T10:06:36.497+00:00

    Cant imagine that there is not already a solution or a Workaruond but if yout got something that works .. i will love it.

    Ill hope you understand that there is everything fine with the query. The problem is caused by the editor that loads all the files instead of the first 1000 Rows and does that all the time at every step instead of one time and than show "This preview can be around 30 day old .." - dont matters for me, only want to work on the query and refresh if i am ready. Then there is no problem with 10 minutes of loading Data ..

    0 comments
  4. Fabian Besche 1 Reputation point
    2021-01-29T09:30:18.66+00:00

    Impossible to work on these files because all Root Data from about 20 .xlsx files, SQL Server, Postgree Server and so on will be loaded when applying a single step in the Editor.

    Trying put the Option "Load Data in the Background" off only causes more problems because you have to refresh the Editor preview manualy.

  5. Fabian Besche 1 Reputation point
    2021-01-29T09:24:59.897+00:00

    Tested File on another Maschine with 2.88 Running. Same Issue as before, as ElConquistador-3740 described and as in the Forum thread linked before.

    0 comments