Book design is the art of incorporating the content, style, format, design, and sequence of the various components of a book into a coherent whole. In the words of Jan Tschichold, "methods and rules upon which it is impossible to improve, have been developed over centuries. To produce perfect books, these rules have to be brought back to life and applied."
Front matter, or preliminaries, is the first section of a book, and is usually the smallest section in terms of the number of pages. Each page is counted, but no folio or page number is expressed, or printed, on either display pages or blank pages.

markdownlint-disable MD045 MD013 MD040

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

Введение

Предисловие

Первая версия данного методического пособия была создана Максимом Уваровым в рамках образовательного проекта 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 Power Query встроенный модуль. В справке и интерфейсе этот модуль называется Query Editor или редактор запросов. Это основной путь загрузки данных в 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

В Excel 2016 Power Query уже встроена. Найти ее можно на вкладке Data (Данные), блок “Get and Transform”.

Что такое Power Query и как его найти в Excel 365

В Excel 365 Power Query уже встроена. Найти ее можно на вкладке Data (Данные), блок “Get and Transform”.

В зависимости от версии подписки 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 BI.

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

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

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

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

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

Какие типы данных существуют в Power Query

На момент написания данного пособия в Power Query были следующие типы данных:

  • Decimal number - десятичное число
  • Fixed Decimal number - десятичное округленное до 4 знака
  • Whole number - целое число
  • Date / Time - дата / время
  • Date - дата
  • Time - время
  • Date / Time / Timezone - дата / время / часовая зона
  • Duration - длительность
  • Text - текст
  • True/False - истина / ложь
  • Binary - двоичный код (например, изображение в формате bmp)

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

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

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

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

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

Команда на Ribbon:

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

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

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

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

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

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

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

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

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

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

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

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

Операции над таблицами в Power Query

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Команда 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»

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

Права доступа, 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”

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 позволяет этого не делать.

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

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

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

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

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

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

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

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

Справка: Создание связей и управление ими в 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”

Формат отображения значений мер в Power BI
Формат отображения значений мер в Power BI

Полезные ресурсы по 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 про шпаргалку с горячими клавишами

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

Made on
Tilda