Как сделать надстройку power query

Добавил пользователь Алексей Ф.
Обновлено: 04.10.2024

Зайдите в Параметры Excel, нажав на вкладку ФАЙЛ.

с Левой стороны внизу нажмите на кнопку НАДСТРОЙКИ, затем справа в нижней части в строке УПРАВЛЕНИЕ выберите НАДСТРОЙКИ COM, затем нажмите кнопку ПЕРЕЙТИ.

Осталось в появившемся окне установить галочки MICROSOFT POWER. ( будет 3 пункта). Готово!

Учтите, что в этой версии PowerQuery называется СКАЧАТЬ И ПРЕОБРАЗОВАТЬ, а во-вторых, она есть только в версии 365 или Pro Plus. Если у вас другая версия офиса, то скорее всего вы не увидите в ней PowerQuery. Говорю "скорее всего, так как Microsoft могут и поменять решение )))

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

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

В сценарии Power Query позвольте , где вы размещаете код, который вы хотите запустить (ввод), и где вы хотите его запустить (вывод). Код, который запускается внутри оператора let может состоять из переменных, выражений и значений.

Имея это в виду, давайте начнем с нашего скрипта Power Query.

Импорт данных в Power Query

Для этого примера давайте возьмем созданную нами таблицу с именем Fruits.xlsx, которая отображает различные фрукты и их цены. Есть несколько простых столбцов для "Fruit" , "City" и "Price" .

Цель этого руководства — взять столбец "Price" и преобразовать данные из числа в формат валюты с помощью Power Query.

Мы сохранили эту таблицу на рабочем столе. Давайте создадим новую книгу Excel под названием Fruit Data.xlsx для импорта наших данных.

Использование редактора Power Query

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

Нажмите Transform Data, чтобы открыть редактор Power Query.

Просмотр скрипта

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

С открытым редактором Power Query Editor на панели инструментов в разделе Home нажмите Advanced Editor, чтобы открыть скрипт. Откроется расширенный редактор, и внутри вы увидите скрипт Power Query на языке M.

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

Содержание скрипта

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

Сценарий Разбивка

Код начинается с оператора let , который запускает каждый блок кода. Первое, что сделает Power Query, — это импортирует файл в переменную с именем Source которая является просто файлом Fruit.xlsx, который мы выбрали для импорта.

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

Оттуда Power Query берет лист из Fruit.xlsx и устанавливает его в переменную с именем Fruit_Sheet .

Table.PromoteHeaders строка использует функцию Table.PromoteHeaders которая берет заголовки столбцов, которые мы использовали в Excel, и делает их заголовками в Power Query.

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

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

Теперь, когда сценарий находится в поле зрения, и мы рассмотрели код, давайте сделаем наше редактирование. Помните, что целью было взять все данные в столбце "Price" и изменить их на валюту.

К счастью, у нас уже есть идея, как этого добиться. Напомним эту строку кода:

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

Каждому столбцу присваивается тип один за другим, поэтому давайте внесем изменения непосредственно в код. В настоящее время столбцу "Price" присвоен type number . Мы хотим, чтобы это была валюта, поэтому давайте изменим этот код на Currency.Type.

Вот как это выглядит:

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

Лучший анализ данных в Microsoft Excel

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

Такой анализ выполняется в масштабе всей компании либо для отдельных центров финансовой активности (бизнес-единиц), проектов или направлений деятельности. Это позволяет уточнить, из-за каких именно структурных элементов бизнеса возникают отрицательные или положительные отклонения плановых статей бюджетов от фактических.

План-фактный анализ представляет собой сравнение плановых и фактических показателей финансового бюджета и выявление причин отклонений.

Исходные данные для план-фактного анализа

1) планируемые величины статей доходов и расходов (бюджет доходов и расходов БДР);

2) фактические величины статей доходов и расходов (Отчет по доходам и расходам).

Аналитика статей доходов и расходов приведена в табл. 1 в разрезе бизнес-единиц и направлений их деятельности.

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

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

Еще одно значительное преимущество Power Query в том, что таблицы (модели данных), сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия надстройки похож на связанные таблицы Excel.

Преимущества Power Query:

1. Можно использовать различные форматы исходных данных: xml, csv, xls, doc.

2. Можно загружать данные в таблицу Excel из различных источников:

Интернет;
базы данных SQL, Oracle, Access, IBM DB, Mysql, Sybase и т. д.;
веб-службы, протоколы, интерфейсы и облачные хранилища.

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

4. Поиск данных по каталогам.

Эти возможности используются также в надстройке Excel Power BI[3]., а также в надстройке Power Query Add-In, доступной для предыдущих версий Excel.

Основные этапы подключения и преобразования данных в Excel посредством Power Query представлены на схеме.

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

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

Чтобы использовать возможности надстройки Power Query, необходимо создать запрос (query) в рабочей книге Excel. Запрос позволяет подключить, просмотреть и преобразовать данные из различных источников. Затем преобразованные данные загружаются в таблицу (лист) Excel или во встроенную модель данных в Excel, далее при необходимости данные обновляются. Также есть возможности редактировать и пересылать сформированный запрос.

Этап 2. Формируем запросы для план-фактного анализа в Power Query

Шаг 3. Формируем запрос [БДР-план] в модели данных

Запрос [БДР-план] включает в себя следующие операции (рис. 1):

5) после подтверждения (нажимаем ОК) запрос [БДР план][4] загружается в модель данных[5].

Шаг 4. Формируем запрос [Отчет-факт] в модели данных

Запрос [Отчет-факт] включает следующие операции (аналогично запросу [БДР план]):

Этап 3. Объединяем запросы для план-фактного анализа в Power Query

[1] БДР (бюджета доходов и расходов), БДДС (бюджета движения денежных средств), ББЛ (бюджета по балансовому листу).

[2] Дашборд (англ. Dashboard) — аналитический инструмент, наглядное представление информации о бизнес-процессах, о состоянии какого-то объекта в виде динамических онлайн-диаграмм.

[3] Power BI — это комплексное программное обеспечение бизнес-анализа (BI) компании Microsoft, объединяющее несколько программных продуктов, имеющих общий технологический и визуальный дизайн, соединителей (шлюзов), а также web-сервисов. Power BI относится к классу self-service BI, и BI с резидентным вычислением (англ. in-memory computing). Является частью единой платформы Microsoft Power Platform.

Ключевой и самый первый продукт линейки — Power BI Desktop состоит из трех интегрированных компонентов, имеющих каждый свой интерфейс:

Power Query (редактор запросов) — выполняет загрузку и очистку данных (ETL);
PowerPivot (наборы данных и модели данных) — интерфейс работы с табличными данными в оперативной памяти где выполняются запросы к данным, агрегация, расчёты и т. п.;
Power View — подсистема визуализации и построения отчётов (Reporting).

[4] Квадратными скобками (напр., [БДР план]) обозначены объекты модели данных.

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

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

Вначале, его можно было скачать лишь с официального сайта Microsoft, в качестве отдельной надстройки для MS Excel 2010 MS Excel 2013. Однако, Power Query оказался настолько революционным и обрёл такую популярность, что начиная с MS Excel 2016 он был интегрирован в программу и доступен уже прямо из коробки.

Архитектура Power Query

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

Получение данных;
Преобразование данных;
Загрузка данных;
Обновление данных.

Получение данных

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

Пользователю предлагается выбрать из четырёх групп источников:

Из файла;
Из базы данных;
Из Azure;
Из других источников.

На практике, чаще всего используется группа Из файла, позволяющая получать данные из книги Excel, из файлов CSV и XML, из текстовых файлов и (целым скопом) из папки.

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

На вкладке Данные выбираем Создать запрос -> Из файла -> Из текста. В открывшемся окне Импорт данных находим нужный нам файл и жмём на кнопку Импорт.

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

Главная;
Преобразование;
Добавить столбец;
Просмотр.

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

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

Прежде всего избавимся от четырёх верхних строк, которые не представляют для нас никакой ценности. Для этого, на вкладке Главная выбираем Сократить строки -> Удалить строки -> Удаление верхних строк . Далее, в появившемся окне указываем количество строк, которые необходимо удалить и жмём ОК.

Теперь нам нужно разделить данные по столбцам. Опять же, на вкладке Главная выбираем Разделить столбец -> По разделителю и в появившемся окне в качестве разделителя указываем табуляцию, после чего жмём ОК.

Далее, на вкладке Главная выбираем команду Использовать первую строку в качестве заголовков.

И последний штрих - если присмотреться к столбцу Сумма, то можно увидеть, что Power Query присвоил ему текстовый тип данных.

Чтобы превратить эти данные в числовые выделяем столбец Сумма и на вкладке Главная изменяем тип данных на Целое число.

Загрузка данных

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

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

загрузка данных в таблицу с указанием листа (новый либо существующий);
только создание соединения - в случае если мы планируем использовать этот запрос для объединения с другими запросами;
добавление в модель данных - если мы хотим загрузить полученные данные прямо в модель данных Power Pivot.

Чтобы загрузить новые данные на лист, на вкладке Данные, в группе Подключения, жмём на кнопку Обновить.

И это всё! Только подумайте об этом - Вам больше не нужно проходить весь процесс заново, как Вы раньше делали при обычной выгрузке данных из текстовых таблиц. Всё, что Вам нужно всего лишь нажать на одну кнопку!

Заключение

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

Читайте также: