Excel power query зависает

Обновлено: 14.05.2024

Вы можете предположить, что при сортировке данных все нисходящие операции сохраняют порядок сортировки.

Например, если вы сортируете таблицу Sales так, чтобы в первую очередь отображались самые большие продажи магазина, вы можете ожидать, что выполнение операции "удалить дубликаты" возвратит только верхнюю продажу для каждого магазина. Эта операция, на самом деле, может работать. Однако такое поведение не гарантируется.

Из-за того, что Power Query оптимизирует определенные операции, включая их пропуски или разгрузку в источники данных (которые могут иметь собственное уникальное поведение порядка), порядок сортировки не гарантируется при статистической обработке (например Table.Group ,), слиянии (например Table.NestedJoin ,) или удаление дубликатов (например, Table.Distinct ).

Это можно обойти несколькими способами. Ниже приведены два предложения.

Выполните сортировку после применения подчиненной операции. Например, при группировании строк следует сортировать вложенную таблицу в каждой группе перед применением дальнейших действий. Ниже приведен пример кода M, демонстрирующий такой подход: Table.Group(Sales_SalesPerson, , )>>)
Замещает данные в буфер (с помощью Table.Buffer ) перед применением подчиненной операции. В некоторых случаях эта операция приведет к тому, что подчиненная операция сохранит порядок сортировки в буфере.

Вывод типа данных

Иногда Power Query может неправильно обнаружить тип данных столбца. Это обусловлено тем, что Power Query выводит типы данных, используя только первые 200 строк данных. Если данные в первых 200 строках по-разному отличаются от данных после строки 200, Power Query может завершить выбор неправильного типа. (Помните, что неверный тип не всегда приводит к ошибкам. Иногда результирующие значения будут просто неправильными, что затрудняет обнаружение.)

Например, представьте столбец, который содержит целые числа в первых 200 строках (например, все нули), но содержит десятичные числа после строки 200. В этом случае Power Query определит тип данных столбца как целое число (Int64. Type). Это Выведение приведет к усечению десятичных частей любых нецелочисленных чисел.

Или представьте столбец, содержащий текстовые значения даты в первых 200 строках, а также другие виды текстовых значений после строки 200. В этом случае Power Query будет выводить тип данных столбца в значение Date. Это Выведение приведет к тому, что текстовые значения, не являющиеся датами, будут обрабатываться как ошибки преобразования типов.

Так как функция обнаружения типов работает в первых 200 строках, но Профилирование данных может работать по всему набору данных, можно использовать функцию профилирования данных, чтобы получить раннее представление об ошибках (от обнаружения типа или по любому ряду других причин) за пределами первых N строк.

Подключения, принудительно закрытые удаленным узлом

При подключении к различным интерфейсам API может появиться следующее предупреждение:

Data source error: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host

Если вы выпустили эту ошибку, скорее всего, проблема связана с сетью. Как правило, первые пользователи, которым нужно проверить, являются владельцами источника данных, к которому вы пытаетесь подключиться. Если они не полагают, что они закрывают соединение, возможно, что-то по своему пути (например, прокси-сервер, промежуточные маршрутизаторы и шлюзы и т. д.).

Если это возможно только при воспроизведении только с данными или с большими размерами данных, скорее всего, в маршруте есть время ожидания сети. Если это возможно только с большим объемом данных, то клиентам следует обратиться к владельцу источника данных, чтобы узнать, поддерживает ли их API разбиение по страницам, чтобы они могли разбивать свои запросы на небольшие фрагменты. После этого можно использовать альтернативные способы извлечения данных из API (следующие рекомендации по источникам данных).

Комплекты TLS RSA шифров являются устаревшими

Начиная с 30 октября 2020 г. следующие комплекты шифров устарели с наших серверов.

"TLS_RSA_WITH_AES_256_GCM_SHA384"
"TLS_RSA_WITH_AES_128_GCM_SHA256"
"TLS_RSA_WITH_AES_256_CBC_SHA256"
"TLS_RSA_WITH_AES_128_CBC_SHA256"

Ниже перечислены поддерживаемые комплекты шифров.

"TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256"
"TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384"
"TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256"
"TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384"
"TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256"
"TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384"
"TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256"
"TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384"

Это наборы шифров, к которым подключен сервер должен поддерживать подключение из Power Query в сети или Power BI.

в Power Query Desktop (Power BI, Excel) мы не будем управлять комплектами шифров. если вы пытаетесь подключиться к платформе питания (например, к потокам данных платформы питания) или службе Power BI, вам потребуется один из этих комплектов шифров, включенных в ос. вы можете либо обновить Windows версию , либо обновить реестр Windows TLS , чтобы конечная точка сервера поддерживала один из этих шифров.

Чтобы убедиться, что сервер соответствует протоколу безопасности, можно выполнить тест с помощью средства шифрования и проверки подлинности TLS. Одним из примеров может быть ссллабс.

Клиенты должны обновить свои серверы до 1 марта 2021 г. Дополнительные сведения о настройке порядка комплектов шифров TLS см. в разделе Управление безопасностью транспортного уровня (TLS).

Отзыв сертификатов

будущая версия Power BI Desktop приведет к сбою SSL-соединений с настольного компьютера, если в цепочке SSL отсутствует состояние отзыва сертификатов. Это изменение из текущего состояния, при котором отзыв вызывает только сбой соединения в случае, когда сертификат был явно отозван. Другие проблемы с сертификатами могут включать недействительные подписи и истечение срока действия сертификата.

Так как существуют конфигурации, в которых состояние отзыва может быть удалено, например с помощью корпоративных прокси-серверов, мы будем использовать другой вариант пропуска сертификатов, у которых нет сведений об отзыве. Этот параметр позволяет использовать ситуации, когда сведения об отзыве удаляются в некоторых случаях, но вы не хотите полностью понизить уровень безопасности, чтобы продолжить работу.

Это не рекомендуется, но пользователи будут по прежнему иметь возможность полностью отключить проверки отзыва.

Ошибка: вычисление отменено

Ошибка: ключ не соответствует ни одной строке в таблице

Существует множество причин, по которым Power Query может возвращать ошибку, если ключ не совпал ни с одной строкой в таблице. При возникновении этой ошибки подсистема гибридного веб-приложения не может найти имя таблицы, которую она ищет. Причины возникновения этой ошибки могут быть следующих:

Имя таблицы было изменено, например в самом источнике данных.
Учетная запись, используемая для доступа к таблице, не имеет достаточных привилегий для чтения таблицы.
для одного источника данных может существовать несколько учетных данных, которые не поддерживаются в службе Power BI. Эта ошибка может возникать, например, когда источник данных является облачным источником данных, и несколько учетных записей используются для одновременного доступа к источнику данных с разными учетными данными. Если источник данных находится в локальной среде, необходимо использовать локальный шлюз данных.

ограничение: требование к присоединению к домену для компьютеров шлюзов при использовании проверки подлинности Windows

для использования проверки подлинности Windows с локальным шлюзом необходимо, чтобы компьютер шлюза был присоединен к домену. это относится ко всем подключениям, настроенным с помощью "Windows проверки подлинности через шлюз". Windows учетным записям, которые будут использоваться для доступа к источнику данных, может потребоваться доступ на чтение к общим компонентам в каталоге Windows и к установке шлюза.

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

Any kind of help would be much appreciated!!

Hi Matt. If you feel comfortable sharing traces, please capture them and ping me. I can try analyzing them to see what's taking so long.

In the meantime I have found the issue which was actually one of my add-ins in Excel: Microsoft Power BI Publisher for Excel. I disabled that and now it works much better again.

In general, when it comes to performance of Power Query I feel like the following things are worth to consider (as far as I know, please feel free to comment or add. )

- query options: disable "allow data preview to download in the background", enable "fast data load", and do not "load data to the data model" if not necessary (note that this is set default in case of multiple queries). automatic type detection could be disabled too if you don't need all columns.

- watch your code and the connections between tables. clean code, avoid duplicate steps, try to reduce data and do steps only for rows or columns needed for your purpose, for instance change type, merging data.

простота и удобство использования, позволяющая Power BI пользователям быстро собирать данные и создавать интересные и эффективные отчеты для принятия коммерческих бизнес-решений, также позволяет пользователям легко создавать плохо выполняющиеся запросы. это часто происходит при наличии двух таблиц, связанных в связи с тем, как внешний ключ связан SQL таблицами или списками SharePoint. (для записи эта проблема не связана с SQL или SharePoint и возникает во многих сценариях извлечения данных в серверной части, особенно в случаях, когда схема является гибкой и настраиваемой.) Кроме того, нет ничего плохого в хранении данных в отдельных таблицах, имеющих общий ключ, — на самом деле это фундаментальный принцип проектирования и нормализации баз данных. Но это подразумевает лучший способ расширения связи.

рассмотрим следующий пример SharePoint списка клиентов.

И следующий список расположений, на который он ссылается.

При первом подключении к списку расположение отображается как запись.

При развертывании записи отображаются поля, Соединенные из вторичной таблицы.

при расширении связанных строк из одной таблицы в другую поведением Power BI по умолчанию является создание вызова Table.ExpandTableColumn . Это можно увидеть в созданном поле формулы. К сожалению, этот метод создает отдельный вызов во вторую таблицу для каждой строки в первой таблице.

Если запросы к этому узкому месту достигнуты, лучшее решение заключается в том, чтобы избежать появления запроса в строке с помощью классической таблицы. Это гарантирует, что для получения второй таблицы будет только один вызов, а оставшаяся часть расширения может произойти в памяти с помощью общего ключа между двумя таблицами. В некоторых случаях разница в производительности может быть очень большой.

Сначала начните с исходной таблицы, отметив столбец, который необходимо расширить, и убедитесь, что у вас есть идентификатор элемента, чтобы его можно было сопоставить. Как правило, внешний ключ имеет имя, аналогичное отображаемому имени столбца с добавленным идентификатором . В этом примере это LocationId.

Во-вторых, загрузите вторичную таблицу, включив в нее идентификатор, который является внешним ключом. Щелкните правой кнопкой мыши панель запросы, чтобы создать новый запрос.

Наконец, объедините две таблицы, используя соответствующие имена соответствующих столбцов. Обычно это поле можно найти, сначала развернув столбец, а затем выполнив поиск соответствующих столбцов в предварительной версии.

В этом примере видно, что LocationId в основном списке соответствует идентификатору во вторичном списке. Пользовательский интерфейс переименовывает это в Location.ID , чтобы сделать имя столбца уникальным. Теперь давайте будем использовать эти сведения для слияния таблиц.

Щелкнув правой кнопкой мыши панель запросов и выбрав новый запрос > объединить > запросы слияния как новые, вы увидите удобный пользовательский интерфейс, который поможет вам объединить эти два запроса.

Выберите каждую таблицу из раскрывающегося списка, чтобы просмотреть предварительную версию запроса.

Выбрав обе таблицы, выберите столбец, который логически соединяет таблицы (в данном примере это LocationId из основной таблицы и идентификатора из вторичной таблицы). В диалоговом окне будет указано, сколько строк совпадает с использованием этого внешнего ключа. Для этого типа данных, скорее всего, потребуется использовать тип объединения по умолчанию (левое внешнее).

обновление этих данных приведет к тому, что только два вызова SharePoint — один для основного списка и один для дополнительного списка. Соединение будет выполнено в памяти, что значительно сокращает количество вызовов SharePoint.

Этот подход можно использовать для всех двух таблиц в PowerQuery, имеющих соответствующий внешний ключ.

SharePoint списки пользователей и таксономии также доступны в виде таблиц, и их можно объединить точно так же, как описано выше, при условии, что пользователь имеет достаточные привилегии для доступа к этим спискам.

На этот процесс можно повлиять, и я хочу поделиться таким советом: нужно временно ограничить число строк в конкретном запросе. Например, в вашем запросе миллион строк, вам же нужно временно оставить в этом запросе 100 строк. После того, как вы ограничите количество строк до 100, сделайте все необходимые преобразования запросов и в заключение удалите тот шаг, в котором вы ограничивали количество строк. Тем самым вы ускорите весь процесс работы с запросами.

Вот как это реализовывается на практике.

Редактор запросов Power Query:

На главной странице нажимаем «Изменить запросы»

В данной таблице 996 строк. Для примера ограничим таблицу 20 строками.

В основном поле, в левом верхнем углу правой кнопкой мыши нажимаем на значок на пересечении столбцов и строк.

В появившемся меню нажимаем «Сохранить верхние строки…».

Затем в открывшемся окне указываем цифру 20.

Наша таблица уменьшилась до 20 строк.

Теперь мы можем работать с этим запросом (чистить, модернизировать, соединять с другими запросами и т.д.) и эти действия будут проводиться достаточно быстро, потому что количество строк уменьшилось до 20.

Для примера, создадим шаг, состоящий из нового столбца.

На вкладке «Добавление столбца» нажимаем «Настраиваемый столбец»

В поле «Пользовательская формула столбца» указываем информацию, которая будет отражена в новом столбце. Например, настроим новый столбец как произведение данных столбца 2 и 5000.

Появился новый столбец.

Теперь самым последним шагом необходимо вернуть весь удаленный ранее массив данных (удаленные строки).

Для этого переходим в поле «параметры запроса», которое находится справа, и ищем тот шаг, на котором удаляли строки. В нашем случае это шаг «сохранение первых строк»

Удаляем данный шаг (нажимаем на крестик слева), в появившемся окне подтверждаем удаление.

Все ранее удаленные строки вернулись в таблицу.

Таким образом у нас появился новый столбец, где результат заданного действия, отражен по всем строкам огромного массива данных. И самое главное –процесс прошел быстро.

Пользуйтесь данным советом, и работа с Power Query у вас пойдет заметно быстрее!

Читайте также: