Знакомство с Power Query на примере транспонирования Таблицы Excel

Power Query – это инструмент MS Excel, предназначенный для импорта из самых различных источников и обработки данных. Впервые появился в 2013 году и был доступен в виде специальной надстройки, которую и сейчас можно скачать с официального сайта Microsoft и установить на Excel 2010-2013. После установки и подключения на ленте Excel появится соответствующая вкладка.

В Excel 2016 Power Query уже встроен в ядро программы. Команды управления запросами находятся во вкладке Данные, в группе Скачать и преобразовать (в английском варианте Get & Transform).

Далее будем использовать привычное название Power Query.

На самом деле в Excel и раньше можно было импортировать данные. Для этого в той же вкладке Данные была и есть целая группа команд Получение внешних данных.

Однако их возможности и удобство использования сильно ограничены.

После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое с появлением сводных таблиц. Это не шаг, а прыжок вперед, благодаря которому любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул типа ВПР, затем делаются необходимые расчеты, все агрегируется с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и далее все операции импорта, обработки и выгрузки данных повторяются нажатием одной кнопкой обновления.

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

Вкратце алгоритм работы Power Query таков:

1. импорт данных из выбранных источников данных

2. обработка полученных данных

3. выгрузка

Список возможных источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Также можно легко присоединиться к данным внутри самого MS Excel.

На этапе обработки производят операции по очистке, связыванию, группировке, математическому преобразованию и т.д. Специфика работы именно с такими, плохо организованными и неочищенными данными, объясняет набор инструментов Power Query. Частично они повторяют то, что есть в Excel, но есть и новые, которые значительно расширяют привычный функционал Эксель. Важнейшей особенности работы в Power Query является то, что все шаги записываются. Это дает возможность затем нажатием одной кнопки повторить все операции. Объединяя возможность подключения к данным внутри Excel и новые методы их обработки, мы получаем дополнительные инструменты, которые делают работу в Excel удобнее и быстрее.

На последнем этапе запроса обработанные данные выгружаются в указанное место либо создается только соединение (часто запросы – это только промежуточный этап обработки данных). Но об этом в другой раз.

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

В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.

Первый и самый быстрый способ – воспользоваться Специальной вставкой, поставив галочку напротив опции транспонировать.

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

Второй способ транспонирования – воспользоваться функций ТРАНСП. Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и ввести с помощью комбинации Ctrl + Shift + Enter.

Теперь при изменении данных в источнике автоматически обновится и транспонированный диапазон. Но здесь также есть серьезные недостатки. Во-первых, для вставки формулы ТРАНСП нужно заранее подсчитать, сколько строк и столбцов занимает диапазон, что, мягко говоря, не всегда удобно. Во-вторых, при изменении размера диапазона механизм перестает работать, т.к. транспонированный диапазон зафиксирован и не будет расширяться вслед за источником.

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

Транспонирование таблицы средствами Power Query

Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит адреса обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используют Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T.

Теперь активируем любую ячейку Таблицы с данными и нажимаем кнопку Данные – Скачать и преобразовать – Из таблицы.

Открывается окно редактирования Power Query.

Выглядит, как другая программа, но это только отдельное окно внутри Excel. Интерфейс состоит из пяти частей:

1. Инструменты редактирования – лента, на которой находятся команды Power Query.

2. Строка формул – здесь записывается код языка М для выделенного в данный момент шага обработки.

3. Запросы – скрываемая панель для навигации между запросами текущей книги.

4. Панель результата – место, где отображается результат обработки данных на этапе выделенного шага.

5. Параметры запроса – панель с названием запроса (можно изменять) и перечнем созданных шагов, которые также можно редактировать.

Выделив любой из шагов, мы увидим состояние данных на соответствующем этапе.

Название запроса лучше всего изменить на более говорящее. Довольно часто в книге используют сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».

Из предыдущего рисунка видно, что мы еще ничего не сделали, а два шага уже записаны. Как так? Все просто. Первый шаг – это обращение к источнику, а второй автоматическое определение типа данных у каждого столбца. Поэтому все в порядке.

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

Обратим внимание, что заголовки перенеслись из заголовков Таблицы Excel, которую мы использовали в качестве источника. Однако транспонирование происходит без заголовков. Поэтому, чтобы избежать потери названий столбцов, «опустим» их названия в первую строку таблицы Преобразование – Таблица – Использовать первую строку в качестве заголовков – Использовать заголовки как первую строку.

Таблица с данными получит такой вид.

Теперь можно транспонировать. Используем команду Преобразование – Транспонировать.

Таблица мгновенно изменяется.

Сделаем первую строку назад заголовками. Можно через Преобразование – Таблица — Использовать первую строку в качестве заголовков либо через кнопку в верхнем левом углу от таблицы.

Получим конечный результат обработки.

Задача решена. Все шаги преобразования данных записаны и видны справа.

Осталось измененные данные вернуть в Excel с помощью команды Главная – Закрыть – Закрыть и загрузить.

Если ее нажать, то результат загрузится на новый лист эксель и будет представлять из себя Таблицу Excel с названием, как у запроса. Но давайте пока зайдем в раскрывающийся список, чтобы посмотреть опции выгрузки. В раскрывающемся списке выберем Закрыть и загрузить в… Откроется следующее окно.

Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных, то результат запроса даже без выгрузки в Excel можно будет использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) строк, т.к. на обработку данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить. В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.

Вот и все, дело сделано, мы получили транспонированную таблицу исходных данных.

Самое интересное происходит далее. Если добавить новые данные, то для повторения всех действий достаточно обновить запрос через правую кнопку в панели запросов (см. чуть ниже), либо во вкладке Данные – Подключения – Обновить все.

Добавим в исходную таблицу данные о продажах во втором квартале.

А теперь обновим запрос.

Это просто праздник какой-то! (с).

Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.

Их может быть много, но у нас только один. Сразу под названием видно, сколько загружено строк. Здесь же указываются ошибки, если они есть. Это важно для контроля. Если подвести курсор мыши к названию, то откроется окно с кратким описанием запроса и командами управления снизу.

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

Перечислим наиболее часто используемые среди них.

Изменить – команда открытия окна редактирования. Эквивалентно двойному нажатию левой кнопки мыши по самому запросу.

Обновить – обновление выбранного запроса (если нужно обновить только один запрос, а не все).

Загрузить в… – изменение места загрузки (в таблицу, модель или создания только соединения)

Дублировать – сделать копию выбранного запроса.

Другие команды не менее важны, но их рассмотрим в другой раз.

Панель Запросы книги можно закрыть или снова отобразить с помощью команды Данные – Скачать и преобразовать – Показать запросы.

Итак, мы узнали, что такое Power Query. На примере транспонирования данных увидели, насколько он облегчает и ускоряет работу в Excel.

Серия видеоуроков о Power Query.