Как работает Power Query, для чего нужен, как использовать?

Power Query — надстройка и инструмент для бизнес-аналитики от Microsoft. Он доступен для Windows Excel 2010, 2013 и автоматически входит в Windows Excel 2016. Хоть интерфейс Power Query довольно простой, нужно время, чтобы разобраться в том, как правильно использовать программу. В материале ищите информацию о том, что такое power query простыми словами, что делает и зачем нужен power query, и как правильно использовать инструмент для продуктивной работы.

Power Query что это

Power Query — инструмент от Microsoft, который значительно облегчает рабочий процесс. Его используют в Excel, Power BI и другие программах и приложения. Как он помогает? Благодаря Power Query данные в разы проще собирать, подготавливать и преобразовывать — пользователи также могут объединять таблицы, проводить фильтрацию и редактировать большие объёмы информации. Надстройка хороша и в тех случаях, когда важно привести данные в единый вид и изменить формат.

Возможности Power Query

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

Сортировка 

Чтобы сортировать данные, стоит выгрузить их в новый редактор Excel, а затем нажать одну из кнопок . Для загрузки данных обязательно стоит нажать кнопку «Закрыть и загрузить».

Загрузка данных в таблицу 

Провести экспорт довольно просто, причём данные можно внести из более чем 30 форматов — например, Teradata,SQL, Oracle, различных корпоративных систем — 1C, сервисов из интернета и браузеров.

Приведение к необходимому виду

Таблицы в Excel можно фильтровать по данным источников, изменять порядок столбцов, транспортировать значения и генерировать итоги с помощью дополнительных инструментов. Доступны также текстовые функции, такие как разделение столбцов по символу и количеству знаков, подсчёт количества символов в ячейках, изменение регистра ячеек в столбце. Есть ещё и числовые функции — возведение в степень, корень числа, округление до определённых, сложные арифметические операции.

Очистка данных 

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

Автоматическое обновление запроса 

Это удобно, когда запросы нужно создавать часто — автообновление всегда помогает. Для подключения на вкладке «Данные» нажмите «Существующие подключения источников», а затем щелкните мышкой по подходящему запросу. После этого станет доступна функция «Изменение подключения». Вы можете выставить, через какое время  обновлять данные в Excel — например, раз в 5 или 10 минут. 

Подключение к различным базам данных 

Для подключения в базам данных не обязательно знать язык программирования или другие дополнительные инструменты. Вы можете выбрать MS SQL, MySQL и другие базы данных для работы. Удобно, что все операции проводятся в интерфейсе программы — они транслируются в язык запрос для базы данных. То есть процесс в Power Query автоматизирован и удобен даже для тех, кто раньше раньше не сталкивался с базами данных и подключением к ним. Есть также Power BI — набор разных инструментов аналитики для бизнеса.  Bi хорош тем, что в него входят автоматизированные информационные панели для сайтов.

Установка Power Query

Если у вас новая версия Excel, то надстройка уже входит в пакет — дополнительно устанавливать ничего не нужна. В случае, когда Power Query не установлена, следуйте этим шагам:

1. Загрузить файл легко на официальном сайте Microsoft. Не забудьте отметить  подходящую версию для вашей операционной системы.
2. После установки надстройка должна автоматически открыться на ленте Excel.
3. Иногда случается, что установить Power Query сразу не получается — в таком случае важно проделать такой путь — «Файл», потом «Параметры» и «Надстройки». В надстройках выберите «Надстройки COM» и Перейти. У вас должна стоять галочка рядом с пунктом «Microsoft Power Query for Excel», только тогда надстройка Power для Excel будет правильно функционировать.
4. Сразу добавьте данные продукта с рабочего стола или других папок в инструмент — для этого откройте на панели «Данные пользователей», выберите «Скачать и преобразовать» и щелкните на пункт «Из таблицы», чтобы получить результат анализа.

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

Настройка Power Query 

Инструмент работает при помощи запросов — например, нужно преобразовать или получить данные, для этого существует свой запрос. Настройка позволяет подключить редактор и использовать его на функционал на полную мощность. Чтобы сделать это, необходимо зайти на вкладку «Данные», открыть раздел «Получить и преобразовать данные» и нажать на пункт «Получить данные» Это запустит редактор запросов.

Следующий путь — «Файл»,Параметры» , «Параметры запроса» и «Редактор Power Query». Настройка нужна для отображения редактора, поэтому этот пункт стоит отметить галочкой. Не забудьте зайти в раздел «Конфиденциальность» — здесь необходимо нажать  «Всегда игнорировать уровни конфиденциальности». Редактор запустится быстро, сразу после того, как вы нажмёте кнопку Ок и заполните данные о качестве.

Редактор запросов состоит из:

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

Преобразование данных 

Процесс преобразования данных в Power Query лучше рассмотреть на конкретном примере. Представьте, что перед вами таблица из трёх столбцов — в первом название гостиниц, во втором — их прибыль, а третьем — расходы. Перед вами стоит задача рассортировать их по уменьшению  — на первые строки вывести те гостиницы, что принесли больше всего дохода и так далее. Для этого выделите таблицу, открыть меню и отметить пункт «Данные», затем «Из таблицы» (Из таблицы/диапазона). После нажать «Таблица с заголовками» и поставить галочку. Откроется редактор статьи или таблицы, нужно выделить столбцы «прибыль» и «город», а потом проделать путь преобразование — заполнить — вниз. Пустые строки заполнятся готовыми значениями и будут отображаться по уменьшению стоимости второго столбца — первыми в таблице разместятся те гостиницы, которые заработали больше всего.

Когда вы создаёте запрос, программа записывает ваши шаги. На этом примере вы можете посмотреть все действия во вкладке «Параметры запроса», «Примененные шаги». Каждое из действий можно отредактировать, как мы писали выше, и убрать ненужные шаги, просто нажав крестик. Добавлять шаги также довольно легко — стоит лишь нажать на плюс. В Power Query доступна функция перемещения действий, использование замены или встраивание шагов в середину запроса.

Что ещё важно знать про преобразование данных Power Query? Информация обрабатывается постепенно, так что каждое действие влияет на следующее — при работе с надстройкой очень важна последовательность. При создании новые шагов, редактировании старых, удалении, всегда проверяете, всё ли хорошо с предыдущими операциями, насколько они эффективны. Если вы давно хотели научиться работать с Excel, обратите внимание на онлайн курсы — мы писали о них в статье из блога. 

Простые операции — это далеко не полный функционал программы. Есть и более сложные варианты, например, транспонирование таблиц, удаление дубликатов, замещение, группировка и обработка. 

Типы данных 

Для создания запросов обязательно определять тип данных. Это делается после того, как все ранее описанные действия были проведены. Чтобы определить тип данных, нужно:

• выбрать пункт «Главная», «Тип данных» или «Преобразование», а после нажать «Определить тип данных»
• нажать на значок типа данных в названии столбца
• нажать на раздел «Преобразование», «Дата» и «Выполнить анализ».

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

Расширение настроек и строка формул

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

Использование расширенного функционала трудно представить без включения в работу расширенного редактора. Его плюс в том, что такой редактор даёт доступ к скрипту запроса для следующих действий. Ход действий во многом зависит от того, какие операции вы хотите делать. Когда возможности пользовательского интерфейса не дают хорошего результата, стоит обратиться к расширенному редактору и дополнительным инструментам языка М — это обеспечит более гибкое и понятное преобразование данных, объединение и нагрузку. Большой плюс — погружаться в синтаксис языка не надо, все операции выполняются по готовым пунктам в меню, главное — знать точное расположение параметров. 

Что касается ввода формул, для этого в Power Query есть специальная строка. В стандартных настройках она скрыта, поэтому важно найти путь для её восстановления. Сделать это можно на вкладке «Просмотр» — активация пункта «Строка формул» займёт не более пары секунд. Если хотите посмотреть на все формулы, в этой же вкладке нажмите на кнопку «Расширенный редактор» — после клика всплывёт окно с полным текстом запроса. 

Импорт данных 

Следующий после преобразования этап — импорт. Данные из Excel можно удобно импортировать и выгрузить в другие программы. Есть несколько способов, как это сделать. Для начала выберите раздел «Главная», а потом «Закрыть и загрузить в… ». После этого преобразовать данные можно в виде:

• выгрузки — выберите подходящий способ представления данных, кликните на вариант «Таблица» и разместите информацию на листе. Смарт-таблица возникнет автоматически
• сводной таблицы — нужно найти способ представления, перейти в раздел «Отчет сводной таблицы». Все данные будут размещены в виде сводной таблицы или диаграммы.
• только подключения — этот вариант подходит, если необходимо сохранить запрос. В общем меню выберите вариант «Только создать подключение».
• добавления данных в модель данных — пункт «Добавить эти данные в модель данных» помогает добавлять выбранные данные в модель Power Pivot.

Хочу изучать Excel для работы!

Выбрать подходящий курс бывает довольно сложно. Мы собрали лучшие варианты обучения по Excel — в подборке вы найдёте как быстрые варианты, так и длительные курсы с практикой и поддержкой наставников.