Преобразование формы представления данных при помощи Excel+PowerQuery
В нашем менеджерском труде есть много хорошего и не очень. Но ничто не портит нам жизнь так, как многочисленные эксельки, в которых постоянно нужно что-то ВПР-ить, СУММ-ировать, СУММПРОИЗВ-одить и заниматься прочими рутинными операциями, отнимающими кучу времени и сил. Есть инструмент, позволяющий если не убрать, то минимизировать рутину везде, где есть паттерны данных. Итак, вашему вниманию предлагается чуть более глубокое погружение в принципы работы PowerQuery.
Сразу же хочу сориентировать по поводу того, что уже есть на Хабре по данной теме:
- https://habrahabr.ru/post/271019/ — Отличный обзор возможностей PowerQuery для новичков. Даёт представление об инструменте на примерах. Если вы никогда не использовали PowerQuery, то начать, наверное, стоит с этой статьи. Автор введёт в курс дела
- https://habrahabr.ru/post/311600/ — Погружение в функции языка «M» на примере локализации списка месяцев. Автор кратко коснулся мощнейшей функции List.Accumulate описание которой заслуживает отдельного материала на Хабре. В качестве тизера к такой публикации, могу сказать, что List.Accumulate принимает на вход абсолютно любой тип аргументов
- https://habrahabr.ru/post/274615/ — Краткий обзор возможностей PowerQuery по получению данных из баз SQL. Для новичков
Что такое Power Query (PQ)
Дабы не повторять уже сказанное в материалах, ссылки на которые указаны выше, буду краток: Power Query — это инструмент для преобразования формы представления любых логически организованных данных. То есть на входе может быть любая каша, но в которой есть хоть какая-то логика. А на выходе — таблица. По мнению некоторых экспертов, PQ является ETL инструментом. То есть служит для Extract — извлечения, Transform — преобразования и Load — выгрузки данных. По опыту, данная работа занимает до 60% рабочего времени, например, менеджера по контекстной рекламе и до 90% времени аналитика. Важная особенность данной работы состоит в том, что её, как правило, нужно повторять изо дня в день, неделю за неделей. Бесконечно переносить данные, копипастить, вычислять. Для особо тяжёлых случаев программисты пишут решение, в котором всё это делается автоматически. Проблема состоит в том, что решения дороги, а программисты почти всегда заняты более насущными вопросами, чем автоматизация рутинных операций менеджера. PQ позволяет решить указанные трудности:
- Автоматически вытащить данные почти откуда угодно
- Преобразовать данные в соответствии с заранее созданной моделью, без необходимости заново повторять одни и те же действия вручную
- Представить полученные данные в том виде (видах), которые необходимы
Сразу же давайте дифференцируем Power Query и очень похожий на него инструмент Power BI. Итак, Power Query служит для преобразования данных, как правило, в удобный нам табличный вид, с которым мы далее работаем в Excel. Power BI предназначен для преобразования данных и перевода их в форму графиков и другие обобщённые представления (например, сводные таблицы). Power BI и Power Query используют один и тот же язык «M» для преобразования данных. То есть код языка «M» может быть почти дословно перенесён копипастом из PQ в Power BI и обратно. Почти, потому что в PQ могут быть специфические конструкции для обращения к книге Excel, или именованным диапазонам ячеек, что, понятное дело, неприменимо для Power BI.
На сегодняшний день Power Query доступен только для MS Excel, работающего под ОС Windows. До Excel 2013 включительно, распространялся в виде подключаемого модуля. Начиная с Excel 2016 стал встроенным.
Методы преобразования данных «кнопками» подробно разобраны в статьях указанных выше, особенно в первой. Далее статья будет несколько сложновата для новичков, поскольку мы разбираем подноготную, а конкретно то, что можно увидеть при нажатии на кнопку «Расширенный редактор» в редакторе запросов.
Кнопка «Расширенный редактор»
Как устроен язык «M»
Важно всегда иметь в виду, что конструкция let…. in… является просто «syntactic sugar». let по факту представляет из себя запись с множеством пар вида «имя=значение», а in это просто ссылка на некоторое значение в этой записи (причём не обязательно последнее). Сложновато. Но давайте попробуем разобраться. В языке «M» есть следующие типы значений:
- Примитивные типы (Null, Logical, Number, Text, Date, Time, DateTime, DateTimeZone, Duration): «строка», 14, false, #date (2013,3,8) и т.д.
- Список (List) — последовательность значений: Источник = {1,2,3, «hi»}
- Запись (Record) — пара (или набор пар) вида: [ a = 1, b = {1,2,3}, c = true]
- Таблица (Table) — конструкция, включающая в себя список колонок и список записей: #table ({«Col1», «Col2»}, { {1, 2}, {3, 4} })
- Функция (Function) — конструкция вида:
MyFunction = (x, y, optional z) =>
if z = null then x+y
else (x+y) / z - Тип (Type) — для классифицирования других значений. Подробнее об этом типе см. спецификацию языка «M»
- Бинарный (Binary): #binary ({0×68, 0×65, 0×6C})
Таким образом, следующая конструкция:
Абсолютна идентична этой:
[X=2,Y=X+3][Y]
Вычисления происходят на базе значений, полученных в результате предыдущих действий. Чем-то это напоминает цепочку формул Excel:
A1=A2+A3
A2=A3+2
A3=5
В случае с PQ обращение идёт не к ячейкам, а к значениям. Выражения, которые входят в запись или список, вычисляются методом «ленивых» вычислений. То есть пока мы прямо не сошлёмся на значение, получаемое в результате выполнения некоторого выражения, оно не будет выполнено.
Коротко о секциях
PQ использует концепцию секций. Секция — создает область видимости. Это чем-то похоже на $scope в Angular. Имеется внутренняя переменная #sections, которая возвращает все доступные секции. Все действия, которые выполняются в документе, относятся к одной единственной секции: Section1. При этом невозможно создать ещё одну секцию, например, Section2. Однако, мы можем обратиться к элементам других секций с помощью переменной #shared. Данная переменная адресует к записи содержащей все элементы, которые мы можем видеть: созданные нами, а также доступные нам типы, константы, функции. Таким образом, нам нет необходимости каждый раз лезть в справочник по языку «M», чтобы вспомнить как называется та или иная функция. Достаточно обратиться к переменной #shared. При клике по пустому полю вокруг названия каждой функции, мы можем видеть её описание внизу экрана.
Значение переменной #shared. Можно посмотреть экспресс-справку по встроенной функции
В заключении хотелось бы напомнить, что любые, даже самые сложные конструкции языка «M» по своей сути состоят из типов, указанных выше. В подавляющем большинстве случаев, это записи и списки, плюс немного встроенных функций для преобразований. Чтобы было проще ориентироваться в многообразии доступного функционала, приведу несколько полезных ссылок, которыми сам пользуюсь регулярно.
Полезные ссылки
- Power Query Formula Language Specification (August 2015).pdf
- Power Query (informally known as «M») Formula reference
- Power Query Training — ресурс с кучей бесплатных примеров и трюков. Есть как бесплатные, так и платные уроки
- Language file Notepad++ для подсветки синтаксиса языка «M»
- Репозиторий гуру Power Query Макса Зеленского на Github с кучей готовых функций на языке «M» — Макс разработал алгоритм, который сам подгружает нужные вам для работы функции. См. Readme.md
