Power query в Excel

Глава 5. Объединение листов Excel в Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава Следующая глава

В предыдущей главе были описаны методы агрегации данных из нескольких .txt или .csv файлов. В настоящей заметке описано, как объединять Таблицы или листы Excel из активной или внешней рабочей книги. К сожалению, стандартный пользовательский интерфейс Power Query этого не умеет. Но дополнительные манипуляции не будут слишком сложными. При этом методы работы с данными в активной книге отличаются от методов извлечения данных из внешнего файла.

Рис. 5.1. Доступные Таблицы в окне редактора Power Query

Скачать заметку в формате Word или pdf, примеры в формате архива

Объединение таблиц и диапазонов в текущем файле

Откройте файл с примерами Consolidate Tables—Start.xlsx. В файле три листа с информацией о подарочных сертификатах, выданных spa-салоном. Сотрудник, создавший файл, не указывал в Таблицах дату выпуска сертификатов, а размещал данные на отдельных листах по месяцам: Jan 2008, Feb2008, Mar 2008. Для анализа данные желательно объединить, и Power Query справится с этим быстрее, чем ручная обработка.

Создайте пустой запрос: пройдите по меню Данные –> Получить данные –> Из других источников –> Пустой запрос. В окне редактора Power Query в строке формул ведите (рис. 5.1):

=Excel.CurrentWorkbook()

Нажмите Enter. Вы увидите доступные объекты текущей книги: Таблицы, именованные диапазоны, подключения к внешним источникам данных. К сожалению, нельзя получить перечень листов файла.

Как вы узнали из главы 4, можно щелкнуть пробел рядом с зелеными словами в столбце Content для предварительного просмотра данных Table (рис. 5.2).

Рис. 5.2. Предварительный просмотр содержимого Table

В главе 4 вы также узнали, что кликнув на двуглавую стрелку в верхней части столбца Content, вы развернете содержимое Таблиц, сохраняя сведения из столбца Name. Итак, кликните двуглавую стрелку, в открывшемся окне настроек снимите флажок Использовать исходное имя столбца как префикс, нажмите Ok:

Рис. 5.3. Импорт и объединение данных с добавлением столбца с именем таблицы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

  • Щелкните правой кнопкой мыши столбец Name –> Замена значений
  • Заменить символ _ (подчеркивание) на ˽ 1,˽ (пробел, единица, запятая, пробел)
  • Щелкните правой кнопкой мыши столбец Name –> Тип изменения –> Дата
  • Перейдите на вкладку Преобразование –> Дата –> Месяц –> Конец месяца
  • Щелкните правой кнопкой мыши столбец Name –> Переименовать –> Конец месяца
  • Измените имя запроса на Подарочные сертификаты

Рис. 5.4. Финальный вид запроса

Перейдите на вкладку Главная и кликните Закрыть и загрузить. Объединенные данные будут размещены на новом листе Excel. К сожалению, запрос содержит кучу ошибок:

Рис. 5.5. Данные объединены на листе Excel; правда, вкрались ошибки

Не страшно. Наведите курсор мыши на запрос Подарочные сертификаты, и во всплывающем окне кликните Изменить. Снова откроется редактор Power Query. В области ПРИМЕНЕННЫЕ ШАГИ выберите первый шаг – Источник (цифра 1 на рис. 5.6). Вы заметите, что теперь у вас четыре таблицы в списке. Добавилась таблица Подарочные сертификаты, которая была создана в результате запроса. Чтобы избавиться от дублирования, необходимо добавить фильтрацию таблиц, участвующих в запросе. Кликните стрелочку возле названия столбца Name (2), выберите опцию Текстовые фильтры –> Не содержит. Подтвердите, что вы хотите вставить шаг. В окне Фильтрация строк выберите Не содержит – Подарочные (3):

Рис. 5.6. Фильтрация таблиц по имени

Нажмите Ok. В редакторе Power Query перейдите на вкладку Главная. Кликните кнопку Закрыть и загрузить. Теперь запрос содержит 62 строки; ошибок нет.

Существует и вторая возможность избавиться от ошибок – убрать дубли. Откройте редактор Power Query. Перейдите на шаг Измененный тип. Выберите столбец Name и на вкладке Главная кликните Удалить строки –> Удалить ошибки. Подтвердите, что вы хотите вставить новый шаг в середину запроса. На вкладке Главная кликните Закрыть и загрузить.

Объединение диапазонов и листов

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

Перейдите на лист Jan 2008. На вкладке Разметка страницы кликните Печатать заголовки. На закладке Лист введите A:D в поле Выводить на печать диапазон, кликните Ok.

Рис. 5.7. Выбор области печати

Повторите процедуру для листов Feb 2008 и Mar 2008. Создайте пустой запрос, и в строке формул введите: =Excel.CurrentWorkbook(). Нажмите Enter. Вы увидите список трех таблиц и трех именованных диапазонов:

Рис. 5.8. Объекты книги Excel, доступные для импорта в Power Query

Чтобы не дублировать данные, отфильтруйте столбец Name: Заканчивается на – печати. Нажмите кнопку Развернуть в верхней части столбца Content. Оставьте выбранными все столбцы, снимите флажок Использовать исходное имя столбца как префикс. Обратите внимание на отличия от предыдущего случая. Показаны все строки в выбранных столбцах диапазона печати:

Рис. 5.9. Необработанный рабочий лист

Выполним дополнительную очистку данных:

  • Главная –> Удалить строки –> Удалить верхние строки –> 2
  • Главная –> Использовать первую строку в качестве заголовков
  • Столбец Cert Number –> Фильтр –> снимите флажок c null
  • Щелкните правой кнопкой мыши столбец Cert Number –> Тип изменения –> Целое число
  • Выберите столбец Cert Number
  • Закладка Главная –> Удалить строки –> Удалить ошибки
  • Выберите столбец Cert Number. Удерживайте нажатой клавишу Shift выберите столбец Service
  • Щелкните правой кнопкой мыши один из выбранных заголовков столбцов –> Удалить другие столбцы
  • Измените имя запроса на Все листы
  • Главная –> Закрыть и загрузить

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

Агрегирование данных из других книг

Вам нужно создать список книг Excel и извлечь их содержимое, аналогично тому, что вы сделали в главе 4, когда вы извлекли содержимое файлов CSV.

Создайте новую книгу Excel. Создать новый запрос: Данные –> Получить данные –> Из файла –> Из папки. Выберите папку Source Files. В списке есть как файлы Excel, так и иные файлы:

Рис. 5.10. Файлы, доступные в папке Source Files

Нажмите Преобразовать данные, и отфильтруйте файлы Excel:

  • Щелкните правой кнопкой мыши столбец Extension –> Преобразование –> нижний регистр
  • Фильтр столбца Extension –> Текстовые фильтры –> Начинается с… –> .xlsx
  • Выберите столбцы Content имя Name –> щелкните правой кнопкой мыши –> Удалить другие столбцы

У вас может возникнуть соблазн нажать кнопку Объединить файлы…

Рис. 5.11. Объединить файл

… и, к сожалению, Power Query позволит вам это сделать. Однако, вы обнаружите, что Power Query сделает что-то весьма странное. Чтобы откатить импорт, перейдите в область ПРИМЕНЕННЫЕ ШАГИ и удалите все шаги после шага Другие удаленные столбы.

Раз вы не можете объединить и импортировать файлы простым методом, пойдем трудным способом:

  • В редакторе Power Query перейдите на вкладку Добавление столбца –> Настраиваемый столбец
  • Введите формулу =Excel.Workbook()
  • Нажмите кнопку Ok
  • Щелкните правой кнопкой мыши столбец Content –> Удалить

Новый пользовательский столбец содержит все объекты, к которым можно подключиться, включая все Таблицы Excel, именованные диапазоны и даже листы:

Рис. 5.12. Объекты, доступные для импорта

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

Рис. 5.13. Объекты, доступные для импорта

Столбец Kind показывает, что у вас есть Листы, определенное имя и Таблицы. Если не отфильтровать этот перечень объектов, у вас будет много дублей:

  • Отфильтруйте столбец Kind, оставив только Sheet
  • Отфильтруйте столбец Name, удалив файл Named Range.xlsx
  • Выберите столбцы Name, Name.1 и Data –> щелкните правой кнопкой мыши на заголовке одного из этих столбцов –> Удалить другие столбцы
  • Кликните кнопку Развернуть у заголовка столбца Data (снимите настройки префикса)

Запрос теперь выглядит следующим образом:

Рис. 5.14. Запрос с шестью импортированными объектами Sheet

Преобразуем данные:

  • Главная –> Использовать первую строку в качестве заголовков
  • Щелкните правой кнопкой мыши на заголовке столбца Workbookxlsx –> Переименовать –> Source File
  • Щелкните правой кнопкой мыши заголовке столбца Jan 2008 column –> Переименовать –> Month
  • Выберите столбец Amount –> Главная –> Удалить строки –> Удалить ошибки
  • Измените имя запроса FromExcelFiles
  • Главная –> Закрыть и загрузить

Данные загружаются в Таблицу на листе Excel. На их основе можно создать сводную таблицу, чтобы увидеть, что вы смогли извлечь из внешних файлов Excel:

Рис. 5.15. Сводная позволяет проверить, что же вы импортировали

Видно, что вы успешно извлекли данные из двух Excel-файлов, каждый из которых содержит по три листа. В общей сложности извлекли более 12 000 записей.

Подытожим

Функция Excel.CurrentWorkbook() считывает все объекты текущего файла. Поскольку она является первой в применяемых шагах запроса, вы получаете эффект рекурсии. При обновлении Power Query добавит объекты, созданные в процессе выполнения запроса, к тем, что существовали первоначально. Стратегии защиты заключается в фильтрации объектов по имени или фильтрации ошибок в ключевых столбцах. Тестируйте метод фильтрации с помощью нескольких команд Обновить все.

Функция Excel.Workbook() не вызывает проблем с рекурсией, поскольку исходные данные считываются из внешних книг, а результаты запроса сохраняются в текущей книге. Но функция Excel.Workbook() создает иную проблему: она извлекает листы в дополнение к диапазонам и таблицам. Это может приводить к дублированию данных. Обратите особое внимание на фильтрацию столбца Kind (вид), чтобы избежать этой проблемы. Даже если при построении запроса в книге имеется только один вид данных, полезно применить фильтрацию, чтобы защитить решение от будущих изменений.

Надстройки Power Query и Power Pivot

Надстройка Power Pivot

Те, кто часто пользуются сводными таблицами, понимают, насколько они удобны и эффективны в работе.
Power Pivot — это надстройка Excel, которая расширяет функционал сводных таблиц и позволяет работать с файлами больше 1 048 576 строк.

Как работает:
Запуск во всех версиях Excel от 2010 до 2016 выглядит одинаково: меню Power Pivot → Управление.

Возможности Power Pivot:

  • Создание сводных таблиц на основе нескольких таблиц Excel без формулы ВПР.
    В «обычном» Excel сложно создать одну таблицу из нескольких. В Power Pivot таблицы объединяются с помощью графического проектирования, в котором связи между несколькими таблицами создаются с помощью перетаскивания полей.
  • Импорт более 100 миллионов строк в рабочую книгу.
    Power Pivot позволяет импортировать данные из различных источников. Единственное ограничение по объему — размер файла книги, равный 2 Гбайт, и объем оперативной памяти. Благодаря алгоритму сжатия текстовый файл объемом 50 Мбайт сжимается до 4 Мбайт, занимаемых файлом с таблицей.
  • Создание улучшенных формул с помощью языка DAX.
    В Power Pivot можно писать формулы DAX в столбцах, и создавать меры — вычисления под таблицей.
    Подробнее о DAX-формулах: Основные формулы Power Pivot.

Как скачать Power Pivot

Надстройка Power Pivot для Excel 2010 года скачивается бесплатно с сайта Microsoft.

В Excel 2013 и 2016 года надстройка идет в составе программы, но только в версии Pro Plus. В других лицензиях эта надстройка недоступна.

О том, как работать с надстройкой Power Pivot, можно прочитать в статье:

  • Надстройка для Excel — Power Pivot, или жизнь после 1 048 576 строк.

Надстройка Power Query

Надстройка Power Query появилась в Excel относительно недавно, и доступна бесплатно для версий Excel 2010, 2013 года. В Excel 2016 надстройка уже встроена по умолчанию.

Новые возможности для анализа данных:

  • Несомненный плюс – из Excel можно подключаться напрямую к разным источникам данных (файлы excel, csv, текстовые файлы, базы данных, папки и так далее).
  • Данные в подключенных источниках можно преобразовывать, объединять, делать расчеты. При этом последовательность операций записывается на языке M, так что при последующих подключениях повторять расчеты еще раз не нужно – они выполнятся автоматически.

Как работает:

  • В меню Excel 2010, 2013 надстройка появляется в виде отдельной вкладки «Power Query»:

  • В Excel 2016 надстройка уже встроена по умолчанию на вкладке Данные – блок «Скачать и преобразовать».

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

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

Как скачать Power Query

Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года. Для Excel 2010 и 2013 скачивается с сайта Microsoft.

Для Excel 2016 ничего скачивать и устанавливать не нужно, так как эта надстройка идет в составе всех лицензий.

О том, как работать с надстройкой Power Query, можно прочитать в статье:

  • Начало работы в Power Query.

power-bi

/

PowerBI-book-ru

Неофициальное, открытое методическое пособие к программе Power BI и надстройкам над Excel Power Query и Power Pivot

  • Введение
    • Предисловие
    • Отказ от ответственности
    • Лицензия на использование
    • Добавление информации и внесение изменений в методическое пособие
  • Редактор запросов (Query Editor, он же Power Query)
    • Power Query в Power BI
    • Power Query в Excel 2010, 2013
    • Power Query в Excel 2016, 2019, 365
    • Зачем нужен Power Query
    • Что такое запрос (Query)
    • Действия над запросами (по правому щелчку мыши на них)
      • Duplicate (Дублировать)
      • Reference (Сослаться)
    • Шаг (Step)
    • Параметры в Power Query
    • Получение данных в Power Query
    • Типы данных в Power Query
      • Примитивные типы данных Power Query
      • Структурированные типы данных в Power Query
    • Задание типов данных для столбцов в Power Query
    • Автоматическое определение типов данных для столбцов
    • Создание дубликата столбца
    • Переименование столбцов
    • Remove Other Columns — удаление прочих столбцов
    • Split Column by Delimeter — Разделить текстовый столбец по разделителю
    • Действия над таблицами и столбцами таблиц
      • Append — добавление одной таблице к другой таблице
      • Merge — соединение данных одного запроса с другим запросом по общему ключу (аналог ВПР)
      • Соединение данных по составному ключ в Power Query
      • Команда Group by (сгруппировать по полю)
      • Добавление нового столбца в Power Query
        • if then else условия
        • Условный столбец (Conditional column)
        • Изменение типа данных у столбца с текстового на десятичный, в случае если в качестве разделителя десятичной части используется точка вместо запятой
        • Удалить дубликаты в столбцах
        • Count rows — Подсчитать количество строчек в текущей таблице
        • Извлечение шагов в отдельный запрос
        • Функция Сохранить строки (Keep Top Rows)
        • Функция Сохранить ошибки (Keer Errors)
        • Функция Заменить ошибки в столбце (Replace Errors)
    • Получение данных из различных источников
      • Получение данных из файлов
        • Получение данных из текстовых файлов (csv, tsv, txt и т.д.)
        • Извлечение данных из файлов лежащих в папке
      • Получение данных из интернета
        • Права доступа, Formula.Firewall
      • Символы разрыва строки
  • Power Pivot
    • Что такое Power Pivot в Excel
    • Что такое Power Pivot в Power BI
    • Что такое DAX
    • Модель данных
    • Таблицы
    • Функции Dax
    • Меры (Measures)
    • Быстрые меры (Quick Measures)
    • Вычисляемые Столбцы (Calculated Columns)
    • Контекст выполнения функции (Evaluation context)
    • Связь таблиц в модели данных
    • Связь таблиц по ключевому столбцу дата и time-intelligence функции
    • Часто используемые функции DAX
    • Метрики контекстной рекламы в DAX
    • Про абсолютные и относительные метрики в выгрузках
    • Формат отображения чисел в мерах и столбцах
    • Полезные ресурсы по DAX
  • Визуализации в Power BI
    • Срезы в Power BI — Слайсеры (Slicers)
      • Срезы с относительными датами (Relative slicers)
    • Настройка взаимодействия визуализаций (визуалов)
  • Настройки Power BI
    • Фоновые данные — разрешить скачивание в фоне (Background data — allow data preview)
  • Приложения
    • Горячие клавиши окна редактирования DAX формул
      • Базовое редактирование
      • Навигация
      • Поиск и замена
      • Мультикурсор и выделение
  • Вместо заключения

Введение

Предисловие

Первая версия данного методического пособия была создана Максимом Уваровым в рамках образовательного проекта NeedForData.ru. С 21.05.2018 учебное пособие опубликовано на GitHub под лицензией GPL 3.0 и с этого момента соавтором пособия может стать любой желающий.

Цель данного пособия: помочь начинающим пользователям Power BI, а также надстроек над Excel Power Query и Power Pivot, осваивать эти замечательные инструменты. Предполагается, что методическое пособие когда-нибудь станет более легковесной и человечной альтернативой текущей официальной справки по Power BI, расположенной по адресу: https://docs.microsoft.com/ru-ru/power-bi/

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

Отказ от ответственности

Авторы данного методического пособия не имеют отношения к корпорации Microsoft и ее продуктам Power BI, Excel, Power Query, Power Pivot. Авторы публикуют в данном методическом пособии собственноручно созданные учебные материалы, на публикацию которых они имеют все необходимые права.

Лицензия на использование

Данное методическое пособие может бесплатно использоваться для обучения. Методическое пособие предлагается к использованию в режиме AS-IS (как есть), под лицензией GPL 3.0. Полный текст лицензии опубликован по .

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

  1. Поставить ссылки на первоисточник
  2. Обеспечить свободный доступ к вашим материалам

Добавление информации и внесение изменений в методическое пособие

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

  1. Сделать Fork проекта
  2. Внести необходимые изменения на языке разметки в markdown
  3. Создать Pull-request в основной репозиторий

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

Редактор запросов (Query Editor, он же Power Query)

Power Query в Power BI

В программе Power BI Desktop Power Query встроенный модуль. В справке и интерфейсе этот модуль называется Query Editor или редактор запросов. Power Query это основной инструмент данных в Power BI desktop. Как показано на скриншоте ниже все четыре выделенные кнопки относятся к редактору запросов Power Query.

Power Query в Excel 2010, 2013

Также, Power Query это надстройка над MS Excel 2010 — 2013. Она устанавливается дополнительно. Скачать надстройку можно по . На панели Ribbon в Excel 2010 и 2013 Power Query посвящена отдельная вкладка.

Power Query в Excel 2016, 2019, 365

В Excel 2016 и последующих версиях Power Query встроена. Найти ее можно на вкладке Data (Данные), блок «Get and Transform». Скриншоты, как Power Query выглядит в различных версиях офиса приведены ниже:

В зависимости от версии подписки Excel функционал Power Query может различаться.

Зачем нужен Power Query

Power Query нужен для удобного преобразования данных (ETL-процесса).

Согласно википедии — ETL (от англ. Extract, Transform, Load) — процесс в управлении хранилищами данных, который включает в себя:

  • извлечение данных из внешних источников;
  • их трансформация и очистка, чтобы они соответствовали потребностям бизнес-модели;
  • и загрузка их в хранилище данных.

Power Query отлично подходит для задач:

  • подключения к разнообразным источникам (различным типам файлов, api, базам данных и т.п.);
  • для удобного и гибкого преобразования данных в необходимый формат;
  • Для создания повторяемых последовательностей обработки данных.

Что такое запрос (Query)

Запрос (Query) это программа на языке M, задающая последовательность обработки данных.

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

  • таблица — table
  • значение — value,
  • список — list,
  • запись — record и т.д.

Список из всех запросов в Excel можно увидеть в разных местах.

В списке из запросов книги (Queries Pane) интерфейсе Excel 2016:

В интерфейсе самого Power Query:

У каждого запроса есть свое имя.

Имя запроса можно увидеть в нескольких местах. Там же его можно изменить:

В интерфейсе Excel 2016.

В интерфейсе Power Query.

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

В случае, если имя запроса содержит пробелы (например, состоит из нескольких слов), то при обращении к этому запросу из других запросов он начинается с # и заключается в кавычки:

#»имя запроса»

Действия над запросами (по правому щелчку мыши на них)

Duplicate (Дублировать)

Команда Duplicate позволяет создать новый запрос и продублировать в нем все шаги исходного запроса (т.е. при дублировании появляется новый запрос с #»Имя (2)», в котором содержатся все шаги из исходного запроса). Новый запрос, созданный при использовании команды Duplicate, никак не связан с оригинальным запросом.

Reference (Сослаться)

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

Шаг (Step)

Отдельный этап обработки данных в рамках конкретного Запроса.

Запрос состоит из шагов и включает как минимум один шаг.

На каждое примененное действие в интерфейсе создается новый шаг.

Список шагов конкретного запроса можно посмотреть в правой части экрана в панели настроек Запроса.

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

Чтобы посмотреть формулу конкретного шага необходимо включить отображение строки формул на вкладке view и выбрать интересующий шаг в панели «Applied Steps».

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

А также перетаскивая шаги в списке.

Параметры в Power Query

  • 🇺🇲Статья о параметрах Power Query

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

Чтобы начать работать с Power Query, необходимо настроить получение данных из какого-либо источника. Сделать это можно из интерфейса Power Query в Power BI по нажатию на кнопку Get Data.

В Excel 2010-2013 сделать это можно нажав на кнопки с указанием различных источников на панели Ribbon.

А также из интерфейса Power Query в Excel.

Типы данных в Power Query

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

Примитивные типы данных Power Query

  • Decimal number — десятичное число Number.Type или type number
  • Time — время Time.Type или type time
  • Date — дата Date.Type или type date
  • Date / Time — дата / время DateTime.Type или type datetime
  • Date / Time / Timezone — дата / время / часовая зона DateTimeZone.Type или type datetimezone
  • Duration — длительность Duration.Type или type duration
  • Fixed Decimal number — десятичное округленное до 4 знака Currency.Type
  • Whole number — целое число Int64.Type
  • Text — текст Text.Type или type text
  • True/False — истина / ложь
  • Binary — двоичный код (например, изображение в формате bmp)
  • Percentage — проценты Percentage.Type

Структурированные типы данных в Power Query

Задание типов данных для столбцов в Power Query

Присвоенные отдельным столбцам типы данных определяют операции, которые применимы к ним. Если выбрать столбец, то примененные к нему типы данных можно увидеть в следующих местах:

  • Типы данных столбцов таблицы обозначаются иконками в области заголовков:
  • При выбранном столбце, его тип данных в Power BI и Excel на вкладке «Home»
  • При выбранном столбце, его тип данных в Power BI и Excel на вкладке «Transform»

Автоматическое определение типов данных для столбцов

В Power Query есть функция «автоматически определять типы данных». При помощи данной функции Power Query будет подбирать тип данных к столбцу на основ первой тысячи строчек конкретного столбца.

Создание дубликата столбца

Команда на Ribbon:

Команда в контекстном меню:

Переименование столбцов

Чтобы переименовать столбец нужно дважды щелкнуть на его названии:

Remove Other Columns — удаление прочих столбцов

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

Для этих целей отлично работает команда «remove other columns» (удалить прочие столбцы).

Split Column by Delimeter — Разделить текстовый столбец по разделителю

Команду «разделить столбец по разделителю» можно найти в нескольких местах:

Кнопка на Ribbon — Split Column

В контекстном меню, по щелчку на заголовок столбца.

Разделить столбец по произвольному разделителю

Указать максимальное количество столбцов

Действия над таблицами и столбцами таблиц

Append — добавление одной таблице к другой таблице

Из интерфейса Power Query:

Из интерфейса Excel:

Merge — соединение данных одного запроса с другим запросом по общему ключу (аналог ВПР)

Начало операции из интерфейса Power Query:

Начало операции merge из интерфейса Excel:

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

Соединение данных по составному ключ в Power Query

После нажатия на кнопку OK мы видим новый столбец с кнопкой

Нажимаем на кнопку, раскрываем столбец и выбираем желаемую операцию

Expand — развернуть данные из выбранных столбцов

Aggregate — подсчитать данные в конкретных столбцах

Важно помнить что типы данных у ключевых столбцов (в обеих таблицах) должны быть одинаковыми.

Команда Group by (сгруппировать по полю)

Команду можно вызвать по клику на кнопку на панели Ribbon

Также команду можно вызвать из контекстного меню (если нажать правой кнопкой на заголовке столбца)

Интерфейс команды Group By с комментариями представлен на скриншоте ниже:

Добавление нового столбца в Power Query

if then else условия

Для выбора действия в зависимости от условия в Power Query используется структура с оператором if then else

Пример:

if 0 then else

Условный столбец (Conditional column)

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

Пояснения к мастеру конфигурации условного столбца показаны на скриншоте ниже.

Для сведения — задаваемые условия в интерфейсе мастера пишут команду скрипта содержащего операторы if then else. Это значит, что первым будет проверено первое условие в мастере условного столбца. Все множество значений будет проверено по этому условию. В случае, если будут найдены результаты удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия. Для остальных значений из множества, будет проверено следующее условие. И так далее, до тех пор, пока не будут проверенны все условия. Если для элементов множества не будет выполненно ни одно условие, то в условный столбец попадет значение из поля «В противном случае / (otherwise)».

При создании условий важно помнить, операции сравнения включающие type null и другие типы данных, возвращают ошибку в качестве результата. Подробнее об этом можно прочитать в блоге у Максима Зеленского

Изменение типа данных у столбца с текстового на десятичный, в случае если в качестве разделителя десятичной части используется точка вместо запятой
  1. Необходимо щелкнуть правой кнопкой на заголовке столбца
  2. Выбрать пункт «Change type»
  3. И далее выбрать пункт «Using locale»
  4. Выбираем страну, где в качестве разделителя используется точка (например, USA)
Удалить дубликаты в столбцах

Команда «Remove Duplicates» проходит по выбранным столбцам (если выбрана вся таблица, то по всей таблице) и смотрит в них повторяющиеся ячейки (строчки, в случае если выбрана таблица). Если дубликаты найдены функция оставляет первую попавшуюся уникальную строчку и удаляет все последующие повторяющиеся Найти команду можно на Ribbon — Home — Remove Duplicates (Удалить дубликаты в выбранных столбцах)

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

Удалить дубликаты строк в таблице можно нажав на кнопку в левом верхнем углу таблицы предпросмотра.

Аналогичного результата можно добиться если использовать команду «Group By»

Count rows — Подсчитать количество строчек в текущей таблице
Извлечение шагов в отдельный запрос

Для выполнения необходимо щелкнуть правой кнопкой на конкретном шаге обработки. Выбрать пункт меню «Extract previous steps»

ввести имя нового запроса, который будет создан на основе предыдущих шагов

Функция Сохранить строки (Keep Top Rows)
Функция Сохранить ошибки (Keer Errors)
Функция Заменить ошибки в столбце (Replace Errors)

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

Получение данных из различных источников

Получение данных из файлов

Получение данных из текстовых файлов (csv, tsv, txt и т.д.)

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

Извлечение данных из файлов лежащих в папке

Выбираем в качестве типа источника папку. Далее выбираем конкретную папку с файликами, которые предполагается объединить. Файлики должны быть одного типа и с одинаковыми столбцами.

В появившемся окошке предпросмотра данных жмем на кнопочку edit.

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

Права доступа, Formula.Firewall

Текст взят из Power bi formula firewall privacy settings — marketing-wiki.ru

При работе в Power BI, при обращении к внешним источникам данных вроде различных API могут возникать ошибки вроде: OLE DB or ODBC error:

Это ошибки, которые возникают из-за встроенного в Power BI Fomrula.Firewall — механизма, который следит, чтобы данные из Power BI передавались только согласно выставленным правилам доступа.

то есть Power Bi пытается защитить нас, чтобы мы случайно не отправили какие-либо данные (вроде токена) на сервер-злоумышленника.

Однако, если мы работаем с API, то нам неминуемо нужно отправлять данные в интернет. Соответственно, чтобы не иметь проблем в этом процессе проще всего в настройках Power BI выключить Formula.Firewall. Это делается в разделе Privacy. Нужно выбрать 3-й пункт — «ignore privacy level settings»

Символы разрыва строки

При помощи операции найти или для разделения ячейки на отдельные строчки можно воспользоваться символами #(cr)#(lf) либо воспользоваться функцией Lines.FromText

Power Pivot

Что такое Power Pivot в Excel

Power Pivot — это надстройка над Excel, представляющая из себя быструю колоночную базу данных VertiPaq с языком запросов DAX (часто вместо VertiPaq говорят Power Pivot). В отличие от Excel число строк загруженных в Power Pivot ограничено лишь размером доступной оперативной памяти компьютера. Быстродействие Power Pivot во много раз превосходит быстродействие формул в Excel. Также Power Pivot по производительности превосходит и Power Query (при этом часто он потребляет меньше ресурсов). Результаты выполнения запросов доступны пользователям в сводных таблицах и сводных диаграммах MS Excel. Таким образом сводные таблицы выступают аналитическим интерфейсом к данным хранящимся в Power Pivot.

Что такое Power Pivot в Power BI

В Power BI Power Pivot встроен как база данных, к которой присоединяются различные визуализации.

Что такое DAX

Dax (Data Analysis Expressions) — это язык программирования использующийся для запросов в базе данных VertiPaq.

Модель данных

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

Таблицы

Таблицы — совокупность строк, разделённых на столбцы.

У каждого столбца задан тип данных (который, как правило, наследуется из типов данных заданных для столбцов в Power Query).

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

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

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

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

‘название с пробелом’

Однако, если мы заключим в кавычки название таблицы без пробелов, то все будет работать как предполагалось:

‘название’

Функции Dax

Функции языка Dax похожи на функции Excel, с той лишь разницей, что в качестве аргументов используют столбцы, целые таблицы или скалярные выражения (простые значения), а не ячейки.

В качестве разделителей аргументов в зависимости от локали используются:

  • «;» (и «,» для десятичных)
  • «,» (и «.» для десятичных)

Меры (Measures)

Вычисляются только в момент использования. Рассчитываются в рамках текущего контекста фильтров. Именно это свойство позволяет строить интерактивные визуализации, которые фильтруются при нажатии на определенные области конкретных визуализаций. Меры хоть и принадлежат конкретной таблице, но могут быть перенесены в любую другую таблицу в рамках документа. Поэтому хорошая практика при использовании мер формулах не включать название таблицы, в которо й мера лежит. То есть вместо ‘таблица’ писать просто .

Быстрые меры (Quick Measures)

Функционал быстрых мер позволяет создавать сложные DAX формулы без написания кода. Вместо написания кода нужно сконфигурировать желаемую меру в графическом интерфейсе. Подробнее о быстрых мерах можно прочитать в справке по Power BI

Вычисляемые Столбцы (Calculated Columns)

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

Подробнее про столбцы и меры читать здесь:

  • Calculated Columns and Measures in DAX — SQLBI

Контекст выполнения функции (Evaluation context)

В Power Pivot существуют два контекста выполнения формулы, которые действуют одновременно:

  • Контекст фильтров (Filter context)
  • Контекст строк (Row context)

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

  • Марко Руссо и Альберто Феррари https://www.sqlbi.com/.
  • Справка Microsoft Power BI: Основные сведения о DAX в Power BI Desktop

Связь таблиц в модели данных

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

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

Справка: Создание связей и управление ими в Power BI Desktop — Power BI | Microsoft Docs

Связь таблиц по ключевому столбцу дата и time-intelligence функции

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

Часто используемые функции DAX

SUM (Столбец) — Cумма чисел по столбцу

COUNTA (Столбец) — Количество значений в столбце

DISTINCTCOUNT (Столбец) — Количество уникальных значений в столбце

SUMX (Таблица, Выражение) — Сумма значений выражения, которое выполняется для каждой строчки таблицы

DIVIDE (Значение числителя, значение знаменателя, альтернативный вариант в случае ошибки деления числителя на знаменатель) — Безопасное деление

IFERROR (Значение, Значение если ошибка) — Если ошибка

IF (Логическое выражение, значение если правда, значение если ложь ) — Если

Метрики контекстной рекламы в DAX

CTR (Кликабельность)

= SUM ( Клики ) / SUM ( Показы )

CPC (Цена клика)

= SUM ( Расход ) / SUM ( Клики )

Ставка (Максимальная цена клика установленная рекламодателем)

Ставка Средняя

= AVERAGE ( ставка )

Ставка СреднеВзвешенная на клики

= SUMX ( ставка * клики ) / SUM ( клики )

Ставка СреднеВзвешенная на показы

= SUMX ( ставка * показы ) / SUM (показы)

CR (Коэффициент конверсии фактический)

= SUM ( транзакции ) / SUM ( сессии )

Ключевая фраза: количество

= COUNTA ( ключевая фраза )

Ключевая фраза: количество уникальных

= DISTINCTCOUNT ( ключевая фраза )

Про абсолютные и относительные метрики в выгрузках

Средний показатель отказов рассчитывается по формуле:

= SUM ( отказы ) / SUM ( визиты )

Если в выгрузке нет абсолютного числа ОТКАЗОВ, но есть ПОКАЗАТЕЛЬ ОТКАЗОВ, то для каждой строчки с исходными данными предварительно необходимо рассчитать абсолютное число ОТКАЗОВ. Для этого нужно умножить ПОКАЗАТЕЛЬ ОТКАЗОВ на ЧИСЛО ВИЗИТОВ. После этого у вас появится возможность рассчитывать средний показатель отказов корректно.

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

Распространенная ошибка рассчитывать СРЕДНИЙ ПОКАЗАТЕЛЬ ОТКАЗОВ в качестве встроенной меры AVERAGE по столбцу ПОКАЗАТЕЛЬ ОТКАЗОВ (см скрин. http://bit.ly/2JMKSl1).

Так средний показатель отказов рассчитывать некорректно.

Формат отображения чисел в мерах и столбцах

Формат отображения значений мер задается при выбранной мере, на вкладке «Modeling», в блоке «Formating»

Полезные ресурсы по DAX

Визуализации в Power BI

Срезы в Power BI — Слайсеры (Slicers)

  • Справка

Срезы с относительными датами (Relative slicers)

Настройка взаимодействия визуализаций (визуалов)

  • Справка

Настройки Power BI

Фоновые данные — разрешить скачивание в фоне (Background data — allow data preview)

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

  1. Параметр Allow Data Preview To Download In The Background в Power Query и Power BI — статья Криса Вебба: перевод 🇷🇺, оригинал 🇺🇲.
  2. Ветка, в предложения выключить эту настройку по умолчанию uservoice.

Приложения

Горячие клавиши окна редактирования DAX формул

Базовое редактирование

Клавиша Описание
Ctrl+X Вырезать строку (Пустое выделение)
Ctrl+C Скопировать строку (Пустое выделение)
Alt+ Alt+↓ Перемещает текущую строку вверх / вниз
Shift+Alt+ Shift+Alt+↓ Копирует строку и вставляет её выше / ниже
Ctrl+Shift+K Удаляет строку
Shift+Enter Добавить новую строку ниже под курсором
Ctrl+] Ctrl+[ Увеличить / уменьшить отступ строки
Tab Shift+Tab Увеличить / уменьшить отступ строки
Home End Перейти в начало / конец строки
Ctrl+Home Ctrl+End Перейти в начало / конец DAX формулы
Ctrl+K Ctrl+C Ctrl+K Ctrl+U Поставить / убрать однострочный комментарий
Ctrl+/ Поставить или убрать однострочный комментарий
Shift+Alt+A Поставить или убрать однострочный комментарий

Навигация

Клавиша Описание
Ctrl+G Перейти к строке
F8 Shift+F8 Перейти к ближайшей / следующей ошибке или предупреждению

Поиск и замена

Клавиша Описание
Ctrl+D Выделить слово под курсором целиком, при повторном нажатии выделяет аналогичные слова по тексту
Ctrl+K Ctrl+D Перейти к следующему совпадающему выделению### Мультикурсор и выделение

Мультикурсор и выделение

Клавиша Описание
Ctrl+A Выделить все
Alt+Клик Добавить курсор к выделению
Ctrl+Alt+ Ctrl+Alt+↓ Добавить мультикурсор выше / ниже
Ctrl+U Убрать последнее выделение курсора
Ctrl+F2 Выделяет все вхождения слова
Ctrl+Shift+L Выделяет все вхождения выделенного текста
Shift+Alt+→ Shift+Alt+← Расширить / уменьшить текущее выделение по строке

Вместо заключения

Данное методическое пособие было выложен в открытый доступ 21.05.2018. С тех пор каждый желающий способен внести в него правки или дополнить его собственной актуальной информацией.

Сделать это можно по адресу: https://github.com/power-bi/PowerBI-book-ru

Upd: по состоянию на 30-09-2018 в методическом пособии появилась статья от @DmitriyVlasov про шпаргалку с горячими клавишами

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

14877 16.07.2018

Термины «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 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.

Ссылки по теме

  • Проектная диаграмма Ганта в Excel с помощью Power Query
  • Как создать в Excel базу данных с помощью Power Pivot
  • Визуализация движения по маршруту на карте в Power Map

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

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над 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 умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

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

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

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

Загрузка данных из Яндекс.Метрики, 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.
Вот часть задач, которые я делаю с использованием Power Query:

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

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

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

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

Официальные ресурсы на английском:

  1. Справка по формулам языка M
  2. Ветка Форума Microsoft Technet про Power Query

На момент написания статьи на русском языке информации по Power Query практически нет, и то что мной найдено приведено ниже:

  1. Импорт данных из таблиц в Google Spreadsheets
  2. Power BI. Получаем данные из REST API
  3. Сообщество продвинутых пользователей Excel, Power Pivot и Power Query в Facebook
  4. Обновляемая страничка в Marketing-wiki про Power Query

На английском:

  1. Видео с демонстрацией возможностей Power Query на конференции TechEd North America
  2. Блог Chris Webb, пожалуй наиболее полный ресурс с примерами использования Power Query
  3. Книга Chris Webb — Power Query for Power BI and Excel
  4. Блог Ken Puls
  5. Курс «Excel для интернет-маркетинга»
  6. Книга Ken Puls и Miguel Escobar — M is for Data Monkey

About the author

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *