Начало работы в Power Query

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

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

надстройка power query

Power Query в меню Excel

Зависимо от того, какая у вас версия Excel, вид надстройки Power Query может смотреться по-разному. В Excel 2010 и 2013 надстройка возникает в виде отдельной вкладки «Power Query» (если у вас таковой вкладки нет, прочитайте, как ее установить ).

В Excel опосля 2016 года Power Query уже встроен по дефлоту и находится в меню Данные → раздел Получить и конвертировать данные (в неких версиях Excel этот раздел именуется Скачать & конвертировать).

Работа с данными в Power Query

Power Query умеет:

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

Таковым образом, Power Query – это настоящий ETL-инструмент (Extract, Transform, Load).

Подключение к данным в Power Query

В Power Query можно подключать данные из самых различных источников: таблицы в самом файле и остальных Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в вебе, файлы xml и json, pdf-файлы, данные из канала OData и так дальше. Также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.

Чтоб поглядеть, какие конкретно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (либо Сделать запрос, если у вас не новенькая версия Excel).

Интересно почитать: Как в эксель поставить номера по порядку

Для примера добавим в Power Query данные из таблицы.

— в Excel 2010 и 2013: вкладка Power Query → Из таблицы (либо С листа).
— для Excel опосля 2016: меню Данные → Из таблицы (Из таблицы/спектра).

В открывшемся окне поставьте галочку «Таблица с заголовками».

Таблица с данными при всем этом перевоплотится в «умную» smart-таблицу.

Раскроется окно редактора запросов, в каком будет наша таблица. Жмем клавишу в меню Основная → Закрыть и загрузить. Готово!

Чтоб открыть перечень запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится перечень всех запросов, сделанных в файле.

Редактор запросов Power Query

Разберем подробнее интерфейс редактора запросов Power Query.

Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Либо щелкните 2 раза мышкой по наименованию запроса на вкладке Запросы и подключения.

Итак, в редакторе Power Query есть:

Лента редактора запросов для вкладок меню: Основная, Преобразование, Добавить столбец, Просмотр.
Список сделанных запросов, который можно свернуть / развернуть.
Строчка формул.
Заглавие самого запроса.
Примененные шаги запроса: записанные шаги получения либо преобразования данных. Их можно редактировать, выбирая в перечне, изменять последовательность шагов, добавлять новейшие либо удалять.
Область подготовительного просмотра, в какой выводится итог преобразования данных для всякого шага.
Меню для данных, которое раскрывается при нажатии правой клавишей мышки.
При выбирании правой клавишей мыши наименования шага возникает его контекстное меню.

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

Поглядим на ординарном примере, как конвертировать данные в Power Query.

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

Интересно почитать: Как в excel выделить цветом повторяющиеся значения

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

выделите таблицу (можно выделить таблицу полностью либо одну из ячеек);
изберите в меню Данные → Из таблицы (Из таблицы/спектра);
в показавшемся окне поставьте галочку рядом с «Таблица с заголовками» → ОК;
в открывшемся редакторе запросов выделите столбцы «показатель» и «город», нажав мышкой на наименования столбцов с зажатым Ctrl;
в меню нажмите Преобразование → Заполнить → Вниз.

Готово! Пустые строчки заполнены значениями из ячеек сверху.

При разработке запроса Power Query сам автоматом записывает его шаги. Их можно узреть в области справа Характеристики запроса → Примененные шаги.

Шаги запроса можно редактировать, выбирая мышкой (таблица в области подготовительного просмотра при всем этом тоже поменяется). Ненадобные шаги удаляются при нажатии на «крестик». Можно добавлять новейшие шаги в середину запроса либо поменять их местами, перемещая мышкой.

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

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