☰

Wie transponiere ich eine Excel-Tabelle aus vielen doppelten Zeilen in eindeutige Spalten? (vorzugsweise in PowerQuery)

tag-icon • tag-icon microsoft-excel power-query
Wie transponiere ich eine Excel-Tabelle aus vielen doppelten Zeilen in eindeutige Spalten? (vorzugsweise in PowerQuery)

Ich habe überall gesucht und viel ausprobiert, aber anscheinend brauchen die meisten Leute keine Lösung für dieses Problem :-( Ich habe eine Tabelle mit zwei Spalten. Spalte A (TAG) hat 4 verschiedene Werte, die sich in diesem Muster immer wieder wiederholen. Diese sollen meine unterschiedlichen Spaltennamen werden. Spalte B (VALUE) hat die passenden Werte für diese Tags.

Beispieltabelle:
Quelltabelle

Jetzt möchte ich einfach eine Tabelle erstellen, die so aussieht: (Jeder einzelne Wert aus der Zeile TAG wird zu einer Spaltenüberschrift und alle Werte aus der Spalte VALUE werden zu den einzelnen Werten unter diesen neuen Spalten.)

Gewünschte Zieltabelle:
Gewünschte Zieltabelle

Ich würde diese gesamte Transposition vorzugsweise bereits in PowerQuery durchführen, wenn ich die CSV-Datei importiere, aus der die Daten stammen. Es wäre jedoch auch in Ordnung, dies später in einer Bereichs-/Tabellenoperation zu tun.

Ich wäre für jede Hilfe WIRKLICH dankbar :-)

Antwort1

In Power Query würden Sie die Spalte TAG pivotieren, mit der Spalte VALUE als Werte und no aggregation.

Wenn Sie jedoch genau dies mit Ihrem Datensatz tun, werden Fehlerwerte zurückgegeben, da Sie mehrere Einträge für denselben Pivot-Wert haben.

Dies kann durch das Hinzufügen einer Indexspalte vermieden werden, die die einzelnen Tag-Einträge fortlaufend nummeriert.

Um dies zu tun und auch zu berücksichtigen, dass die TAGs nicht immer in der gleichen Reihenfolge sind, können Sie

  • Nach Tag gruppieren
  • Fügen Sie jeder Untertabelle eine Indexspalte hinzu
  • Erweitern Sie die Tabellen
  • Pivot auf TAG ohne Aggregation
  • Entfernen Sie die Indexspalte

Sehen Sie sich zum besseren Verständnis die Kommentare und die angewandten Schritte im folgenden Code an.

Wenn diese Methode für Ihren Datensatz zu langsam ist, steht eine benutzerdefinierte Funktion zur Verfügung, die möglicherweise schneller ausgeführt wird.

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"

Bildbeschreibung hier eingeben

Bearbeitet, um eine benutzerdefinierte Funktion zum Ausführen eines Nichtaggregations-Pivots anzuzeigen

Benutzerdefinierte Funktion
in eine leere Abfrage einfügen und wie in den Codekommentaren umbenennen

//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"

Haupt code

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

verwandte Informationen