☰

Как транспонировать таблицу Excel в уникальные столбцы из множества дублирующихся строк? (предпочтительно в PowerQuery)

tag-icon • tag-icon microsoft-excel power-query
Как транспонировать таблицу Excel в уникальные столбцы из множества дублирующихся строк? (предпочтительно в PowerQuery)

Я искал везде и много пробовал, похоже, большинству людей не нужно решение этой проблемы :-( У меня есть таблица с двумя столбцами. Столбец A (TAG) имеет 4 различных значения, повторяющихся в этом шаблоне снова и снова. Они станут моими различными именами столбцов. Столбец B (VALUE) содержит совпадающие значения для этих тегов.

Пример таблицы:
Исходная таблица

Теперь я просто хотел бы создать таблицу, которая будет выглядеть следующим образом: (Каждое отдельное значение из строки TAG становится заголовком столбца, а все значения из столбца VALUE становятся отдельными значениями под этими новыми столбцами)

Желаемая целевая таблица:
Таблица желаемых целей

Я бы предпочел выполнить все эти транспонирования в PowerQuery сразу после импорта CSV-файла, из которого получены данные, но было бы неплохо сделать это и позже в какой-нибудь операции с диапазоном/таблицей.

Любая помощь могла бы быть полезна :-)

решение1

В Power Query вы бы свели столбец TAG, используя столбец VALUE в качестве значений и no aggregation.

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

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

Чтобы сделать это, а также учесть, что ТЭГИ не всегда располагаются в одном и том же порядке, вы можете

  • Группировать по тегу
  • добавить столбец индекса в каждую подтаблицу
  • Развернуть таблицы
  • Сводка по TAG без агрегации
  • Удалить столбец «Индекс»

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

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

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//Group By Tage
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TAG"}, {

        //add an Index column to each subtable
        {"with Index", each Table.AddIndexColumn(_,"Index",0,1), type table [TAG=nullable text, VALUE=nullable text, Index=Int64.Type]}}),

//Expand the sub tables
    #"Expanded with Index" = Table.ExpandTableColumn(#"Grouped Rows", "with Index", {"VALUE", "Index"}),

//Pivot with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Expanded with Index", List.Distinct(#"Expanded with Index"[TAG]), "TAG", "VALUE"),

//Remove the Index column
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

введите описание изображения здесь

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

Пользовательская функция
вставьте в пустой запрос и переименуйте как в комментариях к коду

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Основной код

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//call custom pivot function
    pivot = fnPivotAll(#"Changed Type","TAG","VALUE")
in
    pivot

Связанный контент