Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс. Метрика, Call. Touch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.
Для извлечения данных из внешних источников можно использовать приложение Microsoft Query. Выберите вариант Просмотр или изменение данных в Microsoft Query (View Data Or Edit Query In Microsoft Query) в последнем окне .
И вот под катом вы можете найти подробности всего этого великолепия возможностей. Совместимость и технические подробности. Power Query доступен бесплатно для всех версий Windows Excel 2.
Microsoft Power Query для Excel — это надстройка Excel, которая повышает удобство работы в области бизнес-аналитики с .
Windows Excel 2. 01. Для пользователей Mac.
OS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке). Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.
Как оно работает. После установки Power Query в интерфейсе Excel 2. В новом Excel 2. 01. Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”. Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2).
И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным. Возможности Power Query. У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.
Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех- же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов. Объединение файлов лежащих в папке. Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы.
Это может быть полезно, например, если вам периодически приходят какие- то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка. Текстовые функции. К столбцам из текста в Power Query по нажатию на кнопки на Ribbon можно применять такие функции как: Разделить столбец по символу или по количеству символов. И в отличие от 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 и My. SQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т. А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок. Язык программирования “М”.
Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М. На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка. Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта.
И выглядеть будет он примерно так: Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса. Light Фильтр Для Фотошопа. Загрузка данных из Яндекс.
Метрики, Google Analytics и прочих Api. Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс. Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandex. Metrika и PQGoogle.
Analytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами. Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс. Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным : )Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из Call.
Touch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб- страничек на предмет извлечения актуальной информации. Еще раз про повторяемость и про варианты применения. Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу.
Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных. Я занимаюсь веб- аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — Power.
Pivot. Вот часть задач, которые я делаю с использованием Power Query: разбираю семантику для Толстых проектов,Делаю частотные словари,Создаю веб- аналитические дашборды и отчеты для анализа конкретных срезов, Восстанавливаю достижение целей в системах веб- аналитики, если они не настроены на проекте,Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса: ),Делаю аудит контекстной рекламы на данных из K5. И много других разных ad- hoc analysis задач, которые нужно сделать лишь однажды. Вот bi систему, про которую я рассказывал на Yac/M 2.
Power Query и загружал данные внутрь Power. Pivot. Пару слов про локализацию. На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.
Ссылки по теме. Официальные ресурсы на английском: Скачать Английскую версию Power Query. Справка по формулам языка MВетка Форума Microsoft Technet про Power Query. На момент написания статьи на русском языке информации по Power Query практически нет, и то что мной найдено приведено ниже: Импорт данных из таблиц в Google Spreadsheets Power BI.
Получаем данные из REST API Сообщество продвинутых пользователей Excel, Power Pivot и Power Query в Facebook. Обновляемая страничка в Marketing- wiki про Power Query. На английском: Видео с демонстрацией возможностей Power Query на конференции Tech. Ed North America.
Блог Chris Webb, пожалуй наиболее полный ресурс с примерами использования Power Query. Книга Chris Webb — Power Query for Power BI and Excel.