Время прочтения: 7 мин.

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

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

Что же умеет Power Query?

1) Работа с различными источниками. Данный инструмент позволяет загружать данные из источников разных форматов (например, XLS, TXT, PDF, CSV, JSON, HTML, XML).

2) Преобразование данных. После сбора информацию можно редактировать и преобразовывать разными способами (менять тип данных, менять регистры, очистить от лишних элементов)

3) Отслеживание шагов. В Power Query можно отслеживать каждый шаг обработки данных и при необходимости вернуться к конкретному этапу.

4) Работа с таблицами. Power Query позволяет объединять таблицы (берегись, VLOOKUP!)

Последнее продемонстрируем на простых и понятных примерах.

Допустим, есть две таблицы некоторой овощной лавки, которые надо объединить в одну:

  1. Для того, чтобы открыть их в Power Query, нужно перейти на вкладке «Данные» — «Скачать & преобразовать» — выделить любую ячейку первой таблицы — «Из таблицы» (здесь Power Query предложит сделать её «умной» таблицей, жмём «ОК»)
  2. Попадаем в меню Power Query. Переходим по «Закрыть и загрузить» — «Закрыть и загрузить в…»

В новом окне выбираем «Только создать подключение» — «Загрузить»

  1. Делаем то же самое со второй таблицей
  2. Теперь остаётся лишь нажать «Создать запрос» — «Объединить запросы» — «Добавить»

Важный момент: для успешного объединения необходимо, чтобы заголовки таблиц имели одинаковые названия.

Для того, чтобы сделать объединения по столбцу (аналог join или merge), нужно проделать всё то же самое, но на 4 шаге выбрать «Создать запрос» — «Объединить запросы» — «Объединить», выбрать нужные таблицы и выделить нужный столбец в каждой таблице (в таком случае столбцы могут иметь разное название).

Пример данных «Титаника»

 Предлагем рассмотреть некоторые возможности использования языка программирования M на примере работы с данными пассажиров Титаника (ссылка на репозиторий).

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

Для начала решения данной задачи нужно сформировать столбец, который содержит информацию по стадии возрастной зрелости пассажира. Синтаксис языка M предполагает использование стандартных конструкций, например, «if-elif-else». Можно задать функцию для классификации пассажиров в зависимости от их возраста.

Настраиваемый и условный столбец

Альтернативным способом использования конструкции «if-elif-else» является создание условного столбца.

Отличие от настраиваемого столбца в том, что данная функция изначально предполагает использование конструкции «if-elif-else» и не требует использования кода.

Это является более простым и удобным способом создания столбца на основании какого-либо условия, однако, не предполагает использование арифметических операций над данными.

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

Так была получена информация по тем пассажирам, возраст которых менее 1 года.

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

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

Группировка

Далее для определения количества пассажиров каждого из классов использую функцию «Группировать по».

В новом окне задаю параметры группировки. В данном случае необходимо посчитать количество строк, сгруппированных по значениям столбца «Pclass»:

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

Так как в результате будет создана новая таблица, необходимо выбрать расширенный вариант группировки и задать 2 условия: ранее заданное условие по подсчету количества пассажиров и условия для определения числа выживших. Во втором случае необходимо посчитать сумму значений столбца Survived, сгруппированных по столбцу Pclass (это возможно, так как значения столбца Survived заданы так, что 1 – выживший пассажир, а 0 – не выживший).

Так была получена новая таблица с данными о пассажирах в разрезе классов обслуживания:

Для полного понимания ситуации с выживаемостью пассажиров каждого из классов, проведём 2 операции: поиск числа погибших пассажиров и вычисление доли погибших пассажиров относительно общего числа пассажиров каждого из классов.

Создадим настраиваемый столбец, в котором найдём разницу между количеством пассажиров и числом выживших пассажиров. Это и будет количество погибших пассажиров.

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

Для определения доли погибших пассажиров необходимо поделить число погибших на общее число пассажиров:

Так была получена доля погибших пассажиров.

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

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

Заключение

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

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

Для продолжения серии постов по пассажирам Титаника предлагаем проявить активность – это даст дополнительную мотивацию к публикации. Если вы знаете другие способы решения поставленных задач с помощью Power Query – предлагаем присоединиться к обсуждению под этим постом.