Начало работы в Power Query
Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.
Несмотря на то, что данные в Excel можно загружать с помощью инструментов Power Pivot , возможностей для преобразования и доступных источников данных в 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 и перейдите в меню: — в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
— для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).
В открывшемся окне поставьте галочку «Таблица с заголовками».
Таблица с данными при этом превратится в «умную» smart-таблицу.
- Откроется окно редактора запросов, в котором будет наша таблица. Нажимаем кнопку в меню Главная → Закрыть и загрузить. Готово!
Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.
Редактор запросов Power Query
Разберем подробнее интерфейс редактора запросов Power Query.
Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.
Итак, в редакторе Power Query есть:
- Лента редактора запросов для вкладок меню: Главная, Преобразование, Добавить столбец, Просмотр.
- Перечень созданных запросов, который можно свернуть / развернуть.
- Строка формул.
- Название самого запроса.
- Примененные шаги запроса: записанные шаги получения или преобразования данных. Их можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
- Область предварительного просмотра, в которой выводится результат преобразования данных для каждого шага.
- Меню для данных, которое открывается при нажатии правой кнопкой мышки.
- При выборе правой кнопкой мыши названия шага появляется его контекстное меню.
Преобразование данных
Посмотрим на простом примере, как преобразовать данные в Power Query.
Допустим, у нас есть таблица с выручкой и расходами по городам за несколько лет. В таблице эти показатели разделены на две группы. Столбец с городами тоже имеет группировки (смотрите рисунок).
Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:
- выделите таблицу (можно выделить таблицу целиком или одну из ячеек);
- выберите в меню Данные → Из таблицы (Из таблицы/диапазона);
- в появившемся окне поставьте галочку рядом с «Таблица с заголовками» → ОК;
- в открывшемся редакторе запросов выделите столбцы «показатель» и «город», нажав мышкой на названия столбцов с зажатым Ctrl;
- в меню нажмите Преобразование → Заполнить → Вниз.
- Готово! Пустые строки заполнены значениями из ячеек сверху.
При создании запроса Power Query сам автоматически записывает его шаги. Их можно увидеть в области справа Параметры запроса → Примененные шаги.
Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.
Обработка данных в Power Query выполняется последовательно, шаг за шагом, и каждое последующее действие использует результаты предыдущего. Поэтому при добавлении новых шагов или изменении их последовательности обязательно проверьте, все ли в порядке со следующими операциями. Проверить, все ли в порядке, можно, нажав на самый нижний шаг.
Кроме простых операций с данными, Power Query умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.
Управление запросами (Power Query)
Вы можете управлять запросами в области «Запросы» Редактор Power Query или на вкладке «Запросы» на панели «Запросы & подключения» в Excel. Это особенно полезно, если у вас много запросов и вы хотите быстро найти запрос. После поиска запроса можно выполнить ряд дополнительных действий, таких как дублирование, ссылка, слияние, добавление, совместное использование и удаление запроса.
Сведения о запросах и подключениях
При создании запроса сведения о подключении создаются в фоновом режиме. Роль запроса — формирование данных. Сведения о подключении должны поддерживать сведения об источнике данных и обновлять их в соответствии с параметрами обновления, такими как частота.
Не все запросы могут быть загружены на лист. Это может произойти, когда вы импортируете данные из внешнего источника данных, формируете данные в Редактор Power Query, выбираете home> Load To, а затем используете диалоговое окно «Импорт данных», чтобы поместить запрос в модель данных или ODC-файл.
Использование команд управления запросами
Ниже приведена сводка команд, которые можно использовать в том порядке, в котором они отображаются в соответствующих контекстных меню на вкладке «Запросы» на панели «Запросы & Подключение» в Excel или на панели «Запросы» Редактор Power Query. Некоторые команды доступны только на одной панели. Многие из этих команд также доступны на вкладке «Запрос контекста» на ленте. Все запросы в книге отображаются и сортируются по дате и времени последнего изменения, начиная с последнего.
-
Выполните одно из следующих действий:
в Excel выберите запросы> данных & подключения> запросы.
Edit Edits the query in the Редактор Power Query. Доступно только на вкладке « Запросы » & «Подключения «.
Удалить Удаляет запрос.
Переименовать Изменяет имя запроса.
Обновить Предоставляет актуальные данные из внешних источников данных. Доступно только на вкладке « Запросы » & «Подключения «. Дополнительные сведения см. в разделе «Обновление подключения к внешним данным» в Excel.
Загрузить в Отображает диалоговое окно «Импорт данных», в котором можно выбрать способ просмотра данных, место их размещения и добавить в модель данных. Доступно только на вкладке « Запросы » & «Подключения «.
Дублировать Создает копию выбранного запроса с тем же именем, что и исходный запрос, добавленный (2). Вы можете переименовать запрос, чтобы упростить его идентификацию. Последующие изменения исходного запроса не влияют на новый запрос.
Ссылки Создает новый запрос, который использует шаги предыдущего запроса без необходимости дублировать запрос. Новый запрос следует тому же соглашению об именовании, что и команда Duplicate . Вы можете переименовать новый запрос, чтобы его было проще идентифицировать. Последующие изменения исходного запроса, которые приводят к изменению выходных данных, влияют на новый запрос.
Объединить Объединение столбцов в запросе с соответствующими столбцами в других запросах. Дополнительные сведения о слиянии см. в разделе «Запросы на слияние». Доступно только на вкладке « Запросы » & «Подключения «.
Добавить Добавление столбцов в запрос с соответствующими столбцами в других запросах. Дополнительные сведения о добавлении см. в разделе «Запросы на добавление». Доступно только на вкладке « Запросы » & «Подключения «.
Переместить в группу Перемещает запрос в группу в списке или, если группы отсутствуют, выберите «Создать группу», чтобы создать ее. Присвойте ему понятное имя, например «Бюджет» или «Маркетинг». Используйте группы для упорядочивания множества запросов по категориям. Вы даже можете создавать группы в группах.
Вверх Перемещает запрос вверх в списке запросов.
Переместить вниз Перемещает запрос вниз в списке запросов.
В диалоговом окне «Создание функции » отображается диалоговое окно «Создание функции». Доступно только на панели запросов в Редактор Power Query. Дополнительные сведения см. в Power Query функции M.
Преобразование в параметр Преобразует запрос в параметр и отображает его в предварительной версии данных. Если текущий дисплей является параметром, команда переключается на преобразование в запрос. Доступно только на панели запросов в Редактор Power Query. Дополнительные сведения см. в разделе «Создание запроса параметров Power Query».
Расширенный редактор открывает окно Расширенный редактор окна. Дополнительные сведения см. в статье «Создание Power Query в Excel». Доступно только на панели запросов в Редактор Power Query.
Экспорт файла подключения Сохраняет запрос в виде ODC-файла подключения. ODC-файл содержит определение запроса, сведения о подключении к источнику данных и все шаги преобразования. Это полезно, если вы хотите поделиться запросами с другими пользователями и книгами. Кроме того, можно использовать команду «Свойства» на вкладке «Контекст запроса» Power Query ленты. Дополнительные сведения см. в статье «Создание, изменение подключений к внешним данным и управление ими». Доступно только на вкладке « Запросы » & «Подключения «.
Показать окно просмотра Отображает всплывающее окно запроса с предварительным просмотром данных и другими полезными сведениями. Доступно только на вкладке « Запросы » & «Подключения «.
Управление наведением имени запроса во всплывающем окне запроса
Вы можете включить или отключить отображение всплывающего элемента запроса при наведении указателя мыши на имя запроса на вкладке «Запрос» на панели «Запросы & подключения». Этот параметр управляет отображением всплывающего элемента только путем наведения указателя мыши. Вы по-прежнему можете отобразить всплывающее окно запроса, щелкнув правой кнопкой мыши имя запроса, а затем выбрав команду «Показать».
- Выберите параметры >«Получение >данных».
- Слева в разделе GLOBAL выберите » Общие».
- Справа в разделе » Предварительный просмотр запроса» снимите флажок рядом с кнопкой «Показать окно при наведении указателя мыши». Действие по умолчанию — отображение всплывающего меню при наведении указателя мыши.
Excel для Интернета включает Power Query (также называемую get & Transform) для повышения производительности при работе с импортируемыми источниками данных. Запросы и связанные сведения можно просмотреть в области задач «Запросы».
Примечание: Следите за дальнейшими объявлениями об улучшениях Excel для Интернета и Power Query интеграции.
Просмотр области задач «Запросы»
Запросы и связанные сведения можно просмотреть на панели запросов.
- Убедитесь, что вы в режиме правки ( выберите «Просмотр >редактирование»).
- Выберите запросы >данных.
Excel отображает область задач « Запросы» и все запросы в текущей книге, а также сведения о запросах, такие как количество строк, дата последнего обновления, расположение и состояние загрузки. Если запрос загружается в модель данных, а не в сетку, в Excel отображается значение «Только подключение».
Управление запросами в группах
Вы можете выполнять различные задачи группирования, чтобы лучше упорядочить запросы.
- Убедитесь, что вы в режиме правки ( выберите «Просмотр >редактирование»).
- Выберите запросы >данных.
- Выполните одно или несколько из указанных ниже действий. Создание группы
- В правом верхнем углу области « Запросы» выберите «Дополнительные параметры» > «Создать группу».
- Введите имя (при необходимости описание) и нажмите кнопку » Сохранить».
Перемещение запроса или группы в другую группу
- Рядом с запросом или именем группы выберите «Дополнительные параметры» >«Переместить в группу».
- Выберите группу, в которую нужно переместить, а затем нажмите кнопку «Сохранить».
Перемещение группы на верхний уровень
- Рядом с именем группы выберите «Дополнительные параметры» >«Переместить на верхний уровень».
Развертывание или свертывание групп
- Чтобы развернуть или свернуть все группы, в правом верхнем углу области «Запросы» выберите «Дополнительные параметры» > «Развернуть все» или «Свернуть все».
- Чтобы развернуть или свернуть определенную группу, щелкните стрелку рядом с ее именем.
Обновление всех запросов в группе
- Рядом с именем группы выберите » Дополнительные параметры» >«Обновить». Примечание Обновление работает только для поддерживаемых источников данных. Дополнительные сведения см. в Power Query источниках данных в версиях Excel.
Разгруппировка запросов в группе
- Рядом с именем группы выберите » Дополнительные параметры. >группировать«. Примечание Это действие удаляет группу, но не запросы в этой группе.
Переименование группы
- Рядом с именем группы выберите » Дополнительные параметры». >переименовать.
- Введите новое имя и нажмите клавишу RETURN.
Удаление группы
- Рядом с именем группы выберите » Дополнительные параметры. >удалить группу».
Изменение свойств группы
- Рядом с именем группы выберите «Дополнительные параметры. >«.
- Переименуйте группу или измените ее описание, а затем нажмите кнопку «Сохранить».
Совет Чтобы просмотреть описание группы, наведите на нее указатель мыши.
Power Query: простая работа с данными
Что это? Power Query – это бесплатная надстройка для продуктов «Майкрософт», которая значительно облегчает работу с данными: сбор, подготовку, преобразование. Ее не нужно скачивать и как-то специально настраивать.
Как использовать? Интерфейс PQ интуитивно понятен, разобраться в нём не составит труда. Чтобы понять, на что способна эта надстройка, и получить базовые навыки работы в ней, читайте наш материал.
В статье рассказывается:
- Возможности Power Query
- Подключение к данным в Power Query
- Предварительная настройка Power Query
- Редактор запросов Power Query
- Преобразование данных в Power Query
- Строка формул и расширенный редактор
- Типы данных Power Query
- Импорт данных из Power Query
- Использование Power Query в продуктах и службах «Майкрософт»
Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.
Бесплатно от GeekbrainsВозможности Power Query
В статьях о MS Excel в последнее время часто встречаются малопонятные термины, начинающиеся со слова Power. Из контекста этих материалов не всегда получается понять, что подразумевается под Power Query, Power Pivot, Power BI и т. п. Самое главное — неясно, каким образом эти понятия связаны между собой и какую пользу они приносят простому пользователю табличного редактора.
Между тем еще в 2013 году в Microsoft была создана специальная группа разработчиков с целью выпуска бесплатной надстройки для Excel. Разработка получила несколько названий: Power Query, Data Explorer, Get&Transform. Данная надстройка служит для выполнения множества повседневных задач:
- Загрузка данных в таблицу Excel
Экспортировать данные можно из почти 40 сторонних форматов: SQL, Oracle, MS Access, Teradata, целый ряд корпоративных систем ERP (SAP, Microsoft Dynamics, 1C и др.), а также источники из интернета (почти все сайты, сервисы Facebook и Google Analytics).
- Сбор информации из файлов всех основных типов данных
Среди поддерживаемых типов — XLSX, TXT, CSV, JSON, HTML, XML. При этом возможно групповое извлечение данных сразу из нескольких файлов, находящихся в одной папке. Доступна автоматическая загрузка информации из всех листов Excel.
Узнай, какие ИТ — профессии
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Павел Симонов
Исполнительный директор GeekbrainsКоманда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.
Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!
Скачивайте и используйте уже сегодня:
Павел Симонов
Исполнительный директор GeekbrainsТоп-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка
Только проверенные нейросети с доступом из России и свободным использованием
ТОП-100 площадок для поиска работы от GeekBrains
Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽
Получить подборку бесплатно
Уже скачали 24058- Очищение полученных данных от лишних элементов
Надстройка удаляет в частности пустые столбцы и строки, лишние пробелы, непечатаемые символы, различную служебную информацию, а также повторяющиеся значения в ячейках.
- Приведение данных к нужному виду
С помощью PQ можно менять регистр на правильный, приводить цифры к числовому формату, заполнять пробелы, исправлять заголовки таблиц, разделять текстовые фрагменты на столбцы и склеивать их снова в единый текст, выполнять некоторые другие полезные операции.
- Приведение таблицы к нужному виду
Среди возможных операций над таблицами — фильтрация, сортировка и смена порядка столбцов, транспонирование значений, генерация итогов, трансформация кросс-таблиц в обычный плоский формат и обратно.
- Подстановка значений из одной таблицы в другую
Эта операция выполняется по совпадению одного или нескольких параметров. Таким образом, она является полной заменой встроенной в Excel функции ВПР (VLOOKUP) и ее аналогов.
Надстройка представлена в двух вариантах: как отдельный модуль, доступный для скачивания с официального сайта Microsoft (для Excel 2010-13), и как сервисная функция в составе редактора (для Excel 2016). В первом варианте установка PQ добавляет в таблицу новую вкладку. Во втором же случае весь функционал надстройки уже имеется в табличном редакторе по умолчанию — он доступен как группа Get & Transform (Получить и преобразовать) во вкладке Data (Данные).
Оба варианта имеют абсолютно одинаковые возможности.
Power Query обладает отличительной особенностью: все совершенные операции сохраняются как запрос из совокупности шагов, представленный на внутреннем языке программирования M. Каждый шаг может редактироваться и повторяться неограниченное число раз. При этом происходит обновление запроса.
Многие пользователи считают эту надстройку самой полезной в Excel. С ее помощью значительно облегчается выполнение многих рутинных задач. В прежние времена для автоматизации действий писались сложные макросы, теперь же это легко выполняется в PQ. К тому же этот инструмент абсолютно бесплатен, что делает его лидером среди возможных конкурентов. Сегодня он должен иметься в арсенале каждого пользователя Excel, обладающего навыками работы выше среднего.
Подключение к данным в Power Query
Как упоминалось ранее, подключаться могут данные из большого количества различных источников. Это и другие Excel-таблицы, и базы данных популярных форматов, и данные из OData, и даже документы PDF. Также доступна загрузка информации из Power BI с последующим написанием запроса с нуля.
Как получить полный список источников, с которыми может работать Power Query? Excel позволяет увидеть в разделе «Получить данные» («Создать запрос»), находящемся во вкладке «Данные».
Разберем этот процесс на примере переноса значений из обычной таблицы.
Сперва необходимо выделить таблицу и перейти в меню «Из таблицы». В разных версиях редактора это делается по-разному:
- Excel 2010-13 — через вкладку «Power Query».
- Excel 2016 и выше — через меню Данные.
Откроется окно, в котором нужно поставить галочку «Таблица с заголовками».
После этого произойдет преобразование обычной таблицы в «умную». Останется перейти во вкладку «Главная» и нажать «Закрыть и загрузить».
Далее для данного файла полезно посмотреть запросы Power Query. Списки их доступны во вкладке «Данные» в разделе «Запросы и подключения».
Предварительная настройка Power Query
Редактор запросов в Excel открывается следующим образом:
- Вкладка «Данные» — раздел «Получить и преобразовать данные» — пункт «Получить данные» — «Запустить редактор запросов»
Настройка Power Query
В данной статье описан порядок подключения надстройки Power Query и необходимые действия по ее настройке.
Разбор возможных ошибок при обновлении запросов и порядок их устранения см. в разделе «Возможные ошибки» статей к соответствующим кнопкам:
1. На компьютере пользователя должна быть установлена надстройка для Microsoft Excel – Power Query версии не ниже 2.49 (доступна в качестве отдельной надстройки в Microsoft Excel 2013, скачать которую можно на сайте Microsoft; в версиях 2016 и 2019 присутствует в качестве встроенной функции на вкладке «Данные»).
2. Для корректного обновления запросов установите соответствующие требования к уровням конфиденциальности Power Query в файле.
a. Для Microsoft Excel 2013
Перейдите на ленте на вкладку Power Query -> Настройки -> Параметры: 
b. Для Microsoft Excel 2016/2019
Перейдите на вкладку Данные -> откройте выпадающий список под кнопкой Получить данные -> Параметры запроса: 
c. В появившемся окне в разделе Глобальные откройте пункт меню Конфиденциальность и выберите опцию « Всегда игнорировать параметры уровней конфиденциальности »:
3. Проверьте, разрешены ли подключения к внешним данным.
Чтобы разрешить подключения к внешним данным, перейдите на вкладку Файл -> Параметры -> Центр управления безопасностью -> Параметры центра управления безопасностью , а затем в группе Параметры безопасности для подключения к данным выберите опцию « включить все подключения к данным », в группе Параметры безопасности для связей в книге — « включить автоматическое обновление для всех связей в книге ».