Основы Power Query как ключевого инструмента аналитика

Power Query разработан Microsoft для облегчения работы с данными. Он создан для легкой и быстрой работы с данными. По большей части это улучшенный Excel, который позволит уйти от рутины, автоматизируя важные процессы анализа данных.

Для аналитика, который ежедневно сталкивается Excel или работает с данными, знание данного инструмента просто необходимо, так как его использование в работе раскроет множество новых возможностей. В этой публикации раскрыты основы Power Query, которые определенно дадут понимание с чего начать внедрение инструмента в работу.

Что такое Power Query?

С помощью Power Query можно загружать данные из баз данных, CSV-файлов, веб-страниц и других источников, фильтровать их, объединять и преобразовывать в нужные таблицы и графики. Программа позволяет создавать и сохранять запросы для повторного использования, что значительно ускоряет работу с данными и упрощает анализ.

Надстройка Power Query — это специальное дополнение для Microsoft Excel, которая позволяет автоматизировать сбор данных из разных источников, их очистку и преобразование в удобный для работы формат.

Зачем нужен Power Query аналитику?

Power Query нужен аналитику для следующих целей:

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

Примеры использования Power Query?

Так как Power Query — мощный инструмент, который может использоваться различными способами. Ниже приведены некоторые примеры его использования:

Сбор данных : Power Query может быть использован для сбора данных из различных источников, таких как базы данных, файлы CSV и веб-страницы.

Импорт данных из других приложений : инструмент поддерживает импорт данных из Excel, Google Sheets и других приложений.

Преобразование данных : Power Query позволяет преобразовывать данные в нужный формат, например, изменять структуру таблицы или добавлять новые столбцы.

Фильтрация и сортировка данных : С помощью Power Query данные могут быть отфильтрованы и отсортированы по определенным критериям.

Объединение данных : надстройка позволяет объединить данные из нескольких источников в одну таблицу.

Автоматизация задач : позволяет автоматизировать рутинные задачи, такие как обновление данных или создание отчетов.

Плюсы и минусы Power Query

Power Query обладает как рядом преимуществ, так и недостатков, несмотря на которые инструмент по-прежнему остается одним из самых популярных в работе с данными.

Плюсы Power Query

К преимуществам можно отнести:
Автоматизация рутинных операций
Интеграция с другими инструментами
Работа с большими объемами данных
Поддержка разных форматов данных

Читайте также: Что за программа tessa

Минусы Power Query

Power Query не идеален и имеет некоторые недостатки:

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

Где учить Power Query?

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

Для изучения Power Query рекомендуется начать с изучения официальной документации Microsoft. Там вы найдете подробное описание возможностей и функций инструмента, учебные пособия, а также примеры использования. Помимо информации на официальном ресурсе есть тематические сайты и книги по работе с Power Query. Пример одной из них – «Скульптор данных в Excel с Power Query» Н. Павлов, ссылку на которую можно найти в разделе книги для аналитика.

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

Знание Power Query станет вашим преимуществом при поиске работы в сфере анализа данных и поможет вам принимать более обоснованные решения.

Программа power query что это

Первые три команды этого списка позволяют быстро загрузить в Power Query в режиме подключения текущую «умную» таблицу, в которой сейчас стоит активная ячейка или же сразу все «умные» таблицы с листа или из книги.

Также можно быстро удалить все запросы Power Query (вкладка Данные — Запросы и подключения) или существующие подключения к данным (вкладка Данные — Существующие подключения), если вы, например, планируете поделиться текущим файлом с другими пользователями. За это отвечают команды Удалить все запросы и Удалить все подключения к данным. Обратите внимание, что действие этих команд нельзя отменить.

Возможно экспортировать М-код выбранных запросов Power Query во внешние текстовые файлы и, наоборот, импортировать М-код из внешних текстовых файлов и создать запросы на их основе. Это позволяет многократно использовать единожды созданные запросы и огромное количество готовых М-функций, написанных другими пользователями (см., например, https://github.com/KiaraGrouwstra/pquery, https://github.com/ImkeF/M и т.п).

Команда Обновить все запросы в папке выведет на экран диалоговое окно, где можно выбрать папку и задать при необходимости критерий для отбора запросов (если нужно обновлять не все подряд):

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

Команда Создать таблицу параметров создаёт на отдельном листе Параметры (1) заготовку «умной» таблицы с именем tParam (2), где в двух столбцах размещены имена и значения параметров (3), которые можно будет использовать в ваших запросах. В качестве примера создаются параметры для текущей даты, имени текущей книги, её расположения и т.д. Также создаётся специальная функция (4) для их загрузки в Power Query:

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

Чтобы использовать введённые в таблицу параметры, необходимо в М-коде запроса (вкладка Просмотр – Расширенный редактор в окне Power Query) завернуть их в функцию fxGetParam, например:

И, наконец, команда Тест скорости открывает окно, где можно протестировать ваши запросы, замерив длительность их обновления, что весьма пригодится при оптимизации ваших запросов и поиска «узких мест»:

Преобразование формы представления данных при помощи Excel+PowerQuery

В нашем менеджерском труде есть много хорошего и не очень. Но ничто не портит нам жизнь так, как многочисленные эксельки, в которых постоянно нужно что-то ВПР-ить, СУММ-ировать, СУММПРОИЗВ-одить и заниматься прочими рутинными операциями, отнимающими кучу времени и сил. Есть инструмент, позволяющий если не убрать, то минимизировать рутину везде, где есть паттерны данных. Итак, вашему вниманию предлагается чуть более глубокое погружение в принципы работы PowerQuery.

Сразу же хочу сориентировать по поводу того, что уже есть на Хабре по данной теме:

Что такое Power Query (PQ)

Дабы не повторять уже сказанное в материалах, ссылки на которые указаны выше, буду краток: Power Query — это инструмент для преобразования формы представления любых логически организованных данных. То есть на входе может быть любая каша, но в которой есть хоть какая-то логика. А на выходе — таблица. По мнению некоторых экспертов, PQ является ETL инструментом. То есть служит для Extract – извлечения, Transform – преобразования и Load – выгрузки данных. По опыту, данная работа занимает до 60% рабочего времени, например, менеджера по контекстной рекламе и до 90% времени аналитика. Важная особенность данной работы состоит в том, что её, как правило, нужно повторять изо дня в день, неделю за неделей. Бесконечно переносить данные, копипастить, вычислять. Для особо тяжёлых случаев программисты пишут решение, в котором всё это делается автоматически. Проблема состоит в том, что решения дороги, а программисты почти всегда заняты более насущными вопросами, чем автоматизация рутинных операций менеджера. PQ позволяет решить указанные трудности:

Автоматически вытащить данные почти откуда угодно
Преобразовать данные в соответствии с заранее созданной моделью, без необходимости заново повторять одни и те же действия вручную
Представить полученные данные в том виде (видах), которые необходимы

На сегодняшний день 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) – последовательность значений: Источник =
Запись (Record) – пара (или набор пар) вида: [ a = 1, b = , c = true]
Таблица (Table) – конструкция, включающая в себя список колонок и список записей: #table( , < , > )
Функция (Function) – конструкция вида:

Абсолютна идентична этой:

Вычисления происходят на базе значений, полученных в результате предыдущих действий. Чем-то это напоминает цепочку формул 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

power query
M
excel
функциональное программирование
личная эффективность

При подготовке материала использовались источники:
https://budnianalitika.ru/instrumenty-analiza/power-query/osnovy-power-query
https://www.planetaexcel.ru/plex/features/20/22645/
https://habr.com/ru/articles/318058/