Power query 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 следующие:
- Сначала мы загружаем данные в Power Pivot - поддерживается 15 различных источников: распространенные БД (SQL, Oracle, Access. ), файлы Excel, текстовые файлы, веб-каналы данных. Кроме того, можно использовать Power Query как источник данных, что делает анализ почти всеядным.
- Затем между загруженными таблицами настраиваются связи или, как еще говорят, создается Модель Данных. Это позволит в будущем строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица. И никаких ВПР опять же.
- При необходимости, в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в "умной таблице") и мер (аналог вычисляемого поля в сводной). Всё это пишется на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis eXpressions).
- На листе 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, а отдельный продукт, представляющий собой целый комплекс средств для бизнес- анализа и визуализации. Он состоит из трех ключевых элементов:
В 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. Также сюда можно заимствовать отдельные визуализации из чужих отчетов, собирая из них свои авторские дашборды.
Выглядит это примерно так:
На iPhone, например, созданный выше отчет выглядит так:
Причем всё это с сохранением интерактивностии и анимации + заточенность под тач и рисование по экрану пером. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любой момент и в любом месте - нужен только доступ в интернет.
Тарифные планы Power BI. Power BI Desktop и Mobile бесплатны изначально, большинство функций Power BI Service - тоже. Так что для персонального использования или применения в пределах небольшой компании за всё вышеперечисленное не нужно платить ни копейки и можно смело оставаться на плане Free. Если вы хотите делиться отчетами с коллегами и администрировать их права доступа, то придется перейти на Pro (10$ в месяц за пользователя). Есть еще Premium - для больших компаний (>500 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.
С помощью Power Query (в & Excel Преобразование get &) можно импортировать или подключиться к внешним данным, а затем сформировать эти данные, например удалить столбец, изменить тип данных или объединить таблицы в соответствии со своими потребностями. Затем можно загрузить запрос в Excel для создания диаграмм и отчетов. Периодически можно обновлять данные, чтобы сделать их актуальными. Power Query доступны в трех Excel, Excel для Windows, Excel для Mac и Excel в Интернете. Общие сведения о всех разделах справки Power Query см. Power Query справке Excel справке.
Существует четыре этапа использования Power Query.
Подключение Подключение к данным в облаке, в службе или локально
Преобразования Формирование данных в соответствии со своими потребностями, в то время как исходный источник остается неизменным
Объединить Интеграция данных из нескольких источников для получения уникального представления в данных
Нагрузки Выполните запрос и загрузите его на лист или модель данных и периодически обновляйте его.
В следующих разделах каждый этап рассматривается более подробно.
Можно использовать Power Query для импорта в один источник данных, например в книгу Excel, или в несколько баз данных, веб-каналов или служб, распределенных по облаку. Источники данных включают данные из Интернета, файлов, баз данных, Azure или даже Excel таблиц в текущей книге. Используя Power Query, вы можете объединить все эти источники данных с помощью собственных уникальных преобразований и сочетаний для получения аналитических сведений, которые в противном случае не были бы видны.
После импорта можно обновить данные, чтобы добавить дополнения, изменения и удаления из внешнего источника данных. Дополнительные сведения см. в разделе "Обновление подключения к внешним данным" Excel.
Преобразование данных означает их изменение в соответствии с требованиями к анализу данных. Например, можно удалить столбец, изменить тип данных или отфильтровать строки. Каждая из этих операций представляет собой преобразование данных. Этот процесс применения преобразований (и объединения) к одному или нескольким наборам данных также называется формированием данных.
Подумайте об этом так. Вазы начинаются как часть уютов, которые одна фигура создает что-то практическое и прекрасное. Данные одинаковы. Она должна формироваться в таблицу, подходящую для ваших потребностей и поддерживающая привлекательные отчеты и панели мониторинга.
Power Query использует выделенное окно с именем Редактор Power Query для упрощения и отображения преобразований данных. Чтобы открыть Редактор Power Query, выберите команду "Запустить Редактор запросов" в группе "Получение данных& Преобразование данных", но она также откроется при подключении к источнику данных, создании запроса или загрузке запроса.
Этот Редактор Power Query отслеживает все действия, которые вы выполняете с данными, записав и помечая каждое преобразование или шаг, применяемый к данным. Независимо от того, является ли преобразование подключением к данным, удалением столбца, слиянием или изменением типа данных, можно просматривать и изменять каждое преобразование в разделе APPLIED STEPS на панели Параметры запроса.
Существует множество преобразований, которые можно выполнить из пользовательского интерфейса. Каждое преобразование записывается как шаг в фоновом режиме. Вы даже можете изменять и записывать собственные шаги с помощью Power Query языка M в Расширенный редактор.
Все преобразования, применяемые к подключениям к данным, представляют собой запрос, который представляет собой новое представление исходного (без изменений) источника данных. При обновлении запроса каждый шаг выполняется автоматически. Запросы заменяют необходимость вручную подключать и формировать данные в Excel.
Вы можете объединить несколько запросов в книге Excel путем их добавления или объединения. Операции добавления и слияния выполняются в любом запросе с табличной фигурой и не зависят от источников данных, из которых поступают данные.
Добавить Операция добавления создает новый запрос, содержащий все строки из первого запроса, за которым следуют все строки из второго запроса. Можно выполнить два типа операций добавления:
Промежуточное добавление Создает новый запрос для каждой операции добавления.
Встроенная надстройка Добавляет данные в существующий запрос, пока не будет достигнут окончательный результат.
Объединить Операция слияния создает новый запрос из двух существующих запросов. Этот запрос содержит все столбцы из первичной таблицы, один из них служит ссылкой для навигации по связанной таблице. Связанная таблица содержит все строки, соответствующие каждой строке из общего значения столбца в первичной таблице. Кроме того, можно развернуть или добавить столбцы из связанной таблицы в основную таблицу.
Существует два основных способа загрузки запросов в книгу:
В Редактор Power Query можно использовать команды "Закрыть" и "Загрузить" в группе "Закрыть" на вкладке "Главная".
В области Excel "Запросы книги" (выбор & подключения) можно щелкнуть запрос правой кнопкой мыши и выбрать команду "Загрузить в".
Вы также можете настроить параметры загрузки с помощью диалогового окна "Параметры запроса" (выберите параметры и параметры файла> параметры > Параметры запроса), чтобы выбрать способ просмотра данных и место загрузки данных на листе или в модель данных (которая является реляционным источником данных нескольких таблиц, которые находятся в книге).
Более десяти лет Power Query поддерживается в Excel для Windows. Теперь Excel расширяется Power Query поддержки Excel для Mac и добавляется поддержка Excel в Интернете. Это означает, что мы Power Query на трех основных платформах и демонстрируем популярность и функциональность Power Query среди Excel клиентов. Следите за будущими объявлениями о Microsoft 365 стратегии иновых возможностях Excel для Microsoft 365.
Интеграция Get & Transform Data (теперь называется Power Query) в Excel в течение нескольких лет.
Excel 2010 и 2013 для Windows
В Excel 2010 для Windows мы впервые представили Power Query и она была доступна в виде бесплатной надстройки, которую можно скачать здесь: скачайте надстройку Power Query надстройку. После включения Power Query функции были доступны на Power Query на ленте.
Microsoft 365
Мы обновили Power Query в качестве основного интерфейса в Excel для импорта и очистки данных. Вы можете получить доступ к Power Query и средствам импорта данных из группы "Получение & Преобразование данных" на вкладке "Данные" Excel ленты.
Этот интерфейс включает расширенные функции импорта данных, переупорядоченные команды на вкладке "Данные", новую панель "Запросы & Подключение", а также возможность эффективного формирования данных путем сортировки, изменения типов данных, разделения столбцов, агрегирования данных и т. д.
Этот новый интерфейс также заменил старые мастера импорта устаревших данных в команде "Данные " в группе "Получение внешних данных ". Однако доступ к ним по-прежнему можно получить в диалоговом окне Excel "Параметры файлов"> ( выбор параметров файлов>> " Показать устаревшие мастера импорта данных").
Excel 2016 2019 для Windows
Мы добавили тот же интерфейс Get & Transform Data на основе технологии Power Query, что и в Microsoft 365.
Excel для Microsoft 365 для Mac
В 2019 году мы начали процесс поддержки Power Query в Excel для Mac. С тех пор мы добавили возможность обновлять Power Query из файлов TXT, CSV, XLSX, JSON и XML. Мы также добавили возможность обновлять данные с SQL сервера и из таблиц & диапазонов в текущей книге.
В октябре 2019 г. мы добавили возможность обновлять существующие Power Query и использовать VBA для создания и изменения новых запросов.
В январе 2021 г. мы добавили поддержку обновления Power Query запросов из OData и SharePoint источников.
Дополнительные сведения см. в Power Query в Excel для Mac.
Примечание В Power Query 2019 Excel 2016 Excel 2019 для Mac поддержка Power Query не поддерживается.
Каталог данных устаревания
С помощью Каталог данных вы можете просмотреть общие запросы, а затем выбрать их для загрузки, изменения или иного использования в текущей книге. Эта функция постепенно устарела:
1 августа 2018 г. мы перестали подключать новых клиентов к Каталог данных.
3 декабря 2018 г. пользователям не удалось поделиться новыми или обновленными запросами в Каталог данных.
4 марта 2019 г. работа Каталог данных прекращена. После этой даты мы рекомендуем скачать общие запросы, чтобы вы могли продолжать использовать их за пределами Каталог данных, используя параметр "Открыть" в области задач "Мои Каталог данных Запросы".
Power Query нерекомендуемой надстройки
В начале 2019 года мы официально не рекомендуем использовать надстройку Power Query, которая требуется для Excel 2010 и 2013 для Windows. В качестве предоставленного решения вы по-прежнему можете использовать надстройку, но это может измениться позже.
Отключен соединитель данных Facebook
Импорт и обновление данных из Facebook в Excel прекращено в апреле 2020 г. Все подключения Facebook, созданные до этой даты, больше не работают. Мы рекомендуем как можно скорее изменить или удалить все существующие Power Query, использующие соединитель Facebook, чтобы избежать непредвиденных результатов.
Power Query — это механизм преобразования данных и подготовки данных. Power Query поставляется с графическим интерфейсом для получения данных из источников и редактора Power Query для применения преобразований. Так как подсистема доступна во многих продуктах и службах, назначение, в котором будут храниться данные, зависит от того, где использовался Power Query. С помощью Power Query можно выполнять обработку данных для извлечения, преобразования и загрузки (ETL).
Схема с символизируемыми источниками данных справа, передавая power query для преобразования, а затем переход в различные места назначения, такие как Azure Data Lake Storage, Dataverse, Microsoft Excel или Power BI.
Как Power Query помогает с получением данных
Бизнес-пользователи тратят до 80 процентов времени на подготовку данных, что задерживает работу по анализу и принятию решений. Некоторые проблемы влияют на эту ситуацию, и Power Query помогает решить многие из них.
Существующий вызов | Как Power Query помогает? |
---|---|
Поиск и подключение к данным слишком сложно | Power Query позволяет подключаться к широкому спектру источников данных, включая данные всех размеров и фигур. |
Возможности подключения к данным слишком фрагментированы | Согласованность взаимодействия и четность возможностей запросов во всех источниках данных. |
Данные часто необходимо изменить перед потреблением | Высокоинтерактивный и интуитивно понятный интерфейс для быстрого и итеративного создания запросов по любому источнику данных любого размера. |
Любое формирование является одноразовым и не воспроизводимым | При использовании Power Query для доступа к данным и преобразования необходимо определить повторяемый процесс (запрос), который можно легко обновить в будущем для получения актуальных данных. Если необходимо изменить процесс или запрос для учета базовых данных или изменений схемы, можно использовать тот же интерактивный и интуитивно понятный интерфейс, который использовался при первоначальном определении запроса. |
Объем (размеры данных), скорость (скорость изменения) и разнообразие (широкий спектр источников данных и фигур данных) | Power Query позволяет работать с подмножеством всего набора данных для определения необходимых преобразований данных, что позволяет легко фильтровать и преобразовывать данные в управляемый размер. Запросы Power Query можно обновлять вручную или с помощью возможностей запланированного обновления в определенных продуктах (таких как Power BI) или даже программным способом (с помощью объектной модели Excel). Так как Power Query обеспечивает подключение к сотням источников данных и более 350 различных типов преобразований данных для каждого из этих источников, вы можете работать с данными из любого источника и в любой форме. |
Возможности Power Query
Взаимодействие с пользователем Power Query предоставляется через пользовательский интерфейс редактора Power Query. Цель этого интерфейса заключается в том, чтобы помочь вам применить необходимые преобразования, просто взаимодействуя с понятным набором лент, меню, кнопок и других интерактивных компонентов.
Редактор Power Query — это основной интерфейс подготовки данных, где можно подключаться к широкому спектру источников данных и применять сотни различных преобразований данных, предварительно просматривая данные и выбирая преобразования из пользовательского интерфейса. Эти возможности преобразования данных являются общими для всех источников данных, независимо от ограничений базового источника данных.
При создании нового шага преобразования, взаимодействуя с компонентами интерфейса Power Query, Power Query автоматически создает код M, необходимый для преобразования, чтобы не писать код.
В настоящее время доступны два интерфейса Power Query:
- Power Query Online — в интеграции, таких как потоки данных Power BI, потоки данных Microsoft Power Platform, потоки данных Фабрики данных Azure и многое другое, предоставляющее возможности через веб-страницу.
- Power Query for Desktop — в интеграции, таких как Power Query для Excel и Power BI Desktop.
Хотя существуют два интерфейса Power Query, они оба обеспечивают практически одинаковый пользовательский интерфейс в каждом сценарии.
Преобразования
Подсистема преобразования в Power Query включает множество предварительно созданных функций преобразования, которые можно использовать с помощью графического интерфейса редактора Power Query. Эти преобразования могут быть такими же простыми, как удаление столбца или фильтрации строк или как обычное использование первой строки в качестве заголовка таблицы. Существуют также дополнительные параметры преобразования, такие как слияние, добавление, группировка по, сводка и отмена сводных данных.
Все эти преобразования становятся возможными, выбрав параметр преобразования в меню, а затем применив параметры, необходимые для этого преобразования. На следующем рисунке показаны некоторые преобразования, доступные в редакторе Power Query.
Потоки данных
Power Query можно использовать во многих продуктах, таких как Power BI и Excel. Однако использование Power Query в продукте ограничивает его использование только этим конкретным продуктом. Потоки данных — это не зависящая от продукта версия службы power Query, которая работает в облаке. С помощью потоков данных можно получать данные и преобразовывать данные таким же образом, но вместо отправки выходных данных в Power BI или Excel выходные данные можно хранить в других вариантах хранения, таких как Dataverse или Azure Data Lake Storage. Таким образом, можно использовать выходные данные потоков данных в других продуктах и службах.
Язык формул Power Query M
В любом сценарии преобразования данных существуют некоторые преобразования, которые невозможно сделать наилучшим образом с помощью графического редактора. Для некоторых из этих преобразований могут потребоваться специальные конфигурации и параметры, которые графический интерфейс в настоящее время не поддерживает. Подсистема Power Query использует язык скриптов в фоновом режиме для всех преобразований Power Query: языка формул Power Query M, также известного как M.
Язык M — это язык преобразования данных Power Query. Все, что происходит в запросе, в конечном итоге записывается в M. Если вы хотите выполнить расширенные преобразования с помощью обработчика Power Query, можно использовать расширенный редактор для доступа к скрипту запроса и его изменения по мере необходимости. Если вы обнаружите, что функции и преобразования пользовательского интерфейса не будут выполнять точные изменения, используйте расширенный редактор и язык M для точной настройки функций и преобразований.
Где можно использовать Power Query?
В следующей таблице перечислены продукты и службы Майкрософт, где можно найти Power Query.
В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над 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 умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка
Текстовые функции
- Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
- Изменить регистр ячеек в столбце
- Подсчитать количество символов в ячейках столбца.
Числовые функции
К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:
- Арифметические операции
- Возводить в степени, вычислять логарифмы, факториалы, корни
- Тригонометрические операции
- Округлять до заданных значений
- Определять четность и т.д.
Функции для работы с датами, временем и продолжительностью
К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:
- Автоматическое определение формата вписанной даты (в excel c этим большая боль)
- Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.
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.
- разбираю семантику для Толстых проектов,
- Делаю частотные словари,
- Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
- Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
- Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
- Делаю аудит контекстной рекламы на данных из K50 статистика,
- И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды
Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.
Пару слов про локализацию
На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.
В нашем менеджерском труде есть много хорошего и не очень. Но ничто не портит нам жизнь так, как многочисленные эксельки, в которых постоянно нужно что-то ВПР-ить, СУММ-ировать, СУММПРОИЗВ-одить и заниматься прочими рутинными операциями, отнимающими кучу времени и сил. Есть инструмент, позволяющий если не убрать, то минимизировать рутину везде, где есть паттерны данных. Итак, вашему вниманию предлагается чуть более глубокое погружение в принципы работы PowerQuery.
Сразу же хочу сориентировать по поводу того, что уже есть на Хабре по данной теме:
-
— Отличный обзор возможностей PowerQuery для новичков. Даёт представление об инструменте на примерах. Если вы никогда не использовали PowerQuery, то начать, наверное, стоит с этой статьи. Автор введёт в курс дела.
— Погружение в функции языка «M» на примере локализации списка месяцев. Автор кратко коснулся мощнейшей функции List.Accumulate описание которой заслуживает отдельного материала на Хабре. В качестве тизера к такой публикации, могу сказать, что List.Accumulate принимает на вход абсолютно любой тип аргументов.
Что такое Power Query (PQ)
Дабы не повторять уже сказанное в материалах, ссылки на которые указаны выше, буду краток: Power Query — это инструмент для преобразования формы представления любых логически организованных данных. То есть на входе может быть любая каша, но в которой есть хоть какая-то логика. А на выходе — таблица. По мнению некоторых экспертов, PQ является ETL инструментом. То есть служит для Extract – извлечения, Transform – преобразования и Load – выгрузки данных. По опыту, данная работа занимает до 60% рабочего времени, например, менеджера по контекстной рекламе и до 90% времени аналитика. Важная особенность данной работы состоит в том, что её, как правило, нужно повторять изо дня в день, неделю за неделей. Бесконечно переносить данные, копипастить, вычислять. Для особо тяжёлых случаев программисты пишут решение, в котором всё это делается автоматически. Проблема состоит в том, что решения дороги, а программисты почти всегда заняты более насущными вопросами, чем автоматизация рутинных операций менеджера. PQ позволяет решить указанные трудности:
- Автоматически вытащить данные почти откуда угодно
- Преобразовать данные в соответствии с заранее созданной моделью, без необходимости заново повторять одни и те же действия вручную
- Представить полученные данные в том виде (видах), которые необходимы
На сегодняшний день Power Query доступен только для MS Excel, работающего под ОС Windows. До Excel 2013 включительно, распространялся в виде подключаемого модуля. Начиная с Excel 2016 стал встроенным.
Методы преобразования данных «кнопками» подробно разобраны в статьях указанных выше, особенно в первой. Далее статья будет несколько сложновата для новичков, поскольку мы разбираем подноготную, а конкретно то, что можно увидеть при нажатии на кнопку «Расширенный редактор» в редакторе запросов.
Кнопка «Расширенный редактор»
Как устроен язык «M»
Важно всегда иметь в виду, что конструкция let …. in … является просто “syntactic sugar”. let по факту представляет из себя запись с множеством пар вида «имя=значение», а in это просто ссылка на некоторое значение в этой записи (причём не обязательно последнее). Сложновато. Но давайте попробуем разобраться. В языке «M» есть следующие типы значений:
Абсолютна идентична этой:
Вычисления происходят на базе значений, полученных в результате предыдущих действий. Чем-то это напоминает цепочку формул Excel:
A1=A2+A3
A2=A3+2
A3=5
В случае с PQ обращение идёт не к ячейкам, а к значениям. Выражения, которые входят в запись или список, вычисляются методом «ленивых» вычислений. То есть пока мы прямо не сошлёмся на значение, получаемое в результате выполнения некоторого выражения, оно не будет выполнено.
Коротко о секциях
В заключении хотелось бы напомнить, что любые, даже самые сложные конструкции языка «M» по своей сути состоят из типов, указанных выше. В подавляющем большинстве случаев, это записи и списки, плюс немного встроенных функций для преобразований. Чтобы было проще ориентироваться в многообразии доступного функционала, приведу несколько полезных ссылок, которыми сам пользуюсь регулярно.
Читайте также: