Excel power query руководство

Иллюстрация: Meery Mary для Skillbox Media

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

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

Если в Excel без надстроек можно обрабатывать таблицы только из 1 048 576 строк, то в Power Query количество строк не ограничено. А в отличие от Power Pivot, похожей надстройки Excel, Power Query поддерживает гораздо больше форматов источников. Также в нём больше функций для редактирования данных.

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

• Что умеет надстройка Power Query
• Как включить Power Query в Excel
• Как загрузить и преобразовать данные
• Как импортировать данные из Power Query
• Как узнать больше о работе в Excel

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

Разберём каждый этап подробнее.

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

Например, из Power Query можно подключиться к файлам XLS, TXT, PDF, CSV, JSON, HTML, XML. Также можно выгрузить информацию из разных баз данных — например, MS Access и MS SQL Server; из систем ERP, программ «1C», облачных хранилищ, Google Analytics, «Яндекс Метрики» и других сервисов.

При этом можно одновременно получать данные нескольких источников — например, всех файлов, лежащих в одной папке, или всех листов файла Excel.

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

• менять тип данных — например, изменить числовой формат на формат даты, чтобы день и месяц отображались корректно;
• менять регистр букв — это будет полезно, например, когда нужно в тысячах строк заменить строчные буквы на прописные;
• очистить данные от лишних элементов — например, удалить лишние пробелы, пустые столбцы и строки, повторяющиеся значения ячеек;
• сортировать и фильтровать данные, изменять порядок столбцов и строк;
• разделять единый текст на столбцы или, наоборот, объединять столбцы в единый текст;
• проводить необходимые расчёты — например, суммировать данные или рассчитывать процент;
• подставлять значения из одной таблицы в другую — как с помощью функции ВПР (Vlookup) в Excel.

При этом данные будут форматироваться только в редакторе Power Query — в файлах-источниках они останутся без изменений.

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

Импортировать данные можно тремя способами:

• Выгрузить на лист Excel — тогда на этом листе появится смарт-таблица с данными из Power Query.
• Создать сводную таблицу или сводную диаграмму.
• Добавить данные Power Pivot и построить из них модель данных.

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

Напомним, одно из главных преимуществ Power Query в том, что в нём нет ограничений по объёму данных, с которыми он может работать без потери производительности. Excel без надстроек позволяет работать только с 1 048 576 строками.

В следующих разделах расскажем, где найти Power Query в Excel, и разберём на примере, как с ним работать.

Power Query — бесплатная надстройка Excel. Она доступна для всех версий программы, начиная с 2010 года.

Версии Excel 2010 и 2013 года. Power Query нужно скачивать отдельно — например, с сайта Microsoft.

После установки надстройка отобразится в виде отдельной вкладки Power Query на главной панели Excel. Нужно открыть эту вкладку и нажать на кнопку «Из файла».

Версии Excel 2016 года и новее. Power Query скачивать отдельно не нужно — надстройка есть в Excel по умолчанию. Чтобы её запустить в Excel 2016 года, нужно на вкладке «Данные» выбрать раздел «Скачать и преобразовать». Затем нажать кнопку «Создать запрос».

В Excel 2019 года — на вкладке «Данные» выбрать раздел «Получить и преобразовать данные» и нажать на кнопку «Получить данные».

Дальше нужно выбрать, откуда выгружать данные, преобразовать их в редакторе Power Query и импортировать дальше. Как это делать — разбираем на примере в следующих разделах.

Для примера выгрузим в Power Query справочник товаров книжного магазина в формате XLS. В нём перечислены названия книг, их формат и ID‑номера.

На вкладке Excel «Данные» в разделе «Получить и преобразовать данные» нажимаем кнопку «Получить данные».

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

В появившемся окне выбираем наш XLS-файл для выгрузки и нажимаем «Открыть».

Появляется окно навигатора. В нём выбираем, какую таблицу из XLS-файла нужно выгрузить в Power Query. Можно выгрузить все листы файла или какие-то конкретные.

В нашем примере выберем первый лист — «Таблица 1» — и нажмём «Преобразовать данные».

Готово — Excel открывает окно редактора Power Query, в котором мы сможем обработать данные, полученные из справочника товаров.

В открывшемся редакторе мы можем внести изменения в выгруженную таблицу.

В нашем случае нужно:

• изменить формат данных в столбце «ID-товара»;
• изменить регистр букв в столбце «Формат»;
• удалить лишние пробелы в столбце «Книга».

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

Подробнее о форматах ячеек в Excel рассказывали в этой статье Skillbox Media.

Чтобы поменять формат в Power Query, нажимаем на значок «123» слева от названия столбца и выбираем нужный формат — «Текст».

Готово — теперь ID товаров отображаются корректно.

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

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

Готово — теперь все слова столбца «Формат» начинаются с прописной буквы.

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

Готово — теперь во всех ячейках столбца нет пустых участков перед текстом.

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

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

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

Покажем, как сделать последнее.

Нажмём кнопку «Закрыть и загрузить» в левой части верхнего меню, затем — «Закрыть и загрузить в…».

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

Готово — мы сохранили преобразованную ранее таблицу и импортировали её в Power Pivot.

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

• В Excel много функций, которые упрощают и ускоряют работу с таблицами. В подборке даём ссылки на 15 статей и видео об инструментах Excel, необходимых в повседневной работе.
• В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
• Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.

Как зарабатывать больше с помощью нейросетей?
Бесплатный вебинар: 15 экспертов, 7 топ-нейросетей. Научитесь использовать ИИ в своей работе и увеличьте доход.
Узнать больше

На этой странице находятся ссылки на все уроки моего бесплатного курса по Power Query для Excel. Если вы изучите все уроки, то станете очень продвинутым пользователем Power Query. Желаю вам успехов в освоении Power Query!

Внимание!!! Старица находится в процессе разработки.

Введение

Во вводном модуле вы узнаете:

• Зачем нужен Power Query
• К каким источникам данных может подключаться Power Query
• Как можно использовать результирующие таблицы
• Базовые преобразования данных
• Классификация функций Power Query

Урок	Описание
Знакомство с Power Query
Зачем нужен Power Query. Обзор возможностей

Основы

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

Урок	Описание
Подключение Excel	Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов.
Подключение CSV/TXT, таблиц, диапазонов	Подключаемся к к файлам CSV/TXT, Excel
Подключение XML	Подключаемся к источнику в формате XML
Объединение таблиц по горизонтали	Учимся объединять таблицы по горизонтали — JOIN, merge
Виды объединения таблиц по горизонтали	Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN
Виды объединения таблиц по горизонтали 2	Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN
Группировка	Изучаем операцию группировки с агрегированием — GROUP BY
Объединить таблицы с агрегированием	Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY
Объединить таблицы по вертикали	Учимся объединять две таблицы по вертикали — combine
Объединение таблиц по вертикали, когда не совпадают заголовки столбцов	Как объединить две таблицы по вертикали, если названия столбцов не совпадают
Объединить по вертикали все таблицы одной книги друг за другом	Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel
Объединить по вертикали все файлы в папке	Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке
Консолидация множества таблиц пользовательской функцией	Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции
Анпивот (Unpivot)	Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными
Многоуровневый анпивот (Анпивот с подкатегориями)	Более сложный вариант Анпивота — в строках находится несколько измерений
Скученные данные	Данные собраны в одном столбце, нужно правильно его разбить на несколько
Скученные данные 2	Разбираем еще один пример скученных данных
Создание параметра	Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query
Таблица параметров	Создадим целую таблицу параметров и будем их использовать в запросах Power Query
Поиск ключевых слов	Научимся искать ключевые слова в текстовом поле
Поиск ключевых слов 2	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию
Ссылка на другую строку	Как сослаться на другую строку
Ссылка на другую строку 2	Как сослаться на другую строку, используя объединение по горизонтали
Уникальные значения двух столбцов	Как получить уникальные значения из двух столбцов
Деление на справочник и факт	Разделим один датасет на два датасета: справочник и факт
Импорт из PDF, Импорт из множества PDF	Научимся импортировать таблицы из одного PDF файла и из всех PDF файлов в папке

Формулы М

В этом модуле мы будем изучать язык формул Power Query или M Language.

Урок	Описание
Введение 1	На примере поиска без учета регистра я показываю преимущества изучения формул Power Query
Введение 2	Знакомимся с основами языка формул М: ключевые слова let, in, разбираем как устроены функции
Введение 3	Зачем изучать язык формул, когда существует удобный пользовательский интерфейс
Объекты Power Query	Знакомимся с основными объектами Power Query: Table, List, Record
Объект Table	Изучаем объект Table — таблица
Объект Record	Изучаем объект Record — запись, каждая строка таблицы — это запись
Объект List 1	Изучаем объект List — список, каждый столбец таблицы является списком
Объект List 2	Продолжаем изучать объект List — список
Ссылки 1	Как ссылаться на список и запись и отдельные элементы списка или записи
Ссылки 2	Решаем практическую задачу по объединению таблиц и отрабатываем ссылки на строки и столбцы таблицы
Ссылки 3	В процессе решения практической задачи отрабатываем ссылки на элементы записи и таблицы
Ссылки 4	Закрепляем навык делать ссылки на практическом примере из жизни
Работа с датой, временем и длительностью, #datetime, #duration	Основы работы с датой и временем
Power Query аналоги текстовых функций Excel	Изучаем аналоги текстовых Excel функций ЛЕВСИМВ, ПРАВСИМВ, ДЛСТР, НАЙТИ, ПСТР
Группировка, Пивот, Анпивот, Сортировка	Выполняем знакомые операции, но уже без помощи пользовательского интерфейса, а ручным вводом формул
Вычисления в Power Query	Вычисляем процент от общей суммы, процентное изменение относительно предыдущего дня, создаем рейтинг
Пользовательские функции	Учимся создавать пользовательские функции на примере нарастающего итога
Агрегирование текста, группировка	Выполняем группировку с агрегированием текстовых значений
Функция Table.TransformColumns	Table.TransformColumns позволяет нам применить любую функцию преобразования к столбцу
Скользящее среднее в Power Query, List.Range	Изучаем функцию List.Range на примере вычисления скользящего среднего
Разгруппировка, скрытые возможности Table.ReplaceValues	Разгруппировка строки — разбить число на N равных частей; Вводим функцию внутри Table.ReplaceValues
Нюансы консолидации и List.PositionOf	Нужно выполнить объединение по вертикали с предварительной обработкой, но количество лишних строк сверху всегда разное
Удалить лишние пробелы в текстовом столбце, Text.Split	Изучим функцию Text.Split и удалим с помощью нее лишние пробелы
Обработка заголовков в двух строках, Функция List.Zip, Практика List.x, Record.x	Каждый заголовок разбит на две строки, нужно превратить эти две строки в одну объединенную
Фильтрация таблицы списком	Есть список интересующих нас категорий, нужно из таблицы выбрать только эти значения
Разделить столбец на строки, Расширенные возможности Text.Trim	Разберем на практическом примере, что еще может чистить функция Text.Trim помимо лишних пробелов по краям
Нарастающий итог 2	Вычислим нарастающий итог функциями List.Range и List.Accumulate
Создание пользовательской функции Switch	Повторим пользовательские функции на примере создания функции Switch
Информация о форматах ячеек, Чтение Zip файла	Распакуем XLSX файл и получим данные о формате ячеек
Множественная текстовая замена с List.Generate	Ищем определенный перечень текстовых фрагментов и заменяем на другой.
Минимум в диапазоне строк

Ошибки

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

Урок	Описание
Введение	Определимся, что мы будем назвать ошибками и ловушками
Как развернуть все столбцы	В данные добавился новый столбец, но у вас он не разворачивается, потому что вы хардкодом прописали список для экстракта
Измененный тип, Неверная фильтрация в UI	Запрос, который раньше работал, вдруг, работать перестал. Возможно, дело в ошибке «Измененный тип»; В результирующей таблице меньше строк, чем должно быть. Возможно, вы попали в ловушку неверной фильтрации
Неверное количество столбцов при импорте CSV	Вы импортировали CSV файл, все было хорошо, но когда в источник добавились новые столбцы вы их не увидели в Power Query
Подключиться к последнему файлу	Каждую неделю в сетевую папку для вас добавляют новый файл источник. Вам нужно автоматически подключаться только к самому свежему файлу
Консолидация и MissingField.Type	Вы хотите выполнить массовое объединение таблиц по вертикали, но не во всех таблицах присутствует полный список столбцов. Что делать?
Автоматически удалить пустые столбцы	В вашем источнике часто присутствуют лишние пустые столбцы; Научимся автоматически удалять их
Ошибка Formula.Firewall	Разберем 2 способа избежать ошибки Formula.Firewall
Разные имена листов
Разные имена листов 2
Разные названия столбцов
Удалить лишние пробелы 2	Удалим лишние пробелы в текстовых столбцах с помощью функцию Text.SplitAny и Text.Combine
Доступ к веб-содержимому	При подключении к веб-страницам постоянно появляется окно «Доступ к веб-содержимому»; Как от этого окна избавиться?
Импорт плохо структурированного TXT	TXT источник плохо структурирован и столбцы не распознаются автоматически
Не совпадает сумма при округлении	Выполнив округление общая сумма перестала совпадать на незначительную величину
Фиксировать ширину столбцов Excel	Настроенная вами ширина столбцов Excel сбивается после каждого обновления запроса.

Веб-запросы

Разберем множество практических примеров подключения к веб-страницам и web api.

Урок	Описание
Веб-запросы. Текстовый документ, веб-страница, практика List.Zip	На одной веб-странице находятся данные без заголовков, а заголовки для нее на другой странице.
Веб-запросы. Неразмеченный текст	Текс находится на веб-странице и он плохо размечен. Нужно преобразовать его в таблицу.
Веб-запросы. Многостраничное извлечение	Как извлечь таблицу из множества веб-страниц и объединить их всех по вертикали.
Веб-запросы. Получение котировок Yahoo Finance	Получим историю котировок любой акции с сайта Yahoo Finance.
Веб-запросы. JSON, получение котировок Yahoo Finance 2	Получим историю котировок любой акции при помощи API Yahoo Finance.
Веб-запросы. HTML, получить данные по облигациям	Получим данные по облигациям из множества веб-страниц и все объединим в одну таблицу.
Получить котировки Московской биржи	Получим историю котировок с сайта Московской биржи.
YouTube Data API	Получить статистику по любому YouTube видео и каналу прямо на лист Excel.
Подключение к личному OneDrive	Как подключиться к файлу или папке из личного OneDrive.
Подключиться к книге на Google Drive	Как подключиться к файлу, который лежит в облаке Google Drive

Практика

Отработаем изученные функции на реальных практических примерах из жизни.

Урок	Описание
Динамическая таблица дат	Создаем саморасширяющуюся таблицу календаря
Продажи год назад	Отработаем SELF JOIN на примере вычисления продаж прошлого года
Количество позиций в текстовой строке	В ячейках столбца находится перечень товаров в чеке через точку с запятой; Сделаем так, чтобы каждая позиция находилась в своей отдельной строке
Консолидация листов и книг одновременно	Объединим по вертикали все таблицы, находящиеся на разных листах разных книг Excel
Собрать разбитую строку	Каждая строка данных разбита на несколько строк. Нужно привести данные в порядок.
Обработка типичной выписки	Отработаем несколько приемов, обработав банковскую выписку.
Прирост населения Китая	На примере анализа прироста населения Китая отработаем несколько приемов.
Нужные столбцы нужной книги	Как выбрать нужную книгу среди множества и из этой книги получить только определенные столбцы.
Объединить разбитую строку	Ряды данных разбиты на части. Нужно правильно соединить ряды обратно.
Группировка, пивот, анпивот, условия	Практическое применение группировки, пивота, анпивота и условной логики
Процент от суммы по категории в Power Query и в Power Pivot	Учимся вычислять процент от общей суммы в Power Query и в Power Pivot.
Выполнить пивот и пронумеровать столбцы	Делаем пивот и столбцы автоматически пронумеровываем.
Интересная консолидация
Преобразовать строки в столбцы
Повторяющиеся значения в строке
Нарастающий итог 3
Минимальное значение в диапазоне строк
Нарастающий итог 4. Группировки
Функция List.Contains и создание пользовательской функции
Скученные данные 3

Power Query + VBA

Урок	Описание
Указать путь к файлу при помощи VBA	Укажем путь к файлу, выбрав его в диалоговом окне.
Массовая обработка файлов	По одному обработаем файлы из перечня.
Обновить запросы выборочно	С помощью VBA обновить только определенные Power Query запросы в книге Excel.

Трюки и советы

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

Урок	Описание
Таблица навигации по функциям Power Query	Создадим таблицу, в которой будет полный перечень функций Power Query с категориями, описаниями и примерами.
Удалить все запросы и модель данных из книги	Вы узнаете как моментально удалить все запросы и модель данных из книги за несколько кликов.
Открыть еще 1 Excel и еще 3 трюка	В этом уроке мы разберем как открыть еще одно окно Excel, когда запущен редактор Power Query, как скопировать запросы из одной книги в другую, как быстро удалить шаги запроса от выбранного и до последнего и как быстро перейти к нужному столбцу в редакторе Power Query
Подключение к ZIP файлу	Подключимся к файлу-источнику, который находится внутри ZIP архива
Подключение к WORD документу	Импортируем таблицы из MS Word документа

Погружение в M

В этом модуле мы углубимся в язык М.

Урок	Описание
Простые выражения и let	Что такое let, одна или несколько переменных в in, вложенные блоки let.
Создание функций М	Функции без выражения let, функции внутри выражения let
Создание функций М 2	Функция как параметр функции, ключевое слово each
Функции и идентификаторы	Идентификаторы с кавычками и без, допустимые и недопустимые идентификаторы
Текстовый тип данных	Что из себя представляет текстовый тип данных в Power Query.

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

Раскройте весь потенциал шаблонов с помощью обучающих ресурсов

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

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

Количество просмотров 200K

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

Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать

повторяемые последовательности обработки

этих данных и загружать их внутрь таблиц Excel или самого data model.

И вот под катом вы можете найти подробности всего этого великолепия возможностей.

Совместимость и технические подробности

Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).

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

Как оно работает

После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.

В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.

Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.

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

У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.

Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.

Объединение файлов лежащих в папке

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

Текстовые функции

К столбцам из текста в Power Query по нажатию на кнопки на Ribbon можно применять такие функции как:

1. Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
2. Изменить регистр ячеек в столбце
3. Подсчитать количество символов в ячейках столбца.

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

1. Арифметические операции
2. Возводить в степени, вычислять логарифмы, факториалы, корни
3. Тригонометрические операции
4. Округлять до заданных значений
5. Определять четность и т.д.

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

1. Автоматическое определение формата вписанной даты (в excel c этим большая боль)
2. Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.

Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.

Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

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

Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.

А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.

Язык программирования “М”

Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.

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

Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:

Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.

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

Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.

Еще раз про повторяемость и про варианты применения

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

Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.

Вот часть задач, которые я делаю с использованием Power Query:

1. разбираю семантику для Толстых проектов,
2. Делаю частотные словари,
3. Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
4. Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
5. Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
6. Делаю аудит контекстной рекламы на данных из K50 статистика,
7. И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

Ссылки по теме

Официальные ресурсы на английском:

1. Скачать Английскую версию Power Query
2. Справка по формулам языка M
3. Ветка Форума Microsoft Technet про Power Query

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

1. Импорт данных из таблиц в Google Spreadsheets
2. Power BI. Получаем данные из REST API
3. Сообщество продвинутых пользователей Excel, Power Pivot и Power Query в Facebook
4. Обновляемая страничка в Marketing-wiki про Power Query

На английском:

1. Видео с демонстрацией возможностей Power Query на конференции TechEd North America
2. Блог Chris Webb, пожалуй наиболее полный ресурс с примерами использования Power Query
3. Книга Chris Webb — Power Query for Power BI and Excel
4. Блог Ken Puls
5. Курс «Excel для интернет-маркетинга»
6. Курс «Power BI для интернет-маркетинга»
7. Книга Ken Puls и Miguel Escobar — M is for Data Monkey

КУРС

ОСНОВЫ DATA SCIENCE

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

Power Query в Экселе — инструмент бизнес-аналитики, созданный компанией Microsoft. Позволяет извлекать, преобразовывать и загружать данные из различных источников, таких как БД, электронные таблицы, текстовые файлы, веб-страницы и другие. Его можно использовать в Excel, Power BI и других приложениях Майкрософт.

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

Как установить

Много дискуссий касается того, как установить Power Query. В новых версиях Эксель он уже доступен. Если на ПК / ноутбуке стоит Excel 2010, придется сначала загрузить и установить дополнительный пакет.

Алгоритм действий такой:

1. Перейдите на страницу загрузки Power Query на сайте Microsoft по ссылке https://www.microsoft.com/ru-ru/download/details.aspx?id=39379.
2. Жмите на кнопку «Скачать».
3. Выберите оптимальную версию для операционной системы и следуйте инструкциям на экране.

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

Как открыть

Для начала работы стоит разобраться, как открыть Power Query и начать с ним работу. В ситуации с Excel сделайте следующие шаги:

1. Запустите Эксель.
2. Зайдите во вкладку «Данные» в верхней части экрана.
3. Кликните на клавишу «Получение внешних данных» и «Из других источников» с левой стороны.
4. Выберите тип источника данных, с которым вы планируете работать: файл Excel, БД SQL, текстовый файл и т. д.
5. Если требуется, введите информацию о подключении: имя сервера и учетные данные.

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

1. Выберите столбец в таблице и жмите правой кнопкой мыши на нем. Затем выберите «Добавить столбец» и выберите «Столбец с формулой» или «Использовать Power Query».
2. Нажмите на кнопку «Power Query» на вкладке «Данные» и выберите «Из других источников» (если такая опция имеется).

Как пользоваться

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

•  «Добавить столбец» — создание новых элементов на основе существующих;
• «Заменить значение» — установка нового параметра;
• «Группировать и агрегировать» — группировка информации и вычисление требуемого параметра;
• «Изменить тип данных» — внесение правок в столбец;
• «Объединение запросов» — преобразование и объединение информации из разных источников в одну таблицу;
• «Фильтрация строк» — применение фильтра на базе определенного условия.

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

КУРС

ОСНОВЫ DATA SCIENCE

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

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

Для получения 100% возможностей программы рекомендуется использовать следующие опции:

• импорт информации через вкладку «Данные» и выбора «Из других источников» или «Из веба»;
• преобразование и проведение разных операций с данными, таких как фильтрация, объединение таблиц, удаление дубликатов и т. д;
• очистка данных, например, удаление строк с пустыми значениями, уничтожение дубликатов, замена значений;
• объединение информации из разных источников в одну таблицу;
• группировка с помощью функции «Группировать и агрегировать»;
• добавление пользовательских функций и скриптов на языке М Power Query;
• загрузка для импортирования в Excel.

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

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

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

• «Добавить столбец» для создания нового столбца на основе существующих сведений;
• «Изменить тип данных» для замены информации с числовой на текстовую;
• «Фильтрация строк» для отбора строк, удовлетворяющих определенному условию;
• «Объединение запросов» для соединения данных из разных источников в одну таблицу (к примеру, использовать Power Query для SQL и других файлов);
• «Группировать и агрегировать» для группировки данных и вычисления агрегированных значений: сумма, среднее значение или количество;
• Очистка данных — удаление строк с пустыми значениями и дубликатов, замена значений и прочие действия;
• Использование пользовательских функций и скриптов на языке M для решения более сложных задач.

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

Строка формул и расширенный редактор

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

Расширенный редактор позволяет создавать более сложные скрипты и пользовательские функции на языке M. Он предоставляет больше функций и возможностей, чем строка формул. Доступен расширенный редактор для создания пользовательских опций.

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

Типы данных Power Query

Рассматриваемый инструмент поддерживает множество типов данных, включая следующие:

1. Текстовый (Text). Используется для хранения текстовых значений, таких как имена, адреса, описания.
2. Числовой (Number). Применятся для числовых значений, таких как целые числа, десятичные дроби и прочие.
3. Дата/Время (Date/Time). Этот тип данных включает даты, времена и даты со временем.
4. Логический (Logical). Касается таких значений, как «истина» или «ложь».
5. Валюта (Currency). Используется для хранения валютных значений, таких как суммы денег.
6. Процент (Percentage). Необходим для хранения процентных значений.
7. Длительность (Duration). Применятся для сохранения продолжительности времени, например, длительности фильмов.
8. Бинарный (Binary). Касается изображений или звуковых файлов.
9. Рекорд (Record). Используется для хранения групп связанных значений: информации о клиенте.
10. Список (List). Касается коллекции значений, например, списка продуктов в заказе.

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

КУРС

ОСНОВЫ DATA SCIENCE

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

О курсе “Power BI и Power Query: аналитика и визуализация данных”

Power BI и Power Query – обучающий курс по работе с Power BI и Power Query. На курсе студенты обучаются формировать, обрабатывать и визуализировать бизнес-данные,…

Tableau vs Power BI

Статья доступна в аудиоверсии Как же приятно осознавать, что мы не только обучаем Вас, дорогие студенты, но и развиваемся сами! Мы запустили новый курс…