Power query excel что это

Power Query — обзор и обучение

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

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

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

Power Query — одно из трех средств анализа данных, доступных в Excel.

Ресурсы, посвященные Power Query

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

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

Принципы работы Power Query

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

Чтобы понять, как работает Power Query, ознакомьтесь со статьей Начало работы с Power Query.

Где взять Power Query?

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

Чтобы скачатьтекущий выпуск Power Query, нажмите кнопку ниже.

Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

Учебники по Power Query

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

Дополнительные сведения о Power Query

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

Сообщества, форумы и отзывы

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel

Термины «Power Query», «Power Pivot», «Power BI» и прочие «пауэры» все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.

Давайте проясним ситуацию.

Power Query

Еще в 2013 году специально созданная группа разработчиков внутри Microsoft выпустила для Excel бесплатную надстройку Power Query (другие названия — Data Explorer, Get&Transform), которая умеет массу полезных для повседневной работы вещей:

• Загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata. ), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C. ), интернет-сервисы (Facebook, Google Analytics, почти любые сайты).
• Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML. ), как поодиночке, так и сразу оптом — из всех файлов указанной папки. Из книг Excel можно автоматически загружать данные сразу со всех листов.
• Зачищать полученные данные от «мусора»: лишних столбцов или строк, повторов, служебной информации в «шапке», лишних пробелов или непечатаемых символов и т.п.
• Приводить данные в порядок: исправлять регистр, числа-как-текст, заполнять пробелы, добавлять правильную «шапку» таблицы, разбирать «слипшийся» текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.
• Всячески трансформировать таблицы, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно).
• Подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров, т.е. прекрасно заменяет функцию ВПР (VLOOKUP) и ее аналоги.

Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:

В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :

Возможности этих вариантов совершенно идентичны.

Принципиальной особоенностью Power Query является то, что все действия по импорту и трансформации данных запоминаются в виде запроса — последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется «М». Шаги можно всегда отредактировать и воспроизвести повторно любое количество раз (обновить запрос).

Основное окно Power Query обычно выглядит примерно так:

По моему мнению, это самая полезная для широкого круга пользователей надстройка из всех перечисленных в этой статье. Очень много задач, для которых раньше приходилось либо жутко извращаться с формулами, либо писать макросы — теперь легко и красиво делаются в Power Query. Да еще и с последующим автоматическим обновлением результатов. А учитывая бесплатность, по соотношению «цена-качество» Power Query просто вне конкуренции и абсолютный must have для любого средне-продвинутого пользователя Excel в наши дни.

Power Pivot

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

Общие принципы работы в Power Pivot следующие:

1. Сначала мы загружаем данные в Power Pivot — поддерживается 15 различных источников: распространенные БД (SQL, Oracle, Access. ), файлы Excel, текстовые файлы, веб-каналы данных. Кроме того, можно использовать Power Query как источник данных, что делает анализ почти всеядным.
2. Затем между загруженными таблицами настраиваются связи или, как еще говорят, создается Модель Данных. Это позволит в будущем строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица. И никаких ВПР опять же.
3. При необходимости, в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в «умной таблице») и мер (аналог вычисляемого поля в сводной). Всё это пишется на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis eXpressions).
4. На листе Excel по Модели Данных строятся интересующие нас отчеты в виде сводных таблиц и диаграмм.

Главное окно Power Pivot выглядит примерно так:

А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:

У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:

• В Power Pivot нет предела по количеству строк (как в Excel). Можно грузить таблицы любого размера и спокойно работать с ними.
• Power Pivot очень хорошо умеет сжимать данные при загрузке их в Модель. 50 Мб исходный текстовый файл может легко превратиться в 3-5 Мб после загрузки.
• Поскольку «под капотом» у Power Pivot, по сути, полноценный движок базы данных, то с большими объемами информации он справляется очень быстро. Нужно проанализировать 10-15 млн. записей и построить сводную? И все это на стареньком ноутбуке? Без проблем!

К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.

Power Maps

Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).

Демо-версию Power Map (почти не отличающуюся от полной по возможностям, кстати) можно совершенно бесплатно загрузить опять же с сайта Microsoft. Полная же версия включена в некоторые пакеты Microsoft Office 2013-2016 вместе с Power Pivot — в виде кнопки 3D-карта на вкладке Вставка (Insert — 3D-map) :

Ключевые особенности Power Map:

• Карты могут быть как плоскими, так и объемными (земной шар).
• Можно использовать несколько разных типов визуализации (гистограммы, пузырьковые диаграммы, тепловые карты, заливку областями).
• Можно добавлять измерение времени, т.е. анимировать процесс и смотреть на него в развитии.
• Карты подгружаются из сервиса Bing Maps, т.е. для просмотра нужен весьма шустрый доступ в интернет. Иногда возникают сложности с правильным распознаванием адресов, т.к. названия в данных не всегда совпадают с Bing Maps.
• В полной (не демо) версии Power Map можно использовать собственные загружаемые карты, например визуализировать посетителей торгового центра или цены на квартиры в жилом доме прямо на строительном плане.
• На основе созданных гео-визуализаций можно прямо в Power Map создавать видеоролики (пример), чтобы поделиться ими потом с теми, у кого надстройка не установлена или включить в презентацию Power Point.

Power View

Эта надстройка появилась впервые в составе Excel 2013 и предназначена для «оживления» ваших данных — построения интерактивных графиков, диаграмм, карт и таблиц. Иногда для этого используют термины дашборд (dashboard) или панель показателей (scorecard) . Суть в том, что вы можете вставить в ваш файл Excel специальный лист без ячеек — слайд Power View, куда добавить текст, картинки и массу различного типа визуализаций по вашим данным из Модели Данных Power Pivot.

Выглядеть это будет примерно так:

Нюансы тут такие:

• Исходные данные берутся всё оттуда же — из Модели Данных Power Pivot.
• Для работы с Power View необходимо установить на вашем компьютере Silverlight — майкрософтовский аналог Flash (бесплатный).

На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.

Power BI

В отличие от предыдущих, Power BI — это не надстройка для Excel, а отдельный продукт, представляющий собой целый комплекс средств для бизнес- анализа и визуализации. Он состоит из трех ключевых элементов:

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

В Power BI Desktop можно:

• Загружать данные из более чем 70 различных источников (как в Power Query + дополнительные коннекторы).
• Связывать таблицы в модель (как в Power Pivot)
• Добавлять к данным дополнительные вычисления с помощью мер и вычисляемых столбцов на DAX (как в Power Pivot)
• Создавать на основе данных красивейшие интерактивные отчеты с разного типа визуализациями (очень похоже на Power View, но еще лучше и мощнее).
• Публиковать созданные отчеты на сайте Power BI Service (см. следующий пункт) и делиться ими с коллегами. Причем есть возможность давать разные права (чтение, редактирование) разным людям.

2. Онлайн-сервис Power BI — упрощенно говоря, это сайт, где у вас и у каждого пользователя в вашей компании будет своя «песочница» (workspace) куда можно загружать созданные в Power BI Desktop отчеты. Помимо просмотра, позволяет их даже редактировать, воспроизводя онлайн почти весь функционал Power BI Desktop. Также сюда можно заимствовать отдельные визуализации из чужих отчетов, собирая из них свои авторские дашборды.

Выглядит это примерно так:

3. Power BI Mobile — приложение для iOS / Android / Windows для подключения к Power BI Service и удобного просмотра (не редактирования) созданных отчетов и дашбордов прямо на экране телефона или планшета. Скачать его (совершенно бесплатно) можно тут.

На iPhone, например, созданный выше отчет выглядит так:

Причем всё это с сохранением интерактивностии и анимации + заточенность под тач и рисование по экрану пером. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любой момент и в любом месте — нужен только доступ в интернет.

Тарифные планы Power BI. Power BI Desktop и Mobile бесплатны изначально, большинство функций Power BI Service — тоже. Так что для персонального использования или применения в пределах небольшой компании за всё вышеперечисленное не нужно платить ни копейки и можно смело оставаться на плане Free. Если вы хотите делиться отчетами с коллегами и администрировать их права доступа, то придется перейти на Pro (10$ в месяц за пользователя). Есть еще Premium — для больших компаний (>500 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.

Power Query — что такое и почему её необходимо использовать в работе?

Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные (Data) -группа Скачать и преобразовать (Get & Transform)

Для 2010 и 2013 после подключения надстройки появится новая вкладка — POWER QUERY
Скачать Power Query для 2010 и 2013 можно по ссылке: http://go.microsoft.com/fwlink/?Link > После скачивания необходимо закрыть Excel и запустить скачанный файл. После следующего запуска Excel на панели должна появится новая вкладка — POWER QUERY
Что же дает эта надстройка и почему её надо устанавливать? Если в общих чертах: Power Query является очень мощным инструментом и позволяет получить данные из множества различных источников: Excel, CSV, XML, бд Access и SQL, интернет-страницы, OneDrive, сервисы Google и многие другие:

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

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

А далее с помощью редактора мы можем:

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

Конечно, напрашивается вопрос: что будет, если данные в том же файле Excel изменятся? Например, добавятся строки? А все просто — надо будет лишь обновить запрос — он обновит подключение и данные будут опять обработаны и преобразованы с учетом изменений.

Напоследок еще несколько доводов в пользу Power Query:

• она абсолютна бесплатна
• раньше, чтобы создать полноценную сводную таблицу из нескольких файлов или листов необходимо было писать код на VBA. С помощью PowerQuery это может сделать каждый без всяких кодов(пошаговая видеоинструкция как это сделать приведена в статье План-фактный анализ в Excel при помощи Power Query)
• можно легко и просто получить данные из файлов XML, текстовых файлов и CSV, а потом объединить всю информацию в одну таблицу и. Полагаю сами додумаете — главное собрать данные, а что с ними делать мы всегда придумаем исходя из задачи
• можно получать данные из интернета — и это опять же без написания кодов Visual Basic for Applications(VBA). Тот же курс доллара PowerQuery способна «вытащить» на основании ссылки на сайт и обновлять это автоматически, да еще и «привязать» к расчетам в других таблицах
• можно легко и быстро получить обновляемую информацию даже из файлов Google sheets, что до этого момента даже из VBA было занятием не из простых
• есть встроенные возможности получения данных из аккаунта Facebook. Мне лично этого никогда не требовалось, но краем правого уха подмечал, что кому-то это очень было нужно 🙂

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

Статья помогла? Поделись ссылкой с друзьями!

Бесплатный курс обучения Power Query на 1-2-3. Вводный урок

Файлы к уроку можно скачать по ссылке Файлы к уроку.

В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.

Если вам удобнее воспринимать видео, то смотрите этот урок на YouTube.

Power Query — это встроенный инструмент Excel для получения и преобразования данных (Get and Transform).

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

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

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

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

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

Как вы думаете, сколько времени уйдет на решение этой задачи в Excel Power Query? Всего несколько минут. Более того, вам достаточно решить эту задачу всего 1 раз. А когда придут новые данные достаточно будет всего лишь нажать «Обновить».

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

Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.

Если у вас Excel 2010 или 2013, то Power Query нужно сначала скачать. Перейдите по ссылке https://www.microsoft.com/ru-ru/download/details.aspx? >

Теперь в главном меню Excel должна появиться еще одна вкладка. Если вкладка не появилась, то перейдите в меню Файл — Параметры — Настройки — Управление — Надстройки СОМ. В открывшемся окне отметьте галочкой пункт Power Query.

Добавить кнопки Power Query на панель быстрого доступа

Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:

• Изменить запрос
• Запросы и подключения
• Запустить редактор запросов

Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.

Также вы можете скачать готовые настройки панели быстрого доступа по ссылке Файлы к уроку и импортировать их. Кликните правой кнопкой мыши по панели быстрого доступа.

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

Предварительные настройки Power Query

Откройте редактор запросов любым удобным способом:

• При помощи созданной кнопки в панели быстрого доступа
• Вкладка Данные — Группа Получить и преобразовать данные —Получить данные — Запустить редактор запросов

В окне Редактора запросов нажмите Файл — Параметры и настройки — Параметры запроса. В пункте Загрузка данных сделайте настройки как на картинке:

Теперь перейдите в пункт Редактор Power Query и поставьте галочку «Отобразить редактор запросов».

Далее переходим в пункт Конфиденциальность и выбираем «Всегда игнорировать уровни конфиденциальности».

Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.

Создаем первый запрос Power Query

Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:

1. Запускаем редактор запросов
2. Вкладка Главная — Создать источник — Другие источники — Интернет
3. Вводим адрес https://finance.yahoo.com/quote/%5EDJI/components?ltr=1
4. Нажимаем Подключение
5. Слева указываем нужную таблицу и жмем ОК

Перед вам появится следующее окно редактора запросов Power Query:

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

Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.

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

Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.

Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.

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