Альберто Феррари и Марко Руссо (оба -- гуру Power Query / BI) не согласятся в вашими айтишниками)
Вообще, Excel -- лишь оболочка для итоговых данных, которые делаются Power Query. Да, что-то удобнее и нагляднее сделать в Power BI, что-то в Excel, а под капотом одно и тоже (Power Query). Пол мира сидит на дашбордах на Power BI, который априори завязан на Power Query так или иначе, и как то всё работает же)
Поэтому утверждать, что Excel не подходит для аналитики, Power BI не подходит для аналитики, Python не подходит для аналитики, SQL не подходит для аналитики, чтоугодноещё не подходит для аналитики — так себе, всё сильно зависит от конкретной задачи.
Что касается самого Power Query (как и любой другой платформы для ETL) — Всё очень зависит от источника данных и качества кода.
Особенности, влияющие на скорость PQ:
1. Сам источник. Наиболее быстрый источник -- модель в SSAS или обычная БД на SQL (MS SQL, PostgreSQL и тд), далее обычный текстовый файл (csv, txt), и уже потом файлы xlsx (которые, по сути, обычный архив).
Если у вас зоопарк "тяжёлых" Excel файлов по 100 столбцов и 100500 строк, которые по ночам обновляются выгрузками из 1С — тут надо менять методологию самого источника, и разворачивать БД или SSAS.
2. Правильная типизация данных (текст, число, целое число и тд). PQ с разными типом данных по разному обращается + сразу покажет ошибки. Особенно важно для даты.
3. Наличие индекса
4. Наличие лишних полей (столбцов)
5. Множество скопированных запросов, а не ссылки на них.
6. Множество джойнов (а-ля Table.NestedJoin) по таблицам, источники которых -- таблицы фактов с миллионами строк. Нужна таблица-справочник "на коленке" -- сделайте её на стороне БД, и путь обновляется ночью.
7. Отсутствие/наличие Table.Buffer, когда нужно и где не нужно. Table.Buffer полезен на больших массивах данных, но жрёт память, как не в себя, зато быстрее. Есть Table.Buffer, но мало оперативки — тормоза. Куча лишних Table.Buffer (привет, копипаст запросов) — тормоза.
8. Объем свободной оперативной памяти (рекомендую от 32, хотя бы, а лучше 64, если много работаете с Power Query)
9. Множественные each в коде в разных местах по мелочи. Надо в целом стараться избегать each — тк это заставляет пробегаться движку по всему массиву строк, физически их читая / трансформируя, или заменять разные each на групповую работу (т.е. не отдельные последовательные шаги с заменой, а один шаг с заменой по списку, например).
10. Неоптимизированный код. Тут много мелочей, начиная от порядка шагов и заканчивая самим кодом (обращение к столбцам, например, обрабатывается быстрее, чем к строкам).
Сначала максимальная чистка на верхнем уровне (удаление ненужных столбцов), потом строк (null, error), потом типизация, потом потом уже фильтры, сложная логика, трансформация. Всё, что можно объединить в один шаг (например, фильтрация) -- должно быть объединено.
11. Использование интерфейсных фич "распределение столбца", "качество столбца", "профиль столбца" — на больших массивах тормоза
12. Включаем трассировку, пишем логи, смотрим, на что уходит больше всего времени. Далее в гугл или нейронку с вопросами.
Финалим. Power Query — мощнейший инструмент, с относительно низким порогом входа. Этап ETL на нём более, чем возможен, и ничуть не уступает Python / SQL.
Средний
