Deployn

Text Kombinieren / Pivotieren

Texte kombinieren bzw. pivotieren mit Excel und PowerQuery

Text Kombinieren / Pivotieren-heroimage

Heute geht es um Datenbearbeitung mit Excel.

Hauptproblem

Ich habe eine Tabelle mit mehreren Spalten.

Spalte 1Spalte 2
A1
A2
B3
A2
B4

Diese Tabelle soll pivotiert werden. Jedoch möchte ich nicht die Summen ziehen:

Spalte 1Spalte 2
A5
B7

Sondern die einzelnen Einträge behalten:

Spalte 1Spalte 2
A1; 2; 2
B3; 4

Datengrundlage

Bei f√ľnf Zeilen ist dieser Vorgang vermutlich manuell schneller fertig als das Aufsetzen einer Pivot-Tabelle oder von PowerQuery. Was ist aber, wenn es viele Daten gibt.

Dazu lade ich mir als Beispiel von der Polizei Berlin eine CSV-Datei zu den Fahrraddiebstählen seit dem 01.01.2021 in Berlin herunter.

Die CSV-Datei hat zu diesem Zeitpunkt 23873 Zeilen. Manuelle Bearbeitung ist nicht mehr einfach möglich.

Vorbereitung

Zunächst speichere ich die Datei so wie ist als *.xlsx ab. Danach markiere ich Spalte A und nutze die Funktion Text in Spalten, um die Werte bei einem , in mehrere Spalten zu trennen.

CSV-Datei trennen

F√ľr das, was ich zeigen m√∂chte, reichen drei Spalten. Ich l√∂sche alle Spalten bis auf ‚ÄúTATZEIT_ANFANG_DATUM‚ÄĚ,‚ÄúTATZEIT_ANFANG_STUNDE‚ÄĚ und ‚ÄúSCHADENSH√ĖHE‚ÄĚ.

Anschließend wandle ich die den Datenbereich in eine Tabelle um (STRG+T).

Datenanalyse

Tats√§chlich bringt das, was ich vorhabe, bei dieser Tabelle recht wenig. W√ľrde ich hier tats√§chlich irgendwas analysieren wollen, w√ľrde ich vermutlich eine Pivot-Tabelle erstellen.

So kann man beispielsweise schnell sehen, zu welcher Uhrzeit der höchste Schaden entsteht.

Pivot Tabelle

Einige Medienunternehmen w√ľrden den BlogPost vermutlich ungef√§hr so beginnen: ‚ÄúSie w√ľrden niemals glauben, zu welcher Uhrzeit die meisten Fahrr√§der gestohlen werden!‚Äú. Antwort: H√§ufigster Tatbeginn ist um 18 Uhr. Dabei wird nat√ľrlich die Dunkelziffer au√üen vor gelassen.

PowerQuery

Nun zum eigentlichen Vorhaben. Ich lade die Tabelle in PowerQuery rein. Dazu die Tabelle anklicken und dann unter dem Men√ľpunkt ‚ÄúDaten‚ÄĚ Aus Tabelle / Bereich ausw√§hlen.

Die Tabelle wird im Power Query-Editor geöffnet.

Power Query-Editor

Anschließend ändere ich die Datentypen.

Datentypen

= Table.TransformColumnTypes(Quelle,{{"TATZEIT_ANFANG_DATUM", type date}, {"TATZEIT_ANFANG_STUNDE", Int64.Type}, {"SCHADENSHOEHE", Currency.Type}})

Text.Combine

Als N√§chstes f√ľge ich eine neue Spalte hinzu. In dieser Nutze ich endlich die Funktion Text.Combine. Hier aber in ihrer gew√∂hnlichen Variante.

= Table.AddColumn(#"Geänderter Typ", "TATZEIT_ANFANG", each Text.Combine({Text.From([TATZEIT_ANFANG_DATUM], "de-DE"), " ", Text.PadStart(Text.From([TATZEIT_ANFANG_STUNDE], "de-DE"), 2, "0"), ":00:00"}), type text)

Damit f√ľge ich das Datum und die Uhrzeit zusammen in eine Spalte.

Mit Text.Combine habe ich so die Spalten aus jeder Zeile kombiniert.

Die einzelnen Spalten kann ich nun entfernen.

= Table.SelectColumns(#"Benutzerdefinierte Spalte hinzugef√ľgt",{"TATZEIT_ANFANG","SCHADENSHOEHE"})

Ich kann die Tabelle nun aufsteigend sortieren.

= Table.Sort(#"Andere entfernte Spalten",{{"TATZEIT_ANFANG", Order.Ascending}})

Sortierte Tabelle

Tabelle Gruppieren

Jetzt w√§hle ich die erste Spalte aus und dr√ľcke auf gruppieren nach.

Tabelle Gruppieren

Ich gruppiere nach der Summe der Schadensh√∂he. Anschlie√üend erhalte ich auch die Summen. Wenn ich aber Texte aneinandergereiht haben m√∂chte, stelle ich den Datentyp auf Text um und √§ndere in der Formel die Funktion List.Sum zu Text.Combine. Hier muss auch ein zweiter Parameter √ľbergeben werden.

Am Ende trenne ich nach diesem Parameter den Inhalt der Spalte und kann dann die fertige Tabelle laden.

Im erweiterten Editor sieht die Formel folgendermaßen aus:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"TATZEIT_ANFANG_DATUM", type date}, {"TATZEIT_ANFANG_STUNDE", Int64.Type}, {"SCHADENSHOEHE", Currency.Type}}),
    #"Benutzerdefinierte Spalte hinzugef√ľgt" = Table.AddColumn(#"Ge√§nderter Typ", "TATZEIT_ANFANG", each Text.Combine({Text.From([TATZEIT_ANFANG_DATUM], "de-DE"), " ", Text.PadStart(Text.From([TATZEIT_ANFANG_STUNDE], "de-DE"), 2, "0"), ":00:00"}), type text),
    #"Ge√§nderter Typ1" = Table.TransformColumnTypes(#"Benutzerdefinierte Spalte hinzugef√ľgt",{{"TATZEIT_ANFANG", type datetime}}),
    #"Andere entfernte Spalten" = Table.SelectColumns(#"Geänderter Typ1",{ "TATZEIT_ANFANG","SCHADENSHOEHE"}),
    #"Sortierte Zeilen" = Table.Sort(#"Andere entfernte Spalten",{{"TATZEIT_ANFANG", Order.Ascending}}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Sortierte Zeilen",{{"SCHADENSHOEHE", type text}}),
    #"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ2", {"TATZEIT_ANFANG"}, {{"SCHADEN", each Text.Combine([SCHADENSHOEHE],"; "), type text}}),
    #"Sortierte Zeilen1" = Table.Sort(#"Gruppierte Zeilen",{{"TATZEIT_ANFANG", Order.Ascending}}),
    #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Sortierte Zeilen1", "SCHADEN", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"SCHADEN.1", "SCHADEN.2", "SCHADEN.3", "SCHADEN.4", "SCHADEN.5", "SCHADEN.6", "SCHADEN.7"}),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"SCHADEN.1", Int64.Type}, {"SCHADEN.2", Int64.Type}, {"SCHADEN.3", Int64.Type}, {"SCHADEN.4", Int64.Type}, {"SCHADEN.5", Int64.Type}, {"SCHADEN.6", Int64.Type}, {"SCHADEN.7", Int64.Type}})
in
    #"Geänderter Typ3"

Entg√ľltige Tabelle

In dieser Tabelle sehe, dass es maximal sieben Einträge zu einer Uhrzeit gibt und an welchem Wert welche Schadenshöhe wie aufgeteilt wurde.

Bei Fragen / Anmerkungen usw. am besten in den Kommentaren schreiben.


Diese Website verwendet Cookies. Diese sind notwendig, um die Funktionalität der Website zu gewährleisten. Weitere Informationen finden Sie in der Datenschutzerklärung