Power BI: Преобразование одного столбца в несколько столбцов с пустыми значениями

Преобразование одного столбца с названиями серий в несколько столбцов со значениями null в Power BI

Чтобы преобразовать один столбец, содержащий названия серий, в несколько столбцов со значениями null в Power BI, вам потребуется использовать либо преобразование Unpivot (Распределить), либо Pivot (Сводка) в Power Query, в зависимости от текущего формата ваших данных. Если у вас данные в длинном формате с столбцом “Series Name” (Название серии), содержащим ‘List’, ‘All’, ‘Ordered’ и ‘Rejected’, вам сначала потребуется выполнить распаковку (unpivot) для создания структуры атрибут-значение, а затем сворачивание (pivot) для преобразования их в отдельные столбцы с сохранением значений null.

Содержание

• Понимание структуры ваших данных
• Метод 1: Использование преобразования Unpivot, затем Pivot
• Метод 2: Решение с использованием пользовательского кода M
• Обработка значений null во время преобразования
• Устранение распространенных проблем
• Лучшие практики для преобразования данных

Понимание структуры ваших данных

Перед выполнением преобразования необходимо понять текущий формат ваших данных. Исходя из вашего описания, скорее всего, у вас данные находятся в “длинном” формате, где каждая строка представляет собой одну точку данных со столбцами, такими как:

• Hire (категория)
• Series Name (содержит ‘List’, ‘All’, ‘Ordered’, ‘Rejected’)
• Value (фактическое значение данных)

Цель - преобразовать это в “широкий” формат, где каждая серия становится отдельным столбцом, создавая значения null там, где данные отсутствуют для определенных комбинаций категорий Hire и Series.

“Распаковка (unpivot) относится к преобразованию, при котором вы перемещаете столбцы в строки, но по умолчанию Power Query удаляет все ячейки со значением null.” - Rick de Groot в LinkedIn

Метод 1: Использование преобразования Unpivot, затем Pivot

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

Шаг 1: Подготовка данных

1. Откройте редактор Power Query в Power BI
2. Выберите запрос, содержащий данные серий

Шаг 2: Распаковка столбцов серий

Если у вас есть отдельные столбцы для каждой серии, которые нужно распаковать:

1. Выберите все столбцы серий (List, All, Ordered, Rejected)
2. Щелкните правой кнопкой мыши и выберите “Unpivot Other Columns” (Распаковать другие столбцы) или перейдите на вкладку Transform (Преобразование) → Unpivot (Распаковать) → Unpivot Other Columns (Распаковать другие столбцы)
3. Это создаст два новых столбца: “Attribute” (атрибут) и “Value” (значения)

“Все просто: просто укажите столбцы, которые вы хотите переместить, и функция распаковки сделает остальное. Но по умолчанию она удаляет все ячейки со значением null.” - BI Gorilla

Шаг 3: Обработка значений null перед сворачиванием

Поскольку распаковка по умолчанию удаляет значения null, их нужно сохранить:

1. Перед распаковкой выберите столбцы серий
2. Перейдите на вкладку Transform (Преобразование) → Replace Values (Заменить значения)
3. Замените значения null на заполнитель (например, “0” или пробел)
4. Выполните операцию распаковки
5. После распаковки при необходимости можно вернуть заполнитель обратно в null

“Попробуйте заменить значение null на пробел в четырех столбцах вопросов, которые вы распаковываете.” - Сообщество Microsoft Fabric

Шаг 4: Сворачивание обратно в широкий формат

1. Выберите столбец “Attribute”
2. Перейдите на вкладку Transform (Преобразование) → Pivot Column (Сводный столбец)
3. Установите “Values Column” (Столбец значений) в ваш столбец со значениями
4. Это создаст отдельные столбцы для каждого названия серии

Метод 2: Решение с использованием пользовательского кода M

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

Базовая структура кода M

let
    Source = YourDataSource,
    // Заменяем null на заполнитель перед преобразованием
    ReplaceNulls = Table.ReplaceValue(Source, null, "NULL_PLACEHOLDER", Replacer.ReplaceValue, {"List", "Ordered", "Rejected"}),
    // Распаковываем столбцы
    Unpivoted = Table.UnpivotOtherColumns(ReplaceNulls, {"Hire"}, "Attribute", "Value"),
    // Сворачиваем обратно в широкий формат
    Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value"),
    // Заменяем заполнитель обратно на null
    FinalResult = Table.TransformColumns(Pivoted, {{"List", each if _ = "NULL_PLACEHOLDER" then null else _}, 
                                                   {"Ordered", each if _ = "NULL_PLACEHOLDER" then null else _}, 
                                                   {"Rejected", each if _ = "NULL_PLACEHOLDER" then null else _}})
in
    FinalResult

Расширенный код M для динамической обработки серий

Если вам нужно обрабатывать динамические названия серий или более сложные сценарии:

let
    Source = YourDataSource,
    // Получаем все уникальные названия серий
    SeriesNames = Table.ColumnNames(Source),
    // Фильтруем несерийные столбцы (например, Hire)
    SeriesColumns = List.Select(SeriesNames, each not List.Contains({"Hire"}, _)),
    // Заменяем null на заполнитель
    ReplaceNulls = Table.ReplaceValue(Source, null, "NULL_PLACEHOLDER", Replacer.ReplaceValue, SeriesColumns),
    // Динамическая распаковка
    Unpivoted = Table.UnpivotOtherColumns(ReplaceNulls, {"Hire"}, "Series", "Value"),
    // Сворачиваем обратно
    Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Series]), "Series", "Value"),
    // Очищаем заполнители
    FinalResult = Table.TransformColumns(Pivoted, 
        List.Transform(SeriesColumns, each {_, each if _ = "NULL_PLACEHOLDER" then null else _}))
in
    FinalResult

Обработка значений null во время преобразования

Сохранение значений null важно для поддержания целостности данных. Вот несколько подходов:

Метод А: Замена перед преобразованием

1. Выберите столбцы, которые вы планируете преобразовать
2. Перейдите на вкладку Transform (Преобразование) → Replace Values (Заменить значения)
3. Замените null на временное значение, например "NULL" или 0
4. Выполните операции распаковки/сворачивания
5. Замените временное значение обратно на null

Метод Б: Использование пользовательской функции

(SeriesColumn as text) as any =>
    if Source[SeriesColumn] = null then 
        null 
    else 
        Source[SeriesColumn]

Метод В: Фильтрация и сохранение

1. Создайте резервную копию исходных данных
2. Работайте с отфильтрованной версией для преобразований
3. Объедините результаты обратно с исходными данными для сохранения null

“Проблема: пустые ячейки создают значения null после распаковки. Решение: используйте Transform → Replace Values для замены null на 0 или удаления строк с null” - UnpivotTool

Устранение распространенных проблем

Проблема 1: Пропущенные строки после распаковки

Проблема: Некоторые строки исчезают после распаковки из-за значений null
Решение: Замените null на заполнители перед преобразованием, как упоминалось ранее

"В приведенном ниже примере обратите внимание, что Andrew не имеет продаж и исключается после распаковки… Выберите понедельник-пятницу в редакторе запросов, перейдите на вкладку Transform, Replace Values → Замените “null” на “0"” - Сообщество Microsoft Fabric

Проблема 2: Несоответствия типов данных

Проблема: Смешанные типы данных после преобразования
Решение: Используйте Table.TransformColumnTypes для стандартизации типов данных

Проблема 3: Проблемы с производительностью при работе с большими наборами данных

Проблема: Преобразования выполняются медленно при миллионах строк
Решение:

• Фильтруйте данные перед преобразованием
• Используйте более эффективный код M
• Рассмотрите возможность использования промежуточных таблиц

Проблема 4: Динамические имена столбцов

Проблема: Названия серий могут меняться со временем
Решение: Используйте динамический код M, который автоматически определяет имена столбцов, как показано в расширенном примере кода M выше

Лучшие практики для преобразования данных

1. Всегда создавайте резервную копию данных: Создайте копию вашего запроса перед выполнением сложных преобразований

2. Используйте значения-заполнители: Заменяйте null на временные заполнители во время преобразования, а затем восстанавливайте их

3. Тестируйте с выборочными данными: Используйте подмножество ваших данных для проверки преобразований перед применением ко всему набору данных

4. Документируйте шаги: Добавляйте комментарии в ваш код M, объясняя логику преобразования

5. Обрабатывайте типы данных: Убедитесь в согласованности типов данных на протяжении всего процесса преобразования

6. Учитывайте производительность: Для больших наборов данных оптимизируйте ваши преобразования, фильтруя данные на ранних этапах

7. Используйте обработку ошибок: Реализуйте блоки try-catch для подверженных ошибкам преобразований

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

“Распаковка в редакторе Power Query… По сути, она делает обратное сворачиванию: вместо того чтобы превращать значения строк в заголовки столбцов, она преобразует заголовки столбцов в значения строк.” - AlmaBetter

Заключение

Преобразование одного столбца с названиями серий в несколько столбцов со значениями null в Power BI требует тщательной обработки значений null во время процесса преобразования. Ключевые шаги включают:

1. Понимание, нужно ли сначала распаковать данные (если они в широком формате) или сразу свернуть (если в длинном формате)
2. Использование значений-заполнителей для сохранения информации о null во время преобразования
3. Применение либо встроенных преобразований Power Query, либо пользовательского кода M
4. Проверку результатов для обеспечения сохранения целостности данных

Следуя описанным выше методам, вы можете успешно преобразовать ваш столбец ‘Series Name’ в отдельные столбцы ‘List’, ‘All’, ‘Ordered’ и ‘Rejected’, сохраняя соответствующие значения null между строками, где данные отсутствуют для всех категорий серий.

Источники

1. BI Gorilla - Unpivot Columns And Keep Null Values in Power Query
2. Сообщество Microsoft Fabric - Unpivot other columns with null values
3. Сообщество Microsoft Fabric - Unpivot removes rows with no/null values
4. UnpivotTool - Power BI Unpivot Columns Guide
5. Rick de Groot в LinkedIn - Unpivot Columns And Keep Null Values
6. Форум Enterprise DNA - Unpivoting removes rows with null values
7. Сообщество Microsoft Power BI - Unpivot removes rows with no/null values
8. AlmaBetter - Pivot and Unpivot in Power BI
9. Сообщество Microsoft Fabric - All data’s do not appear on unpivoting the columns
10. RADACAD - Pivot and Unpivot with Power BI