Особенность загрузки текстовых столбцов в Power BI и PowerPivot, или Как найти дубликаты там, где их нет

Проблема

На днях я провел несколько увлекательных часов, пытаясь найти причину, по которой Power BI отказывался строить связь «один-ко-многим» между двумя таблицами – справочником и таблицей фактов. Эта короткая история в очередной раз говорит нам: «Век живи – век учись»

Ошибка, на которую указывал Power BI, звучит приблизительно так: «невозможно установить связь — как минимум один из столбцов, участвующих в связи, должен содержать уникальные данные»

Связь между двумя столбцами должна была строиться по текстовому полю [SKU Name], содержащему названия SKU (я понимаю, что это не лучший вариант, но таковы условия проекта), источник данных – таблицы Excel.

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

Вы суслика видите?..

Обнаружить дубликаты в редакторе запросов Power Query не получалось ну совсем никак:

  1. Удаление дубликатов из столбца [SKU Name] не изменило количество строк.
  2. Помня, что модель данных и DAX не различает регистр (в отличие от Power Query), я перевел значения столбца в верхний регистр и снова удалил дубликаты. Не помогло.
  3. Предположив, что в тексте могут случайно встретиться непечатаемые символы, я применил очистку (Text.Clean) значений столбца. Это тоже не помогло удалить дубликаты, но зато увеличилось количество звучащих в моем домашнем офисе непечатных выражений.
  4. Я подключил тяжелую артиллерию – вместо простого удаления дубликатов при помощи кнопки в интерфейсе я видоизменил формулу этого шага, явно указав игнорирование регистра (ну мало ли) и язык проверки дубликатов: Table.Distinct(Table, {{“SKU Name”, Comparer.FromCulture(“en-us”, true)}}). Не помогло…

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

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

Я создал простую таблицу, в которую поместил список [SKU Name], и во второй столбец поместил тот же [SKU Name], изменив у него вычисление на подсчет значений.

Бинго! Отсортировав второй столбец по уменьшению, я, наконец, увидел злосчастную SKU:

дублирующийся элемент списка
Вот он, исчадие рода Баскервилей SKU!

Осталось только понять, почему же эта SKU считается дубликатом в модели, но не обнаруживается как таковой в Power Query и Excel.

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

Угадаете результат? Правильно, я получил одну строку. Никаких дубликатов.

Здесь должно быть две строки, но видна только одна
Здесь должно быть две строки, но видна только одна

Я на некоторое время завис, не понимая, что делать дальше. «Вы суслика видите? – Нет. – А он есть.»

Не дубликат я!

И тут меня посетила гениальная идея: вместо фильтра столбца по самому значению SKU, я решил проверить, что мне даст фильтр «Текст содержит…»

Вот они, красавицы:

Дубликаты нашлись. Осталось понять, почему они не удалились
Дубликаты нашлись. Осталось понять, почему они не удалились

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

Еще немного экспериментов показали:

  1. При загрузке в модель данных из строк удаляются концевые пробелы.
  2. Начальные пробелы и лишние внутренние пробелы не удаляются
  3. PowerPivot в Excel ведет себя точно также, как и Power BI
  4. Обычная сводная таблица в Excel уважает концевые пробелы и не удаляет их.

Вы знаете, вообще-то предупреждать надо… Я перерыл кучу документации и нашел только несколько отсылок к кубам Analysis Services, а также несколько сообщений на форумах, подтверждающих: так задумано, и вряд ли будет изменено в ближайшем времени. Может быть, кто-то сможет найти ссылку на место в документации, где об этом говорится?

Опытные товарищи подсказали, что это поведение соответствует стандарту ANSI SQL. Возможно, это и так, но мне, как пришедшему из мира Excel, это неведомо.

Резюме

Справиться с этой проблемой можно так:

  1. Если такие строки действительно дубликаты, они прекрасно удаляются за счет применения функции Text.Trim (или, если вам дороги начальные пробелы, функции Text.TrimEnd) перед удалением дубликатов. Это можно сделать и через интерфейс, кнопками, на вкладке Transform:
    Удаляем лишние пробелы (с обоих концов строки)
  2. Если эти строки принципиально различаются и вы не хотите считать их дубликатами, то нужно сделать очень простую вещь – добавить какой-то символ к строке, заканчивающейся на пробел.
    Например, можно (в новом столбце или при трансформации столбца) добавить точку в конец такой строки при помощи простой функции:
    (string)=> if Text.End( string, 1 )=" " then string &"." else string
  3. Если вы не против добавить символ ко всем строкам, то можно просто добавить суффикс ко всем значениям столбца – через то же меню, что и на картинке выше, пункт “Add Suffix”.

Удачи, и глядите в оба!


Пользовательская сортировка каскадной диаграммы (Waterfall) в Power BI

Недавно в чате о Power BI в Telegram был задан вопрос – возможно ли применить для графика Waterfall (каскадная диаграмма, она же «Водопад») нестандартную динамическую сортировку: положительные значения показываются по убыванию, а отрицательные наоборот, по возрастанию (то есть, сначала самые большие по модулю отрицательные значения, затем минуса поменьше и самые мелкие – в конце).

В итоге из такого графика:

Обычная сортировка каскадной диаграммы

Нужно получить вот такой:

Нестандартная сортировка

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

Настройка сортировки в каскадной диаграмме Power BI

Нам же нужно сделать так, чтобы:

  1. Можно было применить нестандартную сортировку;
  2. Она должна быть динамической, то есть реагировать на фильтры (например, по дате)

Решение этой задачи делится на две подзадачи:

  1. Найти способ применить пользовательскую сортировку
  2. Определить и реализовать алгоритм сортировки

Можем ли мы применить пользовательскую сортировку?

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

У Waterfall таких областей четыре:

  1. Категория
  2. Распределение
  3. Ось Y
  4. Подсказки

Первые две используются для определения значений оси X, третья – для определения размера столбиков, а вот четвертая используется для вывода информации во всплывающем окошке при наведении мыши на элемент графика.

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

Скажу сразу – если бы мы не могли использовать эту область для сортировки, то и решение было бы кардинально другим, если бы вообще было.

Как задать правило сортировки?

Вспомним, что сортировка должна учитывать возможность применения пользовательских фильтров, поэтому нам не подойдет вычисляемый столбец в DAX или Power Query). То есть, нам нужна мера.

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

Continue Reading

Объединяем выбор на разных срезах в DAX: Динамический фильтр «ИЛИ»

В стандартном режиме несколько срезов в Power BI работают по принципу «И», то есть пересечения примененных фильтров. Мы выбираем на одном срезе «Яблоки», на другом цвет «Красный», и получаем меры, рассчитанные только для красных яблок. Зеленые яблоки будут проигнорированы.

Обычный срез: красные яблоки

Обычный срез: красные яблоки

Периодически (хоть и нечасто) возникает другая задача: как заставить срезы работать по принципу «ИЛИ», то есть учитывать в мере значения обоих срезов? Например, получить одновременно все зеленые фрукты и все яблоки? Все желтое или грушевидное?

Более практичный пример: товарная позиция может быть помечена в базе как складская (за это отвечает свойство «Складская»), одновременно она помечена как плановая (за это отвечает другое свойство, «Плановая»). Для расчетов нас интересуют позиции, которые могут являться, к примеру, складскими ИЛИ плановыми (то есть у них может быть установлено либо одно из этих свойств, либо оба).  Но при использовании двух обычных срезов отбор по свойству «Складская» = «Да» приведет к тому, что прочие строки будут отфильтрованы, даже если у них свойство «Плановая» тоже установлено срезом в значение «Да».

Стандартное решение

Множество интересных способов получить желаемый результат можно почерпнуть в статье гуру DAX Альберто Феррари и Марко Руссо («итальянцев»). Обычно примеры таких мер используют так называемые «прямые» фильтры – когда проводится сравнение с жестко заданным значением. Стандартное решение для таких мер следующее:

Continue Reading


Первая встреча Power BI User Group в Санкт-Петербурге

Идея собрать петербургское сообщество пользователей Power BI, вынашиваемая мной и Дмитрием Соловьевым с весны, к концу лета наконец-то «нашла свое воплощение» благодаря настойчивости Димы, помощи коллег из Awara IT Solutions и поддержке самой популярной русскоязычной группы в Facebook, посвященной Power BI

В итоге при поддержке Microsoft в Санкт-Петербурге 23 августа 2017 года прошла первая встреча SPb Power BI User Group (с параллельным созданием сообщества на сайте https://www.pbiusergroup.com).

Состав выступающих был весьма плотный, участвовали целых 5 спикеров:

Я насчитал более 30 человек слушателей — очень неплохо для первого раза и скромного помещения!

Тема моего выступления была достаточно замысловато задана как «Меры DAX с нестандартной реакцией на срезы». На самом деле рассказ был о том, как:

Continue Reading


Динамический выбор мер, отображаемых на диаграммах Power BI, при помощи среза

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

Однако после некоторого обсуждения я засомневался, действительно ли это невозможно. Если мы поместим меру в область значений (“Value”) диаграммы, она будет показана как ряд (например, какая-то мера [Total Amount]). А что на самом деле нужно моему клиенту? Он хочет выбрать какие-то значения на срезе и, если  некоторое значение выбрано, показать соответствующую меру. Если значение не выбрано, то не показывать эту меру.

На самом деле элементы среза это уникальные значения какого-то столбца. Срез применяет фильтр к этому столбцу. Можем мы понять, что столбец отфильтрован? Да, конечно, мы можем это сделать при помощи формул DAX. Если нужное значение выбрано, то мы должны показать меру как ряд на диаграмме. Так как эта мера уже находится в области значений диаграммы, то нам нужно, попросту говоря, «ничего не делать». Собственно, нам необходимо только как-то спрятать меру, если нужное значение не выбрано на срезе.

Continue Reading