Not Monitored
Tag not monitored by Microsoft.
35,992 questions
Hi there. Is the Excel file you're importing an OpenXML file (such as .xlsx) or a legacy Excel file (.xls)?
Ehren
Power Query Super Slow
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 30%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEC%3C/text%3E%3C/svg%3E)
El Conquistador
1
Reputation point
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?
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(195.2, 75%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESK%3C/text%3E%3C/svg%3E)
Scott Kailey 1 Reputation point2021-07-15T19:01:24.833+00:00 I am having the same issue. My power query is simply pulling information from another worksheet in my excel workbook into a power pivot table on another worksheet. It takes 4 minutes to pull that data with 4 queries. The data is very limited so it shouldn't take that long to pull it in.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(188.79999999999998, 31%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEM%3C/text%3E%3C/svg%3E)
Ehren (MSFT) 1,781 Reputation points Microsoft Employee2021-07-19T23:00:07.08+00:00 IIf you're using a recent version of Office, please try Sending a Frown about this. If you can share the workbook in question, that will help us diagnose the slowness.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 51%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Rob 26 Reputation points2021-08-29T05:43:56.197+00:00 In my experience, Power Query in Excel is just awful when it comes to using the editor. I've lost count of the number of times it has crashed excel, with nothing useful recoverable. It even manages to corrupt my VBA modules from time to time. I don't have any problems when it comes to executing queries in real life. Its just editing that has become impractical and it seems to get worse the more queries you have in the spreadsheet. Maybe Microsoft should just set 1TB of RAM as a minimum requirement or 'fess up that its not intended for desktop users.
Sign in to comment
8 answers
Sort by: Most helpful
-
Ehren (MSFT) 1,781 Reputation points Microsoft Employee
2020-11-20T01:14:50.103+00:00 -
El Conquistador 1 Reputation point
2020-11-20T22:00:12.503+00:00 Open Office but with binary extension .xlsb
-
Ehren (MSFT) 1,781 Reputation points Microsoft Employee
2020-11-20T23:32:46.6+00:00 Ok. We had a bug for legacy Excel files (such as .xlsb) where importing a single sheet would always read all the sheets from the file. It's possible that you haven't yet received the fix. What version of Power Query do you have inside Excel?
Ehren
-
El Conquistador 1 Reputation point
2020-11-21T00:35:01.427+00:00 I am using Microsoft Office 365 ProPlus Version 2002 (Build 12527.21236) Semi-Annual Enterprise Channel.
-
Ehren (MSFT) 1,781 Reputation points Microsoft Employee
2020-11-30T19:55:43.577+00:00 Can you share the version of PQ that's running inside Excel? You should be able to see it in the PQ options dialog.
-
El Conquistador 1 Reputation point
2021-01-21T15:57:29.61+00:00 2.78.5740.481 64-bit
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(198.4, 87%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EFB%3C/text%3E%3C/svg%3E)
Fabian Besche 1 Reputation point2021-01-21T13:00:39.717+00:00 Hey,
got the same problem that ElConquistador-3740 described and as in this post too: https://social.technet.microsoft.com/Forums/en-US/09a57cce-2d45-45fb-8a60-b6e23d78805b/power-query-is-sooooooo-slow?forum=powerquery.
Please help!
Ragards
-
Fabian Besche 1 Reputation point
2021-01-21T17:30:48.907+00:00 Power BI Version: Version
2.84.801.0 64 Bit
Sign in to comment -
-
Ehren (MSFT) 1,781 Reputation points Microsoft Employee
2021-01-21T17:44:21.6+00:00 It sounds like your PQ version is quite a bit out of date. (My Excel is running version 2.88.) Here's how you can get more recent bits:
- Ensure that Office is set to automatically update
- Check what update channel Office is using. You may be able to switch to a more up-to-date channel.
To quickly verify whether updating will solve the performance issue, you could try your scenario in the latest version of Power BI Desktop. Let me know what you find.
-
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.
-
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.
-
Ehren (MSFT) 1,781 Reputation points Microsoft Employee
2021-01-29T19:42:06.56+00:00 Processing 20 Excel files is a different scenario than what ElConquistador was reporting, where loading just one Excel file was causing all the sheets in the file to be repeatedly read. For your issue, it's likely that you could benefit a possible feature I'm currently working on, which I alluded to in the Technet thread you linked to above.
Sign in to comment -