Руководство по power bi на русском языке

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

• Table — таблица #table({"Заголовок1", "Заголовок2"}, { {"Строка1Столбец1", "Строка1Столбец2"}, {"Строка2Столбец1", "Строка2Столбец2"} })
• List — список {1,2,4} - список из элементов Чисел со значениями 1, 2, 4
• Record — запись [field1 = "текст в кавычках", field2 = "текст в кавычках2"]

Задание типов данных для столбцов в 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 [столбец1] 0 then [столбец2] else [столбец3]

Условный столбец (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: [information is needed in order to combine data]

или Formula.Firewall: Query is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination

Это ошибки, которые возникают из-за встроенного в 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 ( Клики )

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• sqlbi.com/articles/calculated-columns-and-measures-in-dax/
• powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns/
• Клевый курс на udemy про Power Pivot
• Книга: The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI (Business Skills) 1st Edition
• Daxpatterns.com
• Канал Curbal на youtube с подробными обучающими видео DAX Fridays

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

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

• Справка

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

• https://docs.microsoft.com/ru-ru/power-bi/desktop-slicer-filter-date-range

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

• Справка

Настройки Power BI

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

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

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

Приложения

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

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

Навигация

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

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

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

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

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

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

[мера]: [information is needed in order to combine data]: [столбец3]: [столбец2]: [столбец1]:

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

Кратко о возможностях Microsoft Power BI

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

• Power BI Desktop — предназначен для разработки модели данных и отчетов;
• Power BI Service — онлайн-аналог, который специализируется на мониторинге и анализе готовых отчетов, поэтому возможности конструирования и работы с данными там очень ограничены.

Как правило, декстопная версия используется в качестве конструктора, после чего разработанный файл публикуется в Power BI Service. Скачать Power BI Desktop можно по этой ссылке.

Откуда можно загружать данные?

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

1. Группа «Файл»:

• Excel;
• CSV;
• XML;
• Текст;
• JSON;
• Папка.

2. Группа «База данных»:

• SQL Server;
• Access;
• SQL Server Analysis Service;
• Oracle;
• IBM DB2;
• MySQL;
• PostgreSQL;
• Sybase;
• Teradata;
• SAP HANA.

3. Группа «Azure»:

• База данных Microsoft Azure SQL;
• Microsoft Azure Marketplace;
• Microsoft Azure HDInsight;
• Хранилище BLOB-объектов;
• Табличное хранилище Microsoft Azure;
• Azure HDInsight Spark;
• Microsoft Azure DocumentDB;
• Хранилище озера данных Microsoft Azure.

4. Группа «Другое»:

• Интернет;
• Список SharePoint;
• Канал OData;
• Файл Hadoop;
• Active Directory;
• Microsoft ExChage;
• Dynamics CRM online;
• Facebook;
• Google Analytics;
• Объекты Salesfore;
• Отчеты Salesforce;
• ODBC;
• R-скрипт;
• appFigures;
• GitHub;
• MailChimp;
• Marketo;
• QuickBook Online;
• Smartsheets;
• SQL Sentry;
• Stripe;
• SweetIQ;
• Twilio;
• Zendesk;
• Spark;
• Пустой запрос.

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

Какие визуализации можно построить в Power BI?

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

• линейчатая диаграмма с накопление;
• гистограмма с накоплением;
• линейчатая диаграмма с группировкой;
• гистограмма с группировкой;
• нормированная линейчатая диаграмма;
• нормированная гистограмма;
• график;
• диаграмма с областями;
• диаграмма с областями с накоплением;
• линейная гистограмма и гистограмма с накоплением;
• линейная гистограмма и гистограмма с группировкой;
• каскадная диаграмма;
• точечная диаграмма;
• круговая диаграмма;
• диаграмма дерева;
• карта;
• таблица;
• матрица;
• заполненная карта;
• воронка;
• датчик;
• многострочная карточка;
• карточка;
• ключевой показатель эффективности;
• срез;
• кольцевой график;
• визуальный элемент r-script (на данный момент включается в параметрах программы).

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

Как построить отчёт в Power BI Desktop?

1. Установите связь Power BI с Google Analytics

1.1. Приступим непосредственно к загрузке информации из представления Google Analytics. На вкладке «Главная» в группе «Внешние данные» жмем на кнопку «Получить данные»

После чего в диалоговом окне «Получить данные» в группе «Другое» выбираем сервис «Google Analytics» и жмем «Подключить».

1.2. Далее Power BI запросит вход в аккаунт Google.

1.3. Последний шаг — предоставить разрешение на просмотр данных в нужном представлении Google Analytics.

1.4. После клика по кнопке «Разрешить» мы автоматически возвращаемся в окно «Учетная запись Google», где будет оповещение, что вы вошли в систему. Жмем на кнопку «Подключение».

В открывшимся окне «Навигатор» вы увидите список всех доступных аккаунтов, ресурсов и представлений Google Analytics. Нужно отметить, что в Power BI, как и при любом API запросе в Google Analytics, существует ограничение на максимальное количество запрашиваемых параметров (не более семи) и показателей (не более десяти).

1.5. Для того, чтобы загрузить выбранные данные в модель данных Power BI, жмем кнопку «Загрузить», которая располагается в нижнем правом углу окна «Навигатор».

Теперь загруженный набор данных отображается в области полей, и мы можем строить на основе этой информации любой доступный визуальный элемент.

2. Как загрузить данные из MySQL в Power BI?

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

2.1. Для загрузки данных о продажах из MySQL, необходимо воспользоваться командой «Получить данные», которая находится на вкладке «Главная».

2.2. В диалоговом окне «Получить данные» в группе «База данных» выбираем пункт «База данных MySQL».

2.3. После клика по кнопке «Подключить» в окне «База данных MySQL» вводим IP сервера, имя базы данных и текст SQL запроса. Если вы не знакомы с SQL и хотите просто выбрать некоторые таблицы из базы данных целиком, то вводить SQL запрос не следует, на следующем шаге у вас будет возможность выбора таблицы.

2.4. Далее вводим учетные данные для доступа в MySQL и жмем кнопку «Подключение».

2.5. На этом этапе подключение к MySQL серверу установлено. В случае, если вы ранее прописали SQL скрипт, то результат его работы будет загружен в модель данных. Поскольку мы не указывали запрос, в левой части диалогового окна «Навигатор» появится список доступных таблиц из указанной ранее базы данных. Ставим галочку напротив названия таблицы продаж и жмем «Загрузить».

Теперь в модель данных Power BI загружены данные из двух источников: Google Analytics и MySQL.

3. Как установить связи между таблицами в Power BI

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

В нашем наборе данных существует три параметра, по которым необходимо настроить связи между таблицами «Default Channel Group», «YearMonth», «UserType». Соответственно необходимо создать три одноименных справочника, которые будут содержать список всех уникальных элементов каждой из перечисленных категорий. В качестве примера мы загрузим список уникальных элементов по каждому полю из соответствующих CSV файлов.

3.1. Процесс загрузки CSV файлов в Power BI так же прост, как и описанные раннее подключения к Google Analytics и MySQL: жмем кнопку «Получить данные», в группе «Файл» выбираем «CSV» и по очереди загружаем в модель данных три CSV файла.

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

3.2. На данном этапе можно переходить к процессу создания связей между таблицами. В Power BI существует два способа определения связей между таблицами: в визуальном режиме и с помощью диалогового окна «Управление связями». Чтобы создать связь в режиме визуализации модели данных, нужно перетащить с помощью мыши поле из одной таблицы в ту, с которой хотим создать связь.

Модель данных в итоге должна выглядеть следующим образом. Как видите, таблицы «Данные из GA» и «Данные из MySQL» теперь связаны между собой через справочники.

4. Как построить визуализацию в Microsoft Power BI?

4.1. Чтобы создать визуализацию, вернемся в режим «Отчет», воспользовавшись одноименной кнопкой в меню, расположенном в левой части окна Power BI.

4.2. Далее построим диаграмму, на которой совместим данные из разных источников: из Google Analytics будут отображены данные о количестве сеансов, из MySQL — о количестве продаж.
В качестве элемента визуализации будем использовать вид «Линейная гистограмма и гистограмма с накоплением».

Перетягиваем поле «month» из таблицы «yearMonth» в область «Общая ось».

Далее перетягиваем поле «Session» из таблицы «Данные из GA» в область «Значения столбцов». И в завершение перетягиваем поле «sales» из таблицы «Данные из MySQL» в область «Значения строк».

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

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

5. Как настроить фильтры данных в Power BI?

Для более удобной работы с фильтрами данных следует добавить на рабочий лист три среза. Для этого перетаскиваем с помощью мыши в пустое место области отчетов поля «userType», «yearMonth» и «Channel».
После чего по очереди выделяем мышкой каждый из этих объектов и переключаем в режим «Срез».

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

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

6. Как опубликовать информацию на Power BI Service?

Для этого достаточно быть зарегистрированным пользователем данной службы и нажать кнопку «Опубликовать».

После чего начинается процесс публикации файла в службе Power BI. Если вы все сделали правильно, то получите сообщение, что файл был успешно опубликован.

Заключение

В статье было кратко рассказано:

1. Как загрузить в программу нужный набор данных (на примере интеграции с Google Analytics и загрузки данных из MySQL).
2. Как упорядочить данные, чтобы привести их к нужному для визуализации виду.
3. Как построить отчет в Power BI, где будут отображаться данные из разных источников.
4. Как настроить фильтры данных, выбрать подходящие элементы визуализации и опубликовать результат для просмотра в онлайн-версии Power BI.