Power BI Pivot Column: Преобразование одного столбца в несколько столбцов

Вы можете преобразовать один столбец с несколькими значениями в отдельные столбцы в Power BI с помощью преобразований Power Query или мер DAX. Наиболее эффективный подход — использовать функцию Поворот столбца в Power Query с расширенными настройками для сохранения отдельных точек данных и создания нулевых значений при необходимости.

Содержание

• Метод поворота столбца в Power Query
• Расширенные техники Power Query
• Решения DAX для динамических результатов
• Обработка нулевых значений и целостность данных
• Пошаговое руководство по реализации
• Лучшие практики и соображения по производительности

Метод поворота столбца в Power Query

Преобразование Поворот столбца в Power Query специально разработано для преобразования значений из одного столбца в несколько столбцов, что делает его идеальным для вашего сценария со столбцом “Название серии”.

Базовые шаги поворота

1. Откройте редактор Power Query, нажав Преобразование данных на вкладке Главная в Power BI
2. Выберите вашу таблицу, содержащую столбец “Название серии”
3. Перейдите на вкладку Преобразование и выберите Поворот столбца
4. В диалоговом окне поворота:
   • Столбец для поворота: Выберите ваш столбец “Название серии”
   • Столбец значений: Выберите столбец, содержащий ваши точки данных (вероятно, числовые значения)
   • Расширенные параметры: Выберите “Не агрегировать” из выпадающего списка

Ключевое замечание: Параметр “Не агрегировать” гарантирует, что Power BI не будет пытаться объединять несколько значений для одной серии, сохраняя отдельные точки данных и создавая нули там, где данные отсутствуют.

Почему это работает для вашего сценария

Когда у вас есть несколько точек данных для серий, таких как “Все” (со значениями 358 для Tier05, 274 для Tier06 и т.д.), операция поворота:

• Создаст отдельные столбцы для каждого уникального значения серии (“Список”, “Все”, “Заказано”, “Отклонено”)
• Заполнит значения там, где данные существуют
• Вставит нулевые значения там, где недостаточно точек данных для заполнения всех строк для определенной серии

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

Расширенные техники Power Query

Для более сложных сценариев, когда базовое преобразование поворота не идеально обрабатывает вашу структуру данных, можно использовать расширенные техники Power Query.

Пользовательский столбец с логикой на основе индекса

При работе с неравномерным распределением данных по сериям можно создать пользовательскую логику с помощью языка M Power Query:

let
    Source = YourTableName,
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddedCustom = Table.AddColumn(AddedIndex, "SeriesIndex", 
        each [Series Name] & "_" & Text.From([Index])),
    Pivoted = Table.Pivot(AddedCustom, 
        List.Distinct(AddedCustom[SeriesIndex]), "SeriesIndex", "Hire")
in
    Pivoted

Этот подход:

• Создает уникальные идентификаторы для каждой точки данных
• Поворачивает данные по этим идентификаторам
• Поддерживает целостность данных при разных длинах серий

Группировка и создание столбцов

Еще одна расширенная техника включает группировку данных по сериям и последующее создание столбцов:

let
    Source = YourTableName,
    Grouped = Table.Group(Source, {"Hire Category"}, {{"SeriesData", each _}}),
    Expanded = Table.ExpandRecordColumn(Grouped, "SeriesData", 
        {"Series Name", "Value"}, {"Series Name", "Value"})
in
    Expanded

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

Решения DAX для динамических результатов

Хотя преобразования Power Query обычно предпочтительны для преобразования данных, вы также можете использовать меры DAX для динамического создания столбцов, особенно при работе с вычисляемыми значениями.

Динамическое создание столбцов с DAX

Для создания столбцов для каждой серии динамически можно использовать DAX с условной логикой:

ListSeries = 
CALCULATE(
    SUM[YourDataColumn],
    FILTER(YourTable, YourTable[Series Name] = "List")
)

AllSeries = 
CALCULATE(
    SUM[YourDataColumn], 
    FILTER(YourTable, YourTable[Series Name] = "All")
)

OrderedSeries = 
CALCULATE(
    SUM[YourDataColumn],
    FILTER(YourTable, YourTable[Series Name] = "Ordered")
)

RejectedSeries = 
CALCULATE(
    SUM[YourDataColumn],
    FILTER(YourTable, YourTable[Series Name] = "Rejected")
)

Динамический поворот с DAX

Для более динамичных результатов можно создать меру, которая поворачивается на основе текущего контекста:

DynamicSeriesValue = 
VAR CurrentSeries = SELECTEDVALUE(Series[Series Name])
RETURN
CALCULATE(
    SUM[YourDataColumn],
    FILTER(YourTable, YourTable[Series Name] = CurrentSeries)
)

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

Обработка нулевых значений и целостность данных

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

Техники сохранения нулевых значений

1. Замена заполнителей: Как упоминалось в исследованиях, вы можете временно заменять нули на заполнители в процессе преобразования, а затем преобразовывать их обратно в реальные нули.

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

3. Согласованность типов данных: Убедитесь, что все столбцы имеют согласованные типы данных, чтобы избежать неожиданного поведения нулевых значений.

Проверка данных после преобразования

После выполнения операции поворота проверьте результаты:

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

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

Пошаговое руководство по реализации

Вот полный рабочий процесс для достижения желаемого преобразования:

Метод 1: Поворот в Power Query (рекомендуется)

1. Откройте редактор Power Query

   • Нажмите Преобразование данных на ленте Power BI
   • Выберите вашу таблицу, содержащую столбец “Название серии”

2. Подготовьте ваши данные

   • Убедитесь, что у вас есть столбец, уникально идентифицирующий каждую запись (например, “Категория найма”)
   • Убедитесь, что ваш столбец “Название серии” и столбец данных правильно отформатированы

3. Примените преобразование поворота

   • Выберите ваш столбец “Название серии”
   • Перейдите на вкладку Преобразование → Поворот столбца
   • Установите Столбец значений в ваш столбец данных
   • В Расширенных параметрах выберите “Не агрегировать”
   • Нажмите ОК

4. Очистите результат

   • Удалите все ненужные столбцы
   • Переименуйте столбцы для ясности
   • Нажмите Закрыть и применить, чтобы сохранить преобразование

Метод 2: Расширенный Power Query с пользовательской логикой

1. Добавьте индексный столбец

   • Щелкните правой кнопкой мыши вашу таблицу → Добавить столбец → Индексный столбец
   • Это поможет отслеживать исходные позиции строк

2. Создайте пользовательский столбец

   • Добавьте столбец, который объединяет название серии с индексом
   • Используйте: = [Series Name] & "_" & Text.From([Index])

3. Поверните по пользовательскому столбцу

   • Выберите ваш пользовательский столбец
   • Используйте Поворот столбца с вашим столбцом данных
   • Установите “Не агрегировать”

4. Очистите и завершите

   • Удалите индексный столбец
   • Правильно обработайте оставшиеся нулевые значения

Лучшие практики и соображения по производительности

Когда использовать Power Query вместо DAX

• Преобразования Power Query: Лучше всего для преобразования данных, подготовки данных и создания конечной структуры столбцов
• Расчеты DAX: Лучше всего для бизнес-логики, расчетов и динамической агрегации в визуализациях

Советы по оптимизации производительности

1. Минимизируйте преобразования: Применяйте все необходимые преобразования в одном шаге Power Query, а не в нескольких шагах
2. Используйте встроенные функции: Предпочитайте встроенные функции Power Query пользовательскому коду M, когда это возможно
3. Работа с большими наборами данных: Для больших наборов данных рассмотрите возможность ранней фильтрации в конвейере преобразования
4. Настройки кэширования: Убедитесь, что кэширование Power Query настроено соответствующим образом для вашей стратегии обновления

Соображения качества данных

• Обработка нулевых значений: Будьте последовательны в отношении того, как вы обрабатываете нулевые значения на протяжении всего процесса преобразования
• Проверка данных: Всегда проверяйте преобразованные данные по исходному набору данных
• Документация: Документируйте шаги преобразования для будущего обслуживания

Заключение

Преобразование одного столбца с несколькими значениями в отдельные столбцы в Power BI эффективно достигается через преобразование поворота столбца в Power Query с настройкой “Не агрегировать”. Этот метод сохраняет отдельные точки данных, создавая нулевые значения там, где это необходимо, идеально решая ваш сценарий с сериями “Список”, “Все”, “Заказано” и “Отклонено”.

Для оптимальных результатов:

1. Используйте преобразования Power Query для преобразования данных
2. Применяйте опцию “Не агрегировать” в диалоговом окне поворота столбца
3. Проверяйте результаты для обеспечения целостности данных
4. Рассмотрите расширенные техники для сложных распределений данных

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

Источники

1. Сообщество Microsoft Power BI - Преобразование одного столбца в несколько столбцов таблицы
2. Data Bear - Power BI: Поворот и разворот столбцов
3. BI Gorilla - Разворот столбцов и сохранение нулевых значений в Power Query
4. Документация Microsoft - Поворот столбца в Power Query
5. Stack Overflow - Power BI | Power Query: как создать несколько столбцов из одного столбца значений
6. My Online Training Hub - Сценарии разворота в Power Query