Неофициальное, открытое методическое пособие к программе Power BI и надстройкам над Excel Power Query и Power Pivot
- Введение
- Редактор запросов (Query Editor, он же Power Query)
- Что такое Power Query и как его найти в Power BI
- Что такое Power Query и как его найти в Excel 2010-2013
- Что такое Power Query и как его найти в Excel 2016
- Зачем нужен Power Query?
- Что такое запрос (Query)
- Действия над запросами (по правому щелчку мыши на них)
- Duplicate (Дублировать)
- Reference (Сослаться)
- Шаг (Step)
- Загрузка данных из различных источников в Power Query
- Назначение типов данных для столбцов в Power Query
- Какие типы данных существуют в Power Query?
- Автоматическое определение типов данных
- Переименование столбцов
- Remove Other Columns - удаление прочих столбцов
- Split Column by Delimeter - Разделить текстовый столбец по разделителю
- Операции над таблицами в Power Query
- Append - добавление одной таблице к другой таблице
- Merge - соединение данных одного запроса с другим запросом по общему ключу (аналог ВПР)
- Соединение данных по составному ключ в Power Query
- Извлечение данных из файлов лежащих в папке
- Команда Group by (сгруппировать по полю)
- Добавление нового столбца в Power Query
- Условный столбец (Conditional column)
- Изменение типа столбца с текстового на десятичный, в случае если в качестве разделителя десятичной части используется точка вместо запятой
- Удалить дубликаты в столбцах
- Извлечение шагов в отдельный запрос
- Права доступа, 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
- Приложения
- Вместо заключения
Первая версия данного методического пособия была создана Максимом Уваровым в рамках образовательного проекта 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. Полный текст лицензии опубликован по ссылке.
Согласно лицензии, если вы используете материалы данного учебного пособия в своей документации или своих учебных пособиях, то вы обязательно должны (включая, но не ограничиваясь):
- Поставить ссылки на первоисточник
- Обеспечить свободный доступ к вашим материалам
Любой желающий может предложить исправления текущего методического пособия на Github. Для этого нужно:
- Сделать Fork проекта
- Внести необходимые изменения на языке разметки в markdown
- Создать Pull-request в основной репозиторий
Позже члены редколлегии методического пособия смогут рассмотреть ваши предложения.
В Power BI Power Query встроенный модуль. В справке и интерфейсе этот модуль называется Query Editor или редактор запросов. Это основной путь загрузки данных в Power BI desktop. Как показано на скриншоте ниже все четыре выделенные кнопки относятся к редактору запросов Power Query.
Также, Power Query это надстройка над MS Excel 2010 - 2013. Она устанавливается дополнительно. Скачать надстройку можно по ссылке. На панели Ribbon в Excel 2010 и 2013 Power Query посвящена отдельная вкладка.
В Excel 2016 Power Query уже встроена. Найти ее можно на вкладке Data (Данные), блок "Get and Transform".
В зависимости от версии подписки Excel функционал Power Query может различаться.
Power Query нужен для удобного преобразования данных (ETL-процесса).
Согласно википедии - ETL (от англ. Extract, Transform, Load) — процесс в управлении хранилищами данных, который включает в себя:
- извлечение данных из внешних источников;
- их трансформация и очистка, чтобы они соответствовали потребностям бизнес-модели;
- и загрузка их в хранилище данных.
Power Query отлично подходит для задач:
- подключения к разнообразным источникам (различным типам файлов, api, базам данных и т.п.);
- для удобного и гибкого преобразования данных в необходимый формат;
- Для создания повторяемых последовательностей обработки данных.
Запрос (Query) это программа на языке M, которая представляет собой последовательность обработки данных.
В своем теле запрос может обращаться к неограниченному количеству источников данных (включая другие запросы). В результате выполнения запроса на выходе получается:
- таблица - table
- значение - value,
- список - list,
- запись - record и т.д.
Список из всех запросов в Excel можно увидеть в разных местах.
В списке из запросов книги (Queries Pane) интерфейсе Excel 2016:
В интерфейсе самого Power Query:
У каждого запроса есть свое имя.
Имя запроса можно увидеть в нескольких местах. Там же его можно изменить:
В интерфейсе Excel 2016.
В интерфейсе Power Query.
По имени запроса можно обращаться к результатам этого запроса из других запросов.
В случае, если имя запроса содержит пробелы (например, состоит из нескольких слов), то при обращении к этому запросу из других запросов он начинается с # и заключается в кавычки:
#"имя запроса"
Команда Duplicate позволяет создать новый запрос и продублировать в нем все шаги исходного запроса (т.е. при дублировании появляется новый запрос с #"Имя (2)", в котором содержатся все шаги из исходного запроса). Новый запрос, созданный при использовании команды Duplicate, никак не связан с оригинальным запросом.
Создать новый запрос, в первом шаге которого обратиться по имени к оригинальному запросу.
Отдельный этап обработки данных в рамках конкретного Запроса.
Запрос состоит из шагов и включает как минимум один шаг.
На каждое примененное действие в интерфейсе создается новый шаг.
Список шагов конкретного запроса можно посмотреть в правой части экрана в панели настроек Запроса.
Каждый шаг это отдельная переменная, расположенная на отдельной строчке кода в скрипте запроса на языке программирования M.
Чтобы посмотреть формулу конкретного шага необходимо включить отображение строки формул на вкладке view и выбрать интересующий шаг в панели "Applied Steps".
Очередность шагов можно менять через интерфейс, используя контекстное меню.
А также перетаскивая шаги в списке.
Для того, чтобы начать работать с Power Query необходимо получить данные из какого-либо источника. Сделать это можно из интерфейса Power Query в Power BI.
В Excel 2010-2013 сделать это можно нажав на кнопки с указанием различных источников на панели Ribbon.
А также из интерфейса Power Query в Excel.
Присвоенные отдельным столбцам типы данных определяют операции, которые применимы к ним. Если выбрать столбец, то примененные к нему типы данных можно увидеть в следующих местах:
- В Power BI типы данных обозначаются иконками в столбцах:
- В Power BI и Excel на вкладке «Home»
- В Power BI и Excel на вкладке «Transform»
На момент написания данного пособия в 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 - запись
В Power Query есть возможность автоматически определять типы данных. Она будет пытаться сделать это по тысяче первых строчек конкретного столбца.
Создание дубликата столбца
Контекстное меню:
Чтобы переименовать столбец нужно дважды щелкнуть на его названии:
Для повышения личной эффективности (за счет лучшего фокуса на конкретных цифрах), сохранения оперативной памяти и поддержки быстродействия моделей нужно стараться оставлять в модели только необходимые данные (только необходимые столбцы и строчки).
Для этих целей отлично работает команда "remove other columns" (удалить прочие столбцы).
Команду разделить столбец по разделителю можно найти в нескольких местах:
Кнопка на Ribbon - Split Column
В контекстном меню, по щелчку на заголовок столбца.
Разделить столбец по произвольному разделителю
Указать максимальное количество столбцов
Из интерфейса Power Query:
Из интерфейса Excel:
Начало операции из интерфейса Power Query:
Начало операции merge из интерфейса Excel:
Выбор таблицы, из которой будем подтягивать данные, определение ключевых столбцов и типа операции
После нажатия на кнопку OK мы видим новый столбец с кнопкой
Нажимаем на кнопку, раскрываем столбец и выбираем желаемую операцию
Expand - развернуть данные из выбранных столбцов
Aggregate - подсчитать данные в конкретных столбцах
Важно помнить что типы данных у ключевых столбцов (в обеих таблицах) должны быть одинаковыми.
Выбираем в качестве типа источника папку. Далее выбираем конкретную папку с файликами, которые предполагается объединить. Файлики должны быть одного типа и с одинаковыми столбцами.
В появившемся окошке предпросмотра данных жмем на кнопочку edit.
Команду можно вызвать по клику на кнопку на панели Ribbon
Также команду можно вызвать из контекстного меню (если нажать правой кнопкой на заголовке столбца)
Интерфейс команды Group By с комментариями представлен на скриншоте ниже:
if then else условия
Для выбора действия в зависимости от условия в Power Query используется структура с оператором if then else
Пример:
if [столбец1] 0 then [столбец2] else [столбец3]
Мастер добавления столбца с условным значением (условного столбца), формирует значение столбца согласно заданным правилам. Мастер добавления условного столбца можно найти в Power Query, на вкладке добавления столбца, как показано на скриншоте ниже.
Пояснения к мастеру конфигурации условного столбца показаны на скриншоте ниже.
Для сведения - задаваемые условия в интерфейсе мастера пишут команду скрипта содержащего операторы if then else. Это значит, что первым будет проверено первое условие в мастере условного столбца. Все множество значений будет проверено по этому условию. В случае, если будут найдены результаты удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия. Для остальных значений из множества, будет проверено следующее условие. И так далее, до тех пор, пока не будут проверенны все условия. Если для элементов множества не будет выполненно ни одно условие, то в условный столбец попадет значение из поля "В противном случае / (otherwise)".
При создании условий важно помнить, операции сравнения включающие type null и другие типы данных, возвращают ошибку в качестве результата. Подробнее об этом можно прочитать в блоге у Максима Зеленского
Изменение типа столбца с текстового на десятичный, в случае если в качестве разделителя десятичной части используется точка вместо запятой
-
Необходимо щелкнуть правой кнопкой на заголовке столбца
-
Выбрать пункт «Change type»
-
И далее выбрать пункт «Using locale»
-
Выбираем страну, где в качестве разделителя используется точка (например, USA)
Команда "Remove Duplicates" проходит по выбранным столбцам (если выбрана вся таблица, то по всей таблице) и смотрит в них повторяющиеся ячейки (строчки, в случае если выбрана таблица). Если дубликаты найдены функция оставляет первую попавшуюся уникальную строчку и удаляет все последующие повторяющиеся Найти команду можно на Ribbon - Home - Remove Duplicates (Удалить дубликаты в выбранных столбцах)
Либо найти команду можно щелкнув правой кнопкой на заголовке одного или нескольких выбранных столбцов. В случае, если выбраны несколько столбцов, то тогда будут удалены все неуникальные сочетания значений в каждой отдельной строчке в выбранных столбцах.
Удалить дубликаты строк в таблице можно нажав на кнопку в левом верхнем углу таблицы предпросмотра.
Аналогичного результата можно добиться если использовать команду "Group By"
Count rows - Подсчитать количество строчек в текущей таблице
Для выполнения необходимо щелкнуть правой кнопкой на конкретном шаге обработки. Выбрать пункт меню «Extract previous steps»
ввести имя нового запроса, который будет создан на основе предыдущих шагов
Текст взят из 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 - это надстройка над Excel, представляющая из себя быструю колоночную базу данных VertiPaq с языком запросов DAX (часто вместо VertiPaq говорят Power Pivot). В отличие от Excel число строк загруженных в Power Pivot ограничено лишь размером доступной оперативной памяти компьютера. Быстродействие Power Pivot во много раз превосходит быстродействие формул в Excel. Также Power Pivot по производительности превосходит и Power Query (при этом часто он потребляет меньше ресурсов). Результаты выполнения запросов доступны пользователям в сводных таблицах и сводных диаграмма MS Excel. Таким образом сводные таблицы выступают аналитическим интерфейсом к данным хранящимся в Power Pivot.
В Power BI Power Pivot встроен как база данных, к которой присоединяются различные визуализации.
Dax (Data Analysis Expressions) - это язык программирования использующийся для запросов в базе данных VertiPaq.
Модель данных - совокупность таблиц, связей между ними и вычисляемых мер в базе данных VertiPaq. Благодаря своей быстроте модель данных позволяет создавать мгновенно пересчитывающиеся меры, которые, в свою очередь, позволяют создавать интерактивные визуализации.
Таблицы - совокупность строк, разделённых на столбцы.
У каждого столбца задан тип данных (который, как правило, наследуется из типов данных заданных для столбцов в Power Query).
У столбцов с числовыми типами данных можно увидеть значок сигмы Слева от них. И такие столбцы по умолчанию будут просуммированы, в случае если добавить их в область значений любой визуализации.
Если же добавить в область значений столбцы с не числовыми типами данных (без значка сигмы), то к ним будет применена операция по умолчанию: count (подсчет количества значений).
Список из таблиц загруженных в модель данных можно найти в дереве fields в правой части окна Power BI.
Каждая таблица имеет название, по которому к данным конкретной таблицы можно обращаться в мерах и вычисляемых столбцах. В случае если в названии таблицы присутствует пробел или не латинские символы, то при обращении к таблице извне её название заключается в одинарные кавычки автоматически:
'название с пробелом'
Однако, если мы заключим в кавычки название таблицы без пробелов, то все будет работать как предполагалось:
'название'
Функции языка Dax похожи на функции Excel, с той лишь разницей, что в качестве аргументов используют столбцы, целые таблицы или скалярные выражения (простые значения), а не ячейки.
В качестве разделителей аргументов в зависимости от локали используются:
- ";" (и "," для десятичных)
- "," (и "." для десятичных)
Вычисляются только в момент использования. Рассчитываются в рамках текущего контекста фильтров. Именно это свойство позволяет строить интерактивные визуализации, которые фильтруются при нажатии на определенные области конкретных визуализаций. Меры хоть и принадлежат конкретной таблице, но могут безболезненно быть перенесены в любую другую в рамках документа. Поэтому хорошая практика при использовании мер формулах не включать название таблицы, в которой мера лежит. То есть вместо 'таблица'[мера] писать просто [мера].
Функционал быстрых мер позволяет создавать сложные DAX формулы без написания кода. Вместо написания кода нужно сконфигурировать желаемую меру в графическом интерфейсе. Подробнее о быстрых мерах можно прочитать в справке по Power BI
Рассчитываются однажды во время обновления таблицы в модели данных. Вычисляемые столбцы занимают место в памяти. Вычисляемые столбцы рассчитываются в рамках контекста строки. В отличие от мер, вычисляемые столбцы могут использоваться для фильтрации и сортировки таблиц. Принадлежат конкретной таблице и их лучше указывать в формулах вместе с названием таблицы, даже если Power Pivot позволяет этого не делать.
Подробнее про столбцы и меры читать здесь:
В Power Pivot существуют два контекста выполнения формулы, которые действуют одновременно:
- Контекст фильтров (Filter context)
- Контекст строк (Row context)
Это массивная и сложная тема. На момент написания методички автор так и не разобрался с темой до того уровня, чтобы рассказывать об этом окружающим. Потому рекомендую обратиться к достоверным источникам вроде:
- Марко Руссо и Альберто Феррари https://www.sqlbi.com/.
- Справка Microsoft Power BI: Основные сведения о DAX в Power BI Desktop
Для связи таблиц в модели данных в одной из таблиц в ключевом поле должны быть уникальные значения. Также столбцы должны быть одного типа данных.
Направление связи имеет значение. Вычисляемые столбцы могут использоваться для создания связей между таблицами.
Справка: Создание связей и управление ими в Power BI Desktop - Power BI | Microsoft Docs
В случае, если в модели данных есть отдельная таблица-календарь (вроде такой), и если таблица с фактами связана с таблицей-календарем по полю дата, то для создания быстрых мер в поле дата нужно использовать столбец с датами из таблицы календаря. Столбец с датами из таблицы с фактами в быстрых мерах работать не будет.
SUM (Столбец) - Cумма чисел по столбцу
COUNTA (Столбец) - Количество значений в столбце
DISTINCTCOUNT (Столбец) - Количество уникальных значений в столбце
SUMX (Таблица, Выражение) - Сумма значений выражения, которое выполняется для каждой строчки таблицы
DIVIDE (Значение числителя, значение знаменателя, альтернативный вариант в случае ошибки деления числителя на знаменатель) - Безопасное деление
IFERROR (Значение, Значение если ошибка) - Если ошибка
IF (Логическое выражение, значение если правда, значение если ложь ) - Если
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"
- 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
Загрузку данных в фоне часто рекомендуют отключить, во избежание проблем с производительностью. Ссылки по теме:
- Параметр Allow Data Preview To Download In The Background в Power Query и Power BI - статья Криса Вебба: перевод 🇷🇺, оригинал 🇺🇲.
- Ветка, в предложения выключить эту настройку по умолчанию uservoice.
| Клавиша | Описание | |
|---|---|---|
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
