Text Kombinieren / Pivotieren

Heute geht es um Datenbearbeitung mit Excel.

Hauptproblem

Ich habe eine Tabelle mit mehreren Spalten.

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

Sondern die einzelnen Einträge behalten:

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.

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.

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.

Anschließend ändere ich die 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}})

Tabelle Gruppieren

Jetzt wähle ich die erste Spalte aus und drücke auf gruppieren nach.

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"

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.