Excel power query зависает
Unfortunately, part of my table are calculated dimensions so I cannot just refresh and do a Load to connection. Is there any help here? I've come across this issue before. I've also pulled the query into a new workbook and its fine. This occurs randomly every couple of weeks.
This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Replies (12)
* Please try a lower page number.
* Please enter only numbers.
* Please try a lower page number.
* Please enter only numbers.
No query changes, I.E. no code change
Data sources are csv files. Problem has persisted when data sources are on my hard drive or in a dropbox.
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Part of my table are calculated dimensions so I cannot just refresh and do a Load to connection
Reading between the lines I'm under the impression you actually have a Connection that loads to the Data Model (Power Pivot)
If that's the case, given you seem to run Excel 2016, be aware there was a bug affecting this product (only) where connections - from Power Query - never ended loading to the Data Model, see i.e.:
I am not sure in which exact Version+Build this issue was fixed but David Uyarra who raised the 2nd case above said everything was fine for him with Version 2001 Build 12430.20184, same goes for JP RONSE on the 1st case
Could you confirm you're talking about the same issue and check what Version+Build you run (File > Account > About Excel - on the right of the big button)
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Thanks for responding! I unfortunately don't have a connection that loads to a data model, it just loads directly into a table.
My current version is Excel 2016 Version 2105 Build 14026.20308.
Should I try to revert my Excel back to that version?
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Not sure it makes sense to roll-back until you have +/- identified where the issue could come from.
Something really isn't clear. You say you don't load to the Data Model - let's assume this is right.
You earlier said "Unfortunately, part of my table are calculated dimensions so I cannot just refresh and do a Load to connection"
- Explain exactly what you mean with the above sentence and what I underlined?
- Confirm you use Get & Transform/Power Query to get data from you CSVs (if not how)?
- Confirm the issue is new? You said in your 2nd post "No query changes, I.E. no code change"
This leads me to think the issue could have been introduced by a recent update, Version 2105 Build 14026.20308 having been released on June 18th
Now, if your 99% sure you changed nothing nowhere (query code, settings. ) and the issue didn't exist with the same queries before June 18th or earlier, rolling back would probably make sense at least to check the queries refresh as expected
But don't roll back to Version 2001 Build 12430.20184 as that one is quite old and if you really don't load to the Data Model this means the issue you face is a different than the one they fixed in that version
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Sorry if I'm not being as clear as you would like, let me know if anything else is confusing for you.
1. The powerquery loads into a table where half of the table are calculated dimensions, meaning formula driven. I can't use the workaround solution posted previously in the link I sent previously because I would have to relink all the calculated dimensions all over again throughout the workbook. Not super relevant at the topic at hand.
2. Yes I use the Get & Transform /Power Query to get data from CSVs, but I do not load it into a data model, purely just a table.
3. I'm not 100% sure the issue is new, as I've experienced this issue probably around 2 weeks ago as well. The workaround solution was to copy and paste the query into a new workbook then port over all logic and tabs from the model. Not ideal to have to keep doing.
Is it possible to rollback my excel version if my company is the admin? Just very interesting that I can duplicate the same exact query and it will work fine for a couple days/weeks then stop working again.
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
When I work in the preview - I am expecting the next step to apper instantly
Instead it is reading ALL THE FILES' DATA OVER AGAIN
Why?! Soo slowwww and not efficient.
Please fix - only when I click "Refresh data" - should it do all the process over.
Please fix, please!
Answers
Hi Michael. Were you able to come up with an anonymized version of your scenario?
- Proposed as answer by Ehren - MSFT Microsoft employee Thursday, September 13, 2018 4:59 PM
- Marked as answer by Ehren - MSFT Microsoft employee Monday, December 3, 2018 7:09 PM
All replies
Hi Michael. What data source(s) are you pulling from? CSV files on SharePoint? Excel files on a network folder? Something else?
Very few (5-8) and very small (up to 1 Mb)
What bothers me is that every time I add a step, just a simple step like "change a column name" or multiply a column by a number - it starts to load all the files AGAIN (it shows this at the bottom).
I already have all the preview I need. I just want to rename one column, not to load everything over again.
I find myself WAITING on PowerQuery 80% of my time. 80% of my time. 80% WAITING.
Don't get me wrong - I LOVE you, guys and I admire all the innovation you make with Power BI, Query, etc.
But my User Experience of WAITING for Power Query is AWFUL.
One day I will become a Program Manger of all Microsoft's Data Platform products and I we will definitely focus on the UX.
Anyways - can you help, please?
Hi Michael. Thanks for the additional detail. Are these files .xlsx, .xls, or .xlsb?
Ehren, thank you for your prompt response!
I've doubled checked - there are 6 .xlsx files in network folder, each file is 4.3 Mb and there are 2 more .xlsx files: one is tiny 70 Kb and the other one is 10Mb .xlsx in other network folder
I am running Excel 2013 with Power Query Add-on Version 2.55.5010.1402 64-bit
Thank you for help.
I'll buy you a beer if you solve this!
Hi Michael. Can you try downloading the latest version of the PQ plugin? If that doesn't improve things, can you record a video of what you're seeing and share a link to it here? For example, renaming a column shouldn't cause PQ to re-pull the underlying data. If you're seeing something different, we'd like to get to the bottom of it.
Also, if you could share the M formula text of your queries that would also be helpful. You can copy this from the Advanced Editor in the Power Query Editor.
Thank you very much for trying to help.
Because the files contain sensitive data, I cannot sent them as is. But since you are really want to get to the bottom of it - I will gladly invest two-three hours to change the numbers to random and sent the folder to you so you can improve the Power Query product. I'll get to it this week
Hi Michael. Were you able to come up with an anonymized version of your scenario?
- Proposed as answer by Ehren - MSFT Microsoft employee Thursday, September 13, 2018 4:59 PM
- Marked as answer by Ehren - MSFT Microsoft employee Monday, December 3, 2018 7:09 PM
Not yet - I am working to finish the project for my client and then I can change the actual data for anonymized.
I had to build all from the scratch and to separate the queries to different files :(
It worked OK at the beginning but then - stuck again. waiting and waiting for each step for several minutes.
I've noticed though that the slowdown began when I "Duplicated" two of my queries.
Maybe a DUPLICATION of a query creates some sort of dependency bug?
One thing to try would be to Reference your queries instead of duplicating them. Duplicating them may end up causing PQ to do duplicate work.
I'm experiencing the exact same scenario as OP. Like the OP, my files are private.
I have a query which loads files from CSV (about 5 of them). They are large but not exceptionsally so - about 100,000 rows and 100 columns. Call this query "CSVExtracts".
My main quesy is starting with = CSV Extracts.
Then does a bunch of steps, deleting columns, filtering rows, deriving some new columns.
Bizarrely, this used to work OK, now it seems to grind to a halt.
Just like OP says, making aby change even renaming a column, or even renaming a step in the visual editor just make thes whole thing bog down My memory (maybe unreliable, it used to work fine, now it just doesn't).
PQ has now become unusable.
I will make another thread if needed, but the symptom is exactly like those described so it seems sensible to keep it here.
Should have added ;
Excel 2016 v 16.0.4738 (which looks like a recent update to me ? ) 64 bit version
Running on Win 7 64-bit on regular i5 laptop with 8GB RAM.
Hi there. Can you share the version of PQ you're running? To find it, do the following:
- Click the Data tab of the ribbon
- Click Get Data (or New Query, if you are running Excel build prior to 8067.2115) > Query Options
- Click Diagnostics. You should see the Power Query version printed there.
The optionNew Query/Query Options/DIagnostics/Version returns :
2.61.5192.461 64 bit
By the way this is a corporate licence of Office Pro Plus, it is running on locked-down laptop, and the security patches etc are out of my control.
I went back and looked at some other examples, including the original version of this same query (which only operated on data loaded from 1 file ). I'm finding the same thing - excruciatingly slow performance in the Preview within Power Query Editor.
Some more investigations reveal
which suggests to turn off the option
'Allow data prrview to download in the background'.
I've now done that and the file I am interested in seems to be performing much better. Still not great though, given I'm not really doing anything very intensive (once I load the data I am filtering to just a few 100 or 1000 rows, and adding new columns etc). e.g. takes about 10 seconds if I change the parameter of a integer divide step
Also not clear why this behaviour wasn't happening and then started to happen. Could this be because a new version of Excel 2016 somehow changed default settings.
I have noticed the same behavior. And it gets slower with each action. Since PQ is a recorder/interpreter and must be running from some sort of stack, perhaps the stack gets copied onto the stack and then the change is added to the top. In the olden days we might have called that memory leakage.
Right now I am hung in Excel 365 with a "Document Recovery" screen showing over my sheet. The Query Editor is running but it is greyed-out. Task Manager says 2 to 10% cpu and 48% Memory.
I suspect I will have to kill excel to get out.
I am having a terrible time with the refreshes. I have buffered as many tables as possible, broken down queries into small steps, Turned off Background Refresh. Tried Fast Data Load, didn't do much. Turned off GPU Hardware Acceleration. Only have 1 Excel file open. Outlook and SFB are running too.
Even a simple change, like a field name, causes the preview to be regenerated. I agree on the 80% waiting time, CPU = 100%, Memory typically 90%. this particular project has cost me 2 full weekends plus every evening and I'm still not done.
Using Office 365, Semi-Annual Channel, version 1803 (Build 9126.2315 Click to Run), Lenovo T470, 2 cores, 4 processors, 3.49 GHz, 8 Gb RAM, HDD: 239GB SSD.
Hi JVD_123. Sorry to hear about your refresh trouble. Can you clarify what you're seeing when you change a field name? Is it when the field name changes in the data source, or when you rename a column in the PQ Editor? If it's the latter, the change should be nearly instantaneous.
If you'd like to share more details of your scenario privately, please PM me on the Power BI Community.
I'm so glad to see that I'm not the only one experiencing this. I have also spent many hours just waiting. It's very frustrating. I'm not sure what the issue is because it wasn't so bad at first, but after reading through some comments I wonder if it had something to do with duplicating a query. I have 2 folders with date/country/product data where I need 3 columns from one type of file and one column from the other. After setting up a fantastic query to get the data just right from the first folder, I duplicated the query and changed the source and one step in the query (remove other columns). I feel like this might be when it started becoming ridiculously slow. Changing anything in the PQ editor is now EXCRUCIATING. Refreshing or even opening the PQ editor, I can see it loading and it seems to be going through all of the files multiple times. I did just do a Windows update, I don't know if that might have something to do with it. Anyhow, glad that it's not just me because I'm new to this and still learning the ins and outs.
Hi EmmaBDub. What kind of files do these folders contain? Are you combining the data from these two folders (via Merge or Append), or just pulling them in separately? How many files does each folder contain, and how large are the files in total?
Just want to follow this one up as I have the same issue with my excel/query editor. my query was running quite well without any problems, but has become so slow from one point to another. i even cannot open the query editor correctly, the window only shows the ribbon, the rest is white and i cannot do anything.
Connected databases are Access (OLEDB) and some excel files. I don't use duplicate queries, but have a few merging steps included. I disabled "allow data preview to download in the background", enabled "fast data load", and i don't load data to the data model.
I feel like this has changed since an update (Excel or PBI), but i am not sure. My current version is:
Excel 2016 (16.0.10730.20280) 64-bit
Query Editor 2.61.5192.1301 64-bit
В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.
Power Query — это встроенный инструмент Excel для получения и преобразования данных (Get and Transform).
Специалистам по Excel приходится каждый день тратить уйму времени на преобразование данных. Мы работаем с разными источниками и данные далеко не всегда приходят в удобном виде.
Например, каждый месяц вы получаете таблицу с ответами на вопросы теста сотрудников компании, где вы работаете в таком виде:
Первый столбец — это дата прохождения теста. 2 следующих столбца — это информация о сотруднике и точке продаж. Далее множество столбцов с ответами на вопросы, а вопрос находится в заголовках столбцов. Ваша задача — получить такую таблицу:
В Excel эта задача решается долго и тяжело. Если тесты происходят, например, раз в неделю, то каждую неделю вам придется тратить уйму времени на обработку файлов. Более того, чтобы решить эту задачу стандартными возможностями Excel нужен достаточно высокий уровень владения.
Как вы думаете, сколько времени уйдет на решение этой задачи в Excel Power Query? Всего несколько минут. Более того, вам достаточно решить эту задачу всего 1 раз. А когда придут новые данные достаточно будет всего лишь нажать «Обновить».
Решение
Запуск Power Query
Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.
Теперь в главном меню Excel должна появиться еще одна вкладка. Если вкладка не появилась, то перейдите в меню Файл — Параметры — Настройки — Управление — Надстройки СОМ. В открывшемся окне отметьте галочкой пункт Power Query.
Добавить кнопки Power Query на панель быстрого доступа
Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:
- Изменить запрос
- Запросы и подключения
- Запустить редактор запросов
Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.
Нажмите «Настройка панели быстрого доступа». В появившемся окне справа снизу найдите кнопку «Экспорт/импорт». Нажмите ее и укажите путь к скачанному файлу.
Предварительные настройки Power Query
Откройте редактор запросов любым удобным способом:
- При помощи созданной кнопки в панели быстрого доступа
- Вкладка Данные — Группа Получить и преобразовать данные —Получить данные — Запустить редактор запросов
В окне Редактора запросов нажмите Файл — Параметры и настройки — Параметры запроса. В пункте Загрузка данных сделайте настройки как на картинке:
Теперь перейдите в пункт Редактор Power Query и поставьте галочку «Отобразить редактор запросов».
Далее переходим в пункт Конфиденциальность и выбираем «Всегда игнорировать уровни конфиденциальности».
Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.
Создаем первый запрос Power Query
Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:
Перед вам появится следующее окно редактора запросов Power Query:
Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.
Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.
По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.
Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.
Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.
Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!
Ловушки/проблемы и ошибки — это ситуации, в которых у нас не получается правильно решить задачу. Для начала определимся, что именно мы будем называть ловушками, что ошибками, а что проблемами.
Ошибки в Power Query
Проблемы в Power Query
Проблемой мы будем называть ситуацию, в которой нам нужно составить более уникальный запрос, потому что простое решение не работает. Например, каждую неделю нам присылают примерно одну и ту же Excel-книгу с новыми данными. Вроде бы все хорошо. Количество столбцов и их названия совпадают, но названия листов всегда разные как на картинке ниже.
Составлять универсальные запросы для этой и подобных ситуаций мы будем в уроках по проблемам.
Ловушки Power Query
Ловушка — это ситуация, в которой мы что-то не учли. Распространенный пример — это разворот табличного столбца (Table.ExpandTableColumn). Допустим у нас в запросе есть шаг с разворотом табличного столбца. Мы при развороте отметили все столбцы галочками. Через месяц получаем новые данные. Запрос вроде бы работает как надо, но потом ваш руководитель говорит, что отчет неправильный. Вы выясняете, что не так и обнаруживаете в шаге с разворотом столбца такую картину.
Оказалось, что в новом файле с данными были какие-то новые столбца, которые не разворачиваются. Как быть в этой и других ситуациях мы разберем в уроках по ловушкам.
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?
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.
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.
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.
Читайте также: