Power Query. Введение. Знакомство с Power Query

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

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

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

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

1-ый столбец — это дата прохождения теста. 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?id=39379 и нажмите «Скачать». Позже отметьте галочкой файл для вашей разрядности операционной системы (64 либо 32 бит). Опосля скачки файла запустите установку.

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

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

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

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

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

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

Подготовительные опции Power Query

Откройте редактор запросов хоть каким комфортным методом:

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

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

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

Дальше перебегаем в пункт Конфиденциальность и избираем «Постоянно игнорировать уровни конфиденциальности».

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

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

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

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

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

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

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

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

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

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

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

Power Query: стероиды для MS Excel и Power BI

image

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

Интересно почитать:  Как в excel убрать автозамену на дату

Power Query дозволяет забирать данные из самых различных источников (таковых как csv, xls, json, текстовых файлов, папок с этими файлами, самых различных баз данных, разных api вроде Facebook opengraph, Гугл Analytics, Yandex.Метрика, 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 умеет забирать данные из обозначенной папки и соединять воединыжды их содержимое в единые таблицы. Это быть может полезно, к примеру, если для вас временами приходят какие-то спец отчеты за отдельный просвет времени, но данные для анализа необходимы в общей таблице. Гифка

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

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

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

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

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

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

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

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

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. Но, он весьма прост в исследовании и открывает большие способности по манипуляции данными, которые недосягаемы с внедрением графического интерфейса.

Загрузка данных из Yandex.Метрики, Гугл Analytics и иных Api

Мало овладев языком “M” я сумел написать программы в Power Query, которые могут подключаться к API Yandex.Метрики и Гугл Analytics и забирать оттуда данные с задаваемыми опциями. Программы PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю воспользоваться. И я буду весьма рад, если эти программки будут дорабатываться энтузиастами.

Для Гугл Analytics подобного рода экспортеров в различных реализациях довольно много, но вот для Yandex.Метрики, как я понимаю, мой экспортер был первым на публике легкодоступным, да к тому же бесплатным 🙂

Power Query умеет сформировывать headers для post и get запросов и забирать данные из веба. Благодаря этому, при должном уровне сноровки, Power Query можно подключить фактически к хоть каким API. А именно, я для собственных исследовательских работ дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компом Rescuetime, занимаюсь парсингом подходящих мне веб-страничек на предмет извлечения животрепещущей инфы.

Снова про повторяемость и про варианты внедрения

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

Я занимаюсь веб-аналитикой и контекстной рекламой. И так вышло, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Совместно с тем возросло и мое потребление иной восхитительной надстройки в MS Excel — PowerPivot.

  1. разбираю семантику для Толстых проектов,
  2. Делаю частотные словари,
  3. Создаю веб-аналитические дашборды и отчеты для анализа определенных срезов,
  4. Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
  5. Сглаживаю прогноз вероятности способами Андрея Белоусова (+Байеса:),
  6. Делаю аудит контекстной рекламы на данных из K50 статистика,
  7. И много остальных различных ad-hoc analysis задач, которые необходимо создать только в один прекрасный момент

Вот bi систему, про которую я говорил на Yac/M 2015 (видео) я делал полнстью с помощью Power Query и загружал данные вовнутрь PowerPivot.

Пару слов про локализацию

На веб-сайте Microsoft для юзеров из Рф по дефлоту скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на российский языка программирования (как это изготовлено с языком формул в excel) не добрались, но жизнь юзерам разноплановыми переводами очень усложнили. И я призываю вас закачивать, устанавливать и воспользоваться британской версией Power Query. Поверьте, она будет еще понятнее.

Power View в Excel 2016

Хороший денек, почетаемые подписчики и читатели блога! Я, как и водится, постоянно описываю нововведения в Excel с приметным запозданием. Что поделать — я реакционер (хотя сейчас стильно гласить — слоупок).

Итак, сейчас начнём разговор о Power инструментах в Excel. В этом видео на канале уже был обзор Power Map. В данной статье мы употребляли приложение Bing Maps.

Что такое Power View?

Ответ весьма прост — инструмент визуализации данных из таблицы. Есть свои ограничения (неотклонима установка Silver Light от Microsoft), есть и масса плюсов в виде интерактивных отчётов, диаграмм, таблиц. К примеру, у нас есть таблица, необходимо чтоб эти данные отобразились в диаграмме. Оговорюсь сходу — таблица большая и всю её можно отыскать на веб-сайте ЦИК.

Интересно почитать:  Циклическая ссылка в excel

Power View в Excel 2016

Включение Power View в Excel.

Не совершенно понятные вещи стали твориться с самого начала — включить надстройку недостаточно! Нам придётся пройти маленький квест:

  • установить MS Silverlight;
  • включить надстройку в Excel;
  • добавить клавишу Power View на вкладку «Вставка».

В первых 2-ух пт сложного ничего нет — скачали к для себя установщик, сняли галки с предложения употреблять Bing, дальше, дальше, «Ок».

В плане включения надстройки ещё проще — идём в «Характеристики», «Надстройки», понизу избираем из перечня «Надстройки COM» либо перебегаем на вкладку «Разраб», отыскиваем пункт «Надстройки COM», в показавшемся окне отмечаем галкой «Power View», жмём «Ок».

Power View в Excel 2016

Мы не из трусливых:

  • идём в «Характеристики»;
  • избираем пункт «Настроить ленту»;
  • раскрываем перечень вкладки «Вставка»;
  • создаём новейшую группу «Отчёты» (либо как для вас захочется);
  • слева избираем перечень «Все команды»;
  • добавляем Power VIew в сделанную группу установок.

Power View в Excel 2016

Тщательно я говорил о настройке ленты в Excel вот в этом видео.

Опосля прибавления на вкладке «Вставка» покажется новейший блок клавиш и сама клавиша «Power View».

Power View в Excel 2016

Тэк-с, собственного мы достигнули. Сейчас пришло время загрузить таблицу. Как будете её загружать — дело десятое (советую Power Query), основное, чтоб она возникла на листе Excel.

Power View в Excel 2016

Непременно сделайте ваши таблицы умными — щёлкнуть в всякую ячейку и надавить Ctrl+T, в показавшемся окне «Ок», не снимать галку «Таблица с заголовками». Если будете употреблять Power Query — у вас уже будут умные таблицы.

Встаём во вторую таблицу и на вкладке «Вставка» жмем Power View.

Power View в Excel 2016

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

Настройка отображения в Power View

Что ж, подредактируем нашу модель данных. Для начала отключим столбец «Процент», чтоб выстроить радиальную диаграмму (ну либо столбец «Кол-во голосов», если так будет удобнее). Справа в области запросов снимаем галку со столбца «Процент».

Power View в Excel 2016

Из представления здесь же пропадет столбец. Сейчас можно конвертировать таблицу в диаграмму. Для этого на вкладке «Конструирование» необходимо надавить «Иная диаграмма» и избрать вариант «Радиальная диаграмма».

Power View в Excel 2016

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

Power View в Excel 2016

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

Power View в Excel 2016

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

Power View в Excel 2016

Вот и всё! Таковым образом и будем строить наш отчёт. Во 2-ой части статьи (она будет чуток позднее) мы поработаем с таблицей по регионам и попробуем показать данные на карте. Всем фортуны!

Знакомство с Power Query

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

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

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

Но, не хотя загружать пользовательский интерфейс MS Excel 2016, для Power Query не стали создавать отдельную вкладку на ленте а втиснули его в качестве отдельной группы с очевидным заглавием Скачать & конвертировать на вкладке Данные (хотя общество юзеров MS Excel и продолжает употреблять старенькое имя — Power Query).

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию
Adblock
detector