Знакомство с Power Query
Каждый опытный пользователь MS Excel мечтает получать данные только в правильной, табулярной форме, без разрывов и лишних символов. В реальности, данные редко попадают к нам в готовом для использования виде. Чаще всего мы получаем информацию в сыром виде, причём люди любят разделять информацию по группам, по листам и файлам. Даже данные полученные из современных автоматизированных систем учёта нуждаются в предварительной обработке. В итоге, большую часть нашего времени нам приходится тратить не на анализ данных или создание отчётов, а:
- на чистку данных от лишних символов;
- на объединение разрозненных данных в единую таблицу;
- на исправление форматов данных, к примеру на трансформацию чисел в американском формате в общемировой формат;
- на фильтрацию и отсеивание ненужной информации.
- написания специальных формул, с использованием,библиотеки функций MS Excel;
- создания процедур на встроенном языке Visual Basic for Applications;
- использования SQL-запросов.
Главный недостаток вышеперечисленных (безусловно очень полезных) инструментов - высокий порог вхождения. На изучение любого из них и совершенствования навыков в их использовании могут уйти месяцы, а то и годы.
Понимая сложившуюся ситуацию в Microsoft решили создать дополнение к MS Excel, позволяющее автоматизировать процесс получения и обработки данных, которое при этом было бы интуитивно понятным и лёгким в изучении. Так появился Power Query.
Вначале, его можно было скачать лишь с официального сайта Microsoft, в качестве отдельной надстройки для MS Excel 2010 MS Excel 2013. Однако, Power Query оказался настолько революционным и обрёл такую популярность, что начиная с MS Excel 2016 он был интегрирован в программу и доступен уже прямо из коробки.
Однако, не желая загружать пользовательский интерфейс MS Excel 2016, для Power Query не стали создавать отдельную вкладку на ленте а втиснули его в качестве отдельной группы с банальным названием Скачать & преобразовать на вкладке Данные (хотя сообщество пользователей MS Excel и продолжает использовать старое имя - Power Query).
Первая стадия работы конвейера Power Query, заключающаяся в получении данных для последующей обработки. В основном, получение данных осуществляется через выпадающее меню Создать запрос на вкладке Данные.
Пользователю предлагается выбрать из четырёх групп источников:
Далее, на вкладке Главная выбираем команду Использовать первую строку в качестве заголовков.
И последний штрих - если присмотреться к столбцу Сумма, то можно увидеть, что Power Query присвоил ему текстовый тип данных.
Чтобы превратить эти данные в числовые выделяем столбец Сумма и на вкладке Главная изменяем тип данных на Целое число.
Понимая сложившуюся ситуацию в Microsoft решили создать дополнение к MS Excel, позволяющее автоматизировать процесс получения и обработки данных, которое при этом было бы интуитивно понятным и лёгким в изучении. Так появился Power Query.
Вначале, его можно было скачать лишь с официального сайта Microsoft, в качестве отдельной надстройки для MS Excel 2010 MS Excel 2013. Однако, Power Query оказался настолько революционным и обрёл такую популярность, что начиная с MS Excel 2016 он был интегрирован в программу и доступен уже прямо из коробки.
Однако, не желая загружать пользовательский интерфейс MS Excel 2016, для Power Query не стали создавать отдельную вкладку на ленте а втиснули его в качестве отдельной группы с банальным названием Скачать & преобразовать на вкладке Данные (хотя сообщество пользователей MS Excel и продолжает использовать старое имя - Power Query).
Архитектура Power Query
Если проводить аналогии, то Power Query можно сравнить с конвейером по подготовке данных для последующего анализа. И весь процесс работы этого конвейера можно условно разделить на четыре стадии:- Получение данных;
- Преобразование данных;
- Загрузка данных;
- Обновление данных.
Получение данных
Первая стадия работы конвейера Power Query, заключающаяся в получении данных для последующей обработки. В основном, получение данных осуществляется через выпадающее меню Создать запрос на вкладке Данные.Пользователю предлагается выбрать из четырёх групп источников:
- Из файла;
- Из базы данных;
- Из Azure;
- Из других источников.
На практике, чаще всего используется группа Из файла, позволяющая получать данные из книги Excel, из файлов CSV и XML, из текстовых файлов и (целым скопом) из папки.
И группа Из других источников, позволяющая получать данные из интернета.
Также, на вкладку Данные вынесена команда Из таблицы, позволяющая получать данные из умной таблицы или диапазона, расположенных на активном листе.
Для примера, рассмотрим получение данных из следующего текстового файла:
На вкладке Данные выбираем Создать запрос -> Из файла -> Из текста. В открывшемся окне Импорт данных находим нужный нам файл и жмём на кнопку Импорт.
Преобразование данных
Откроется отдельное окно Редактора запросов, с собственной лентой, состоящей из четырёх вкладок:
- Главная;
- Преобразование;
- Добавить столбец;
- Просмотр.
Каждая из этих вкладок, за исключением последней, содержит набор инструментов, позволяющий трансформировать полученные данные и привести их к нужному нам виду.
Теперь вернёмся к нашему примеру и попробуем преобразовать данные, полученные нами из текстового файла.
Прежде всего избавимся от четырёх верхних строк, которые не представляют для нас никакой ценности. Для этого, на вкладке Главная выбираем Сократить строки -> Удалить строки -> Удаление верхних строк. Далее, в появившемся окне указываем количество строк, которые необходимо удалить и жмём ОК.
Теперь нам нужно разделить данные по столбцам. Опять же, на вкладке Главная выбираем Разделить столбец -> По разделителю и в появившемся окне в качестве разделителя указываем табуляцию, после чего жмём ОК.
И последний штрих - если присмотреться к столбцу Сумма, то можно увидеть, что Power Query присвоил ему текстовый тип данных.
Чтобы превратить эти данные в числовые выделяем столбец Сумма и на вкладке Главная изменяем тип данных на Целое число.
Загрузка данных
За загрузку данных отвечает выпадающее меню Закрыть и загрузить на вкладке Главная.
Доступны две команды: Закрыть и загрузить и Закрыть и загрузить в...
При выборе первого варианта, который также является вариантом по умолчанию, данные выгружаются на новый лист в виде умной таблицы.
Если же выбрать второй вариант, то откроется отдельное окно, в котором можно указать куда мы хотим загружать итоговые данные.
Пользователю предлагается несколько опций:
- загрузка данных в таблицу с указанием листа (новый либо существующий);
- только создание соединения - в случае если мы планируем использовать этот запрос для объединения с другими запросами;
- добавление в модель данных - если мы хотим загрузить полученные данные прямо в модель данных Power Pivot.
Выберем первую команду из выпадающего меню и загрузим данные на новый лист.
Обновление данных
А теперь главный секрет столь огромной популярности Power Query.
Откроем наш исходный текстовый файл и добавим в него показатели продаж ещё за несколько месяцев.
Чтобы загрузить новые данные на лист, на вкладке Данные, в группе Подключения, жмём на кнопку Обновить.
И это всё! Только подумайте об этом - Вам больше не нужно проходить весь процесс заново, как Вы раньше делали при обычной выгрузке данных из текстовых таблиц. Всё, что Вам нужно всего лишь нажать на одну кнопку!
Заключение
Power Query это действительно поразительный инструмент, позволяющий автоматизировать рутину получения данных из самых разных источников. Любой профессиональный пользователь MS Excel должен непременно добавить Power Query в свой арсенал, тем более что его изучение это одно сплошное удовольствие.
Подписаться на:
Сообщения
(
Atom
)
Сообщения
