Справка по Power Query для Excel
Power Query — это технология, внедренная в Excel и другие продукты Корпорации Майкрософт, которая помогает формировать данные. В Excel выберите вкладку Данные на ленте, чтобы увидеть группы & Преобразование данных и запросов & Connections.
После импорта и обновления данных из различных источников вы можете сформировать их в виде пошагового преобразования, которое постепенно создает уникальную таблильную фигуру в нужном виде.
Рекомендуем сделать эту статью справки в избранном для браузера, с помощью Excel и соответствующих сведений справки на сайте справки по Power Query.
Power Query очень многого. Начните отсюда, чтобы получить обную картину и проработать навыки, необходимые для его подготовки.
Надстройки Power Query и Power Pivot
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Power Query сам автоматически генерирует и записывает код запросов на языке M .Но это вовсе не значит, что пользователям нужно писать код – интерфейс Power Query очень понятный и позволяет выполнять операции только с помощью мышки. Большинство операций по преобразованию данных в нем можно выполнить, не написав ни одной строчки кода. Все запросы Power Query записываются и сохраняются, поэтому при последующих подключениях повторять операции еще раз не нужно – они выполнятся автоматически после нажатия на кнопку в меню Данные → Обновить.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В Excel 2016 надстройка уже встроена по умолчанию, на вкладке Данные – раздел «Скачать и преобразовать».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Возможности Power Query
- Из Excel можно напрямую подключаться к самым различным источникам данных: файлы excel, csv, базы данных, папки и т.д.
- Данные в подключенных источниках можно преобразовывать самыми различными способами: сортировать, фильтровать, заменять, делать расчеты, объединять и т.д.
- Преобразованные данные можно импортировать: выгружать на лист, в сводную таблицу, сразу в модель данных (в Power Pivot). Или не выгружать, а просто сохранить запрос.
Операции по преобразованию данных выполняются в окне редактора запросов.
В редакторе Power Query пользователь работает с данными – выполняет преобразования, а его действия автоматически записываются в виде шагов запроса.
О том, как работать в редакторе запросов, читайте в следующей статье .
Как скачать Power Query
Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года:
- для Excel 2010 и 2013 скачивается с сайта Microsoft ;
- для Excel версии после 2016 года ничего скачивать и устанавливать не нужно, так как эта надстройка идет в составе всех лицензий.
Надстройка Power Pivot
Те, кто часто пользуются сводными таблицами , понимают, насколько они удобны и эффективны в работе.
Power Pivot — это надстройка Excel, в которой можно выполнять улучшенные вычисления для сводных таблиц с помощью DAX-формул. Power Pivot значительно расширяет функционал сводных таблиц и позволяет работать с большими объемами данных – в десятки миллионов строк.
Как работает
Вкладка меню Power Pivot во всех версиях Excel, начиная с 2010, выглядит одинаково. Чтобы открыть окно Power Pivot, нажмите в меню Power Pivot → Управление. Если вкладки Power Pivot у вас в меню нет, проверьте, та ли у вас версия Excel . И возможно, вам потребуется добавить эту вкладку в меню .
Возможности Power Pivot
- Power Pivot позволяет обращаться из одной сводной таблицы к данным из нескольких таблиц без формулы ВПР. Это возможно благодаря объединению таблиц в модель данныхс помощью связей.
- Импорт более 100 миллионов строк в рабочую книгу. При работе в «обычном» Excel у пользователей есть ограничения по размеру таблиц – размер листа, то есть чуть больше 1 млн строк. В Power Pivot такого ограничения нет и в него можно загружать миллионы строк. Единственное ограничение по объему — размер файла книги, равный 2 Гбайт, и объем оперативной памяти.
- Благодаря алгоритму сжатия данных в Power Pivot объем файла становится меньше, чем в источнике. Например, текстовый файл объемом 50 Мбайт может сжиматься до 4 Мбайт, занимаемых файлом Excel с таблицей.
- Создание улучшенных формул для сводных таблиц с помощью языка DAX .
В Power Pivot можно писать DAX-формулы для продвинутого анализа в столбцах и создавать меры — вычисления под таблицей.
Кстати, в Power Pivot есть свои инструменты для импорта данных, но из небольшого числа источников – некоторые базы данных, файлы Excel и текстовые и др. Эти инструменты можно найти на вкладке Главная. Так как доступных источников данных не очень много, то, как правило, в качестве источника для него выступает Power Query, функционал которого по подключению намного больше.
Увидеть все загруженные данные можно в нижней части окна Power Pivot – ярлычки с названиями таблиц находятся там же, где названия листов в «обычном» Excel.