Power query windows 10

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

Картинка с сайта: skillbox.ru

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в 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 года — на вкладке «Данные» выбрать раздел «Получить и преобразовать данные» и нажать на кнопку «Получить данные».

Картинка с сайта: skillbox.ru

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

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

Картинка с сайта: skillbox.ru

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

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

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

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

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

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

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

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

Картинка с сайта: skillbox.ru

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

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

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

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

Картинка с сайта: skillbox.ru

Бесплатный курс: «Excel и „Google Таблицы“ для всех»
Начать учиться

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

Он автоматизирует процессы ETL (Extract, Transform, Load) — извлечение, трансформацию и загрузку данных из различных источников. Это позволяет обойтись без ручной обработки данных и повторения однотипных действий.

Основные функции Power Query

У Power Query широкий набор возможностей для автоматизации работы с данными. Он позволяет:

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

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

Если вы используете MS Excel 2016 и новее, то Power Query уже встроен в редактор и доступен через вкладку «Данные».

Картинка с сайта: blog.skillfactory.ru

Для MS Excel 2010 и 2013 его нужно скачать и установить.

1. Определите тип своей системы: 32-разрядная (x86) или 64-разрядная (x64).
2. Скачайте соответствующий файл.

Картинка с сайта: blog.skillfactory.ru

1. Запустите скачанный установщик MSI и следуйте инструкции.
2. После установки в Excel появится вкладка Power Query. Например, так это выглядит в MS Excel 2010.

Картинка с сайта: blog.skillfactory.ru

1. Power Query готов к использованию.

Далее в примерах будем работать в MS Excel 2019.

Как подключиться к источникам данных

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

1. Во вкладке «Данные» переходим в раздел «Получить и преобразовать данные».
2. Нажимаем на кнопку «Получить данные».
3. Здесь можно выбрать источник из основных категорий:

Файл: Excel, текстовые файлы, CSV, XML, JSON.

База данных: SQL Server, Access, Oracle, PostgreSQL.

Веб-службы: SharePoint, Dynamics 365, веб-API.

Azure (облачная платформа): база данных, хранилище таблиц.

Другие источники: OData, ODBC, веб-страницы.

Картинка с сайта: blog.skillfactory.ru

Доступные типы данных 

Power Query поддерживает следующие форматы данных для извлечения:

Как работать в редакторе Power Query

Извлечение

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

• «Получить данные»
• «Из файла»
• «Из книги»

Откроется «Навигатор». Выберем таблицу, которую хотим загрузить. Когда файл Excel содержит несколько листов, то можно загрузить только выбранные.

Картинка с сайта: blog.skillfactory.ru

Нажимаем на опцию загрузки «Загрузить в…». Откроется окно импорта данных.

Картинка с сайта: blog.skillfactory.ru

В этом окне нужно выбрать:

— Способы представления данных

• Таблица

Стандартный способ отображения данных. Загружает данные в виде таблицы.

• Отчет сводной таблицы

Создает новую сводную таблицу на основе загруженных данных.

• Сводная диаграмма

Создает сводную диаграмму на основе загруженных данных.

• Только создать подключение

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

— Куда поместить данные

• Имеющийся лист

Позволяет выбрать существующий диапазон на листе, куда будут загружены данные. Так можно заменить или дополнить уже имеющиеся данные.

• Новый лист

Загружает данные в новый лист.

— Добавлять ли данные в модель данных

Если поставить галочку, то данные будут также загружены в модель данных Excel — ThisWorkbookDataModel. Это полезно, если вы хотите создать сводные таблицы или использовать DAX для анализа данных, а также объединять данные из различных источников.

После того как вы настроите загрузку и нажмете «ОК», загруженный элемент появится в панели «Запросы и подключения» во вкладке «Запросы». 

Картинка с сайта: blog.skillfactory.ru

Во вкладке «Подключения» будет список всех активных подключений к источникам данных, которые Excel использует для извлечения информации. Например, модель ThisWorkbookDataModel будет находиться там.

Картинка с сайта: blog.skillfactory.ru

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

Откроется редактор.

Картинка с сайта: blog.skillfactory.ru

Основные разделы редактора:

1. Панель редактора с инструментами и командами в различных вкладках.
2. Список запросов в текущей рабочей книге.
3. Строка формул на языке M, о котором расскажем ниже.
4. Предварительный просмотр данных.
5. Свойства с именем запроса и дополнительными параметрами «Все свойства».
6. Примененные шаги или история преобразования.

Преобразование

• Переименование столбцов

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

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

Картинка с сайта: blog.skillfactory.ru

• Сортировка данных по возрастанию и убыванию

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

Картинка с сайта: blog.skillfactory.ru

• Фильтрация

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

Чтобы отсортированные данные были корректными, нужно исключить (NULL) из фильтра.

Что касается языка M, то он используется для описания шагов. 

Картинка с сайта: blog.skillfactory.ru

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

= Table.SelectRows(#»Повышенные заголовки», each ([январь] <> null))

Где:

Table.SelectRows: функция, которая выбирает строки из таблицы на основе заданного условия. Она принимает два аргумента:

— первую часть (в данном случае таблицу, из которой будут выбраны строки);

— вторую часть (условие для выбора строк).

#»Повышенные заголовки»: ссылка на предыдущий шаг в запросе, который создает таблицу с повышенными заголовками (т. е. первая строка таблицы используется как заголовки колонок).

each ([январь] <> null): условие для фильтрации строк.

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

([январь] <> null) — это логическое выражение, которое проверяет, что значение в колонке «январь» не является null (т. е. что ячейка не пуста).

• Замена данных

В ячейках также можно заменять данные. 

Картинка с сайта: blog.skillfactory.ru

После нажатия на кнопку «Замена значений» откроется окно для ввода нового значения.

Картинка с сайта: blog.skillfactory.ru

• Создание новых столбцов

Во вкладке «Добавление столбца» есть способы добавления столбцов.

Картинка с сайта: blog.skillfactory.ru

Вы можете выбрать один из следующих вариантов:

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

Например, если у вас есть столбец с полными именами и вы хотите создать новый столбец только с именами, вы можете ввести несколько примеров, например «Иван», «Мария», «Алексей». Power Query проанализирует ваши примеры и автоматически извлечет имена из всех полных имен в исходном столбце.

«Настраиваемый столбец»: позволяет создать новый столбец на основе формул.

Например, у вас есть таблица с двумя столбцами: «Цена» и «Количество». Вы хотите создать новый столбец «Сумма», который будет вычислять общую стоимость.

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

[Цена] * [Количество]

После создания столбца «Сумма» в каждой строке будет отображаться результат умножения цены на количество.

«Столбец индекса»: добавляет индексный столбец, который будет содержать последовательные номера для каждой строки.

Например, у вас есть таблица с данными о продажах, и вы хотите добавить индексный столбец для упрощения сортировки или ссылки на строки.

После добавления столбца индекса ваша таблица может выглядеть так:

Индекс помогает быстро идентифицировать каждую строку в таблице.

«Условный столбец»: добавляет новый столбец на основе заданного условия.

Например, у вас есть столбец «Баллы». Вы хотите создать новый столбец «Статус», который будет содержать значение «Прошел» для 60 баллов и выше и «Не прошел» для остальных баллов.

Картинка с сайта: blog.skillfactory.ru

Условный столбец можно создать с помощью такого условия:

Если [Баллы] >= 60, то «Прошел», иначе «Не прошел»

• Остальные функции преобразования

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

Объединение данных

В Power Query можно соединять и объединять таблицы из различных источников, чтобы создать единый набор данных.

1. Загрузим две таблицы через редактор Power Query.

Картинка с сайта: blog.skillfactory.ru

Таблицы выглядят так:

Картинка с сайта: blog.skillfactory.ru

1. Во вкладке «Главная» есть кнопка «Объединить запросы». Нажимаем «Объединить запросы в новый».

Картинка с сайта: blog.skillfactory.ru

1. Откроется окно «Слияние». Выбираем таблицы, которые хотим объединить, и тип данных.

Картинка с сайта: blog.skillfactory.ru

В выпадающем списке вы можете выбрать разные типы объединения:

— Внешнее соединение слева вернет все строки из первой таблицы и соответствующие данные из второй (если они есть).

— Внешнее соединение справа вернет все строки из второй таблицы и соответствующие данные из первой.

— Полное внешнее соединение вернет все строки из обеих таблиц, включая те, которые отсутствуют в одной из таблиц.

— Внутреннее соединение вернет только те строки, где значение присутствует в обеих таблицах.

— Антисоединение слева выберет все строки из первой таблицы, которые не имеют соответствующих данных во второй таблице.

— Антисоединение справа выберет все строки из второй таблицы, которые не имеют соответствующих данных в первой таблице.

1. В качестве примера выберем внутреннее соединение и выделим столбцы, по которым будем искать совпадения.

Картинка с сайта: blog.skillfactory.ru

Получим результат слияния.

Картинка с сайта: blog.skillfactory.ru

1. Нажмите «Закрыть и загрузить». Объединенная таблица появится на новом листе «Слияние1».

Автоматизация

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

После изменений в источнике данных достаточно просто нажать на кнопку «Обновить» на элементах в «Запросах и подключениях».

Если изменения произошли в таблице «Лист1», то обновляем ее и зависимую от нее таблицу — «Слияние1». 

Работа с типами данных

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

Во вкладке «Главная» редактора есть кнопка «Тип данных». Если тип указан неверный, то нужно выделить нужный столбец и применить правильный тип.

Картинка с сайта: blog.skillfactory.ru

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

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

• Создание сложных условных выражений

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

Пример: сложное условие с несколькими проверками.

Table.AddColumn(Источник, «Категория», each if [Продажи] > 10000 then «Высокие» elseif [Продажи] > 5000 then «Средние» else «Низкие»)

Эта строка добавляет новый столбец, где строки классифицируются как «Высокие», «Средние» или «Низкие» в зависимости от значения в столбце «Продажи». 

• Использование функций List и Record

Работа со списками и записями (List и Record).

Пример: удаление строк, содержащих только пустые значения.

Table.SelectRows(Источник, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {null})))

Запрос фильтрует строки, где все значения не пустые. 

• Динамическая генерация значений

Интерфейс Power Query не позволяет динамически генерировать списки или диапазоны значений. Для этого придется воспользоваться синтаксисом M.

Пример: создание последовательного списка чисел от 1 до 100.

List.Generate(() => 1, each _ <= 100, each _ + 1)

Создает список чисел от 1 до 100.

• Применение пользовательских функций

Хотя интерфейс позволяет использовать предопределенные функции, создание и вызов собственных функций требуют работы в редакторе M.

Пример: создание пользовательской функции для расчета НДС.

(цена) => цена * 0.2

Функция рассчитывает НДС на основе переданного значения. Пользовательские функции можно вызвать в формуле:

Table.AddColumn(Источник, «НДС», each myVAT([Цена]))

• Вложенные запросы и многократная фильтрация

Фильтрация с помощью вложенных запросов.

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

Table.SelectRows(Источник, each [ID] = Table.First(Table.SelectRows(ДругаяТаблица, each [Имя] = «Алексей»))[ID])

Этот запрос находит строки, где ID совпадает с первой строкой другой таблицы.

• Работа с несколькими таблицами через вложенные запросы

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

Пример: обращение к другой таблице внутри запроса.

Table.SelectRows(Источник, each List.Contains(Table.Column(ДругаяТаблица, «ID»), [ID]))

Выбирает строки, где ID из одной таблицы встречаются в другой таблице.

• Уникальные и случайные значения

Генерация случайных чисел.

Пример: генерация 10 случайных значений.

List.Transform({1..10}, each Number.RoundDown(Number.RandomBetween(1, 100)))

Генерирует список из 10 случайных чисел между 1 и 100.

Подведем итог

• Power Query автоматизирует процессы ETL — извлечение, трансформацию и загрузку данных в Excel из других источников.
• В MS Excel 2016 и новее Power Query уже встроен, а в MS Excel 2010 и 2013 его придется установить как надстройку.
• Power Query поддерживает такие форматы файлов, которые можно представить в табличном виде.
• Преобразование файлов проводят в специальном редакторе, интерфейс которого состоит из панели инструментов, списка запросов, строки формул, предварительного просмотра, свойств и истории примененных шагов.
• Шаги отображаются в строке формул в виде запроса на языке M.
• Редактор позволяет сортировать данные, фильтровать их по нужному признаку, создавать новые столбцы в таблице из источника, объединять данные из таблиц.
• Когда данные в источниках меняются, их можно обновить в редакторе, чтобы применить изменения автоматически.
• Можно составлять запросы с помощью языка M. С его помощью создают сложные условия, работают с несколькими таблицами через вложенные запросы и многое другое.

Microsoft Power Query for Excel is a powerful data connector and transformation tool that enables users to easily access and analyze data from a wide range of sources. The software allows users to quickly access, transform, and combine data from multiple sources, allowing them to create powerful insights and visualizations that can be used to inform and drive better business decisions. With Power Query, users can quickly uncover hidden trends, find patterns and correlations, and gain insights into their data in ways that would be impossible with traditional methods.

Power Query provides a unified, easy-to-use user experience to access, transform, and combine data from multiple sources, such as databases, spreadsheets, text files, web services, and more. It also enables users to create powerful data mashups, combining data from different sources and transforming it into insights that are easily consumed by other users. Additionally, Power Query provides a wide range of features for working with data, such as the ability to filter, sort, group, and summarize data.

Features of Microsoft Power Query for Excel:

— Easy to use user interface for accessing and transforming data from multiple sources
— Create powerful data mashups, combining data from different sources
— Filter, sort, group, and summarize data
— Create insights from data that can be easily consumed by other users
— Ability to access data from databases, spreadsheets, text files, web services, and more
— Automatically detect data types, relationships, and anomalies
— Easy to deploy on any platform
— Automatically detect and apply data transformations
— Supports multiple languages including English, French, Spanish, German, and Chinese
— Create custom formulas to transform data
— Create scheduled refreshes to update data automatically
— Create parameterized queries with dynamic inputs
— Publish data to Power BI to create interactive reports
— Publish data to SharePoint to share with other users
— Automatically detect relationships between data sources
— Easily create and share data queries
— Create data models to explore relationships between data sources
— Create data visualizations for easy consumption
— Support for R and Python for data analysis and transformation
— Create custom data connectors to connect to any data source
— Support for Microsoft Azure for storing and analyzing data
— Connect to the Microsoft PowerApps platform for building custom apps
— Comprehensive security features for data privacy and protection
— Advanced data analysis and transformation features
— Support for data wrangling, cleaning, and preparation
— Support for Power BI Desktop, dataflows, and more

Power Query for Excel enables users to easily connect to and extract data from a variety of data sources and transform it into the desired format.

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

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

Картинка с сайта: habr.com

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над 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 появляется отдельная одноименная вкладка.

Картинка с сайта: habr.com

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

Картинка с сайта: habr.com

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

Картинка с сайта: habr.com

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

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

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

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

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

Картинка с сайта: habr.com

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

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

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

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

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

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

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

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

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

Unpivot — Pivot

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

Картинка с сайта: habr.com

Операция 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, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

Картинка с сайта: habr.com

Подключение к различным базам данных. 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), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка

Картинка с сайта: habr.com

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

Картинка с сайта: habr.com

Язык 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