Продвинутый Power BI: Интерактивные дашборды, DAX и оптимизация

1. Подключение и продвинутое преобразование данных в Power Query

Качественный дашборд начинается не с красивых графиков, а с чистых и правильно структурированных данных. В Power BI за этот этап отвечает Power Query — встроенный ETL-инструмент (Extract, Transform, Load — Извлечение, Преобразование, Загрузка). Если представить создание отчета как приготовление блюда в ресторане, то Power Query — это кухня, где сырые ингредиенты моют, чистят и нарезают, прежде чем передать шеф-повару (языку DAX) для создания кулинарного шедевра.

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

Базовые отчеты часто строятся на одном-двух файлах Excel. Однако в реальной бизнес-среде данные разбросаны по множеству систем: базам данных SQL, облачным CRM, веб-API и локальным папкам.

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

При таком подключении Power Query считывает метаданные всех файлов в директории. Вы можете отфильтровать их по расширению (например, оставить только .csv) или по названию (например, файлы, содержащие слово "Sales"). Затем система автоматически объединит содержимое всех файлов в одну таблицу. Когда в следующем месяце в папку добавят новый файл, вам будет достаточно нажать кнопку «Обновить» в Power BI, и новые данные мгновенно подтянутся в модель.

Слияние и Добавление: архитектура объединения данных

Когда данные поступают из разных источников, их необходимо объединить. В Power Query для этого существуют две фундаментально разные операции: Добавление (Append) и Слияние (Merge).

Добавление запросов (Append)

Эта операция используется, когда у вас есть таблицы с одинаковой структурой (одинаковыми столбцами), и вы хотите объединить их данные по вертикали.

> Добавление похоже на складывание кирпичей друг на друга: вы увеличиваете количество строк, но структура столбцов остается прежней.

Пример: у вас есть таблица Продажи_2022 (10 000 строк) и Продажи_2023 (15 000 строк). При добавлении вы получите одну таблицу на 25 000 строк. Если в одной из таблиц есть столбец, которого нет в другой, Power Query создаст его в итоговой таблице, заполнив пустые ячейки значением null.

Слияние запросов (Merge)

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

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

!Схема операций объединения данных: Добавление (Append) и Слияние (Merge)

Отмена свертывания столбцов (Unpivot)

Одной из самых частых проблем при импорте данных из Excel является их «широкий» формат, удобный для чтения человеком, но совершенно непригодный для анализа в Power BI.

Представьте таблицу бюджета, где в первом столбце указана статья расходов, а далее идут 12 столбцов с названиями месяцев (Январь, Февраль, Март и т.д.), в которых записаны суммы.

Если загрузить такую таблицу в Power BI, вы не сможете создать срез (фильтр) по месяцам или написать универсальную меру на DAX для подсчета суммы, так как каждый месяц является отдельным столбцом. Данные необходимо нормализовать — перевести из «широкого» формата в «длинный».

Для этого используется функция Отмена свертывания столбцов (Unpivot). Выделяете столбцы с месяцами и применяете операцию. Power Query преобразует их в два новых столбца: Атрибут (куда попадут названия месяцев) и Значение (куда попадут суммы). В результате таблица станет длиннее (количество строк умножится на 12), но теперь у вас будет единый столбец с датами, который можно связать с календарем и использовать в фильтрах.

Условные столбцы и извлечение данных

Часто исходные данные требуют категоризации или очистки прямо на этапе загрузки. Вместо того чтобы писать сложные формулы IF в DAX, лучше создать Условный столбец в Power Query. Это снижает нагрузку на модель данных.

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

Если Сумма больше 100 000 руб., то вывести "Крупный чек".

Если Сумма больше 50 000 руб., то вывести "Средний чек".

В противном случае — "Мелкий чек".

Также Power Query отлично справляется с извлечением текста. Если у вас есть столбец с артикулами вида FURN-CH-1024 (где FURN — категория, CH — подкатегория, 1024 — номер), вы можете использовать инструмент «Разделить столбец по разделителю» (в данном случае по дефису), чтобы мгновенно получить три отдельных столбца для детального анализа.

Оптимизация производительности: Свертывание запросов (Query Folding)

Когда вы работаете с большими объемами данных (миллионы строк), скорость обновления отчета становится критическим фактором. Здесь на сцену выходит концепция Свертывания запросов (Query Folding).

Свертывание запросов — это способность Power Query переводить шаги преобразования данных (фильтрацию, удаление столбцов, группировку) на родной язык источника данных (например, в SQL-запрос) и передавать их на выполнение серверу.

Почему это важно? Представьте, что в базе данных SQL хранится 100 миллионов записей о продажах, но для отчета вам нужны только данные за 2023 год (около 5 миллионов строк).

Если свертывание работает, Power Query отправляет серверу команду:

Сервер обрабатывает запрос и возвращает в Power BI только 5 миллионов строк. Это происходит быстро и не перегружает вашу оперативную память.

Если свертывание нарушено, Power Query скачивает все 100 миллионов строк на ваш компьютер и только потом начинает фильтровать их локально. Обновление отчета может занять часы или завершиться ошибкой нехватки памяти.

Как сохранить свертывание запросов

Не все операции поддерживают свертывание. Базовые правила оптимизации:

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

Осторожно с типами данных. Изменение типа данных (например, текста на дату) часто прерывает свертывание. Делайте это на самых последних этапах запроса.

Избегайте сложных пользовательских функций. Написание сложных скриптов на языке M или использование специфических функций (например, извлечение текста по сложной логике), которые не имеют аналогов в SQL, гарантированно сломает свертывание.

Проверить, работает ли свертывание, очень просто. В панели «Примененные шаги» кликните правой кнопкой мыши по шагу. Если пункт «Просмотреть собственный запрос» (View Native Query) активен, значит, свертывание работает. Если он выделен серым цветом — на этом шаге передача вычислений на сервер прервалась.

Язык M: взгляд под капот

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

Для 90% задач достаточно визуального интерфейса. Однако понимание языка M открывает двери к продвинутой автоматизации. Открыв «Расширенный редактор» (Advanced Editor), вы увидите структуру запроса. Она всегда состоит из двух блоков: let (где пошагово описываются переменные и преобразования) и in (где указывается финальный результат, который будет выведен в таблицу).

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

2. Моделирование данных и оптимизация производительности отчетов

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

В Power BI за хранение и обработку данных отвечает аналитический движок VertiPaq. Его главная задача — сжимать миллионы строк в оперативной памяти и мгновенно выдавать результат при клике на график. Но чтобы движок работал на 100% своих возможностей, ему нужна правильная архитектура.

Схема «Звезда»: золотой стандарт аналитики

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

Представьте таблицу на 1 000 000 строк. Если один и тот же смартфон покупали 50 000 раз, его длинное название («Apple iPhone 15 Pro 256GB Titanium») будет записано в памяти 50 000 раз. Это съедает оперативную память и замедляет работу отчета.

Правильный подход — использовать схему «Звезда» (Star Schema). В этой архитектуре данные разделяются на два типа таблиц:

Таблицы фактов (Fact tables) — содержат количественные данные о событиях. Это транзакции, клики на сайте, звонки. Здесь хранятся числа (сумма, количество) и ключи (ID товара, ID клиента). Эта таблица длинная (миллионы строк), но узкая (мало столбцов).

Таблицы измерений (Dimension tables) — содержат описательную информацию. Это справочники товаров, клиентов, календари. Здесь хранятся текстовые названия, категории, адреса. Эта таблица короткая (например, 1000 товаров), но широкая (много атрибутов).

В схеме «Звезда» таблица фактов находится в центре, а таблицы измерений окружают ее, соединяясь через уникальные идентификаторы (ID). Теперь название смартфона хранится в справочнике ровно один раз, а в таблице продаж фигурирует только короткое число — например, ID = 42.

!Схема «Звезда»: таблица фактов в центре и таблицы измерений вокруг нее

Связи и направление фильтрации

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

Самый правильный и безопасный тип связи — это один-ко-многим (обозначается как ). «Один» всегда находится на стороне таблицы измерений (каждый товар уникален), а «Много» — на стороне таблицы фактов (один товар может быть продан много раз).

Ключевой параметр любой связи — направление кросс-фильтрации (Cross-filter direction). По умолчанию фильтры текут от «Одного» ко «Многим» (от справочника к транзакциям). Если вы выберете год в календаре, таблица продаж отфильтруется.

Иногда разработчики включают двунаправленную фильтрацию (Both), чтобы фильтры текли в обе стороны. Это позволяет, например, отфильтровать справочник клиентов так, чтобы в нем остались только те, кто покупал выбранный товар. Однако злоупотреблять этим нельзя.

> Двунаправленные связи — это как улица с двусторонним движением без разметки: удобно для одной машины, но при плотном потоке данных неизбежно приведет к затору и неоднозначности вычислений.

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

Движок VertiPaq и кардинальность данных

Чтобы понять, как оптимизировать модель, нужно заглянуть под капот Power BI. Движок VertiPaq является колоночной базой данных. Это значит, что он хранит и сжимает каждый столбец отдельно, а не строки целиком.

Эффективность сжатия напрямую зависит от кардинальности (Cardinality) — количества уникальных значений в столбце. Чем меньше уникальных значений, тем сильнее VertiPaq сожмет данные.

Пример: столбец «Пол клиента» имеет всего два значения (Мужской, Женский). Его кардинальность минимальна. Движок сожмет миллион таких строк до нескольких килобайт.

А теперь рассмотрим столбец «Точное время заказа», где данные записаны в формате 2023-10-25 14:32:15. В таблице на 10 миллионов строк почти каждое значение будет уникальным. Кардинальность огромна, сжатие не работает, и этот единственный столбец может занимать 30-40% всего веса файла Power BI.

Как это оптимизировать? Разделите дату и время на два разных столбца на этапе Power Query.

В столбце «Дата» за 10 лет будет всего 3 650 уникальных значений.

В столбце «Время» (с точностью до секунды) — максимум 86 400 уникальных значений.

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

Вычисляемые столбцы против Мер

Когда базовых данных не хватает, мы начинаем писать формулы на языке DAX. Здесь возникает главный архитектурный выбор: создать Вычисляемый столбец (Calculated Column) или Меру (Measure).

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

Мера — это виртуальная формула. Она не хранит данные в таблице, а вычисляет результат «на лету» в тот момент, когда вы перетаскиваете ее на график. Мера не занимает оперативную память, но использует процессор (CPU) при каждом клике пользователя.

Золотое правило оптимизации: Если вам нужно математически агрегировать данные (найти сумму, среднее, отклонение) — всегда используйте Меру.

Например, чтобы посчитать маржу, не нужно создавать столбец, где в каждой из миллиона строк из выручки вычитается себестоимость. Создайте меру: Маржа = SUM(Продажи[Выручка]) - SUM(Продажи[Себестоимость]). Движок сначала быстро сложит два столбца, а затем сделает всего одно вычитание на уровне итогов. Это сэкономит гигабайты памяти и сделает вашу модель по-настоящему профессиональной.