• Введение
  • Предисловие
  • Отказ от ответственности
  • Лицензия на использование
  • Добавление информации и внесение изменений в методическое пособие
• Редактор запросов (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 в основной репозиторий

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

В программе Power BI Desktop Power Query встроенный модуль. В справке и интерфейсе этот модуль называется Query Editor или редактор запросов. Power Query это основной инструмент данных в 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". Скриншоты, как Power Query выглядит в различных версиях офиса приведены ниже:

В зависимости от версии подписки 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 Query в Power BI по нажатию на кнопку Get Data.

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

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

В 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

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

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

• Типы данных столбцов таблицы обозначаются иконками в области заголовков:

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

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

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

Команда на Ribbon:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример:

if [столбец1] 0 then [столбец2] else [столбец3]

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

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

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

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

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

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

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

Текст взят из 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 - это надстройка над 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 позволяет этого не делать.

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

• Calculated Columns and Measures in DAX - SQLBI

В 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

• Справка

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

• Справка

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

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

Данное методическое пособие было выложен в открытый доступ 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]: