Wie kann ich eine Liste auswerten, in der mehrere Spalten die gleiche Art von Information enthalten? Im folgenden Beispiel wurden in der oberen Liste die Daten zu jeder Firma in einer Zeile erfasst. Vor- und Nachname des Ansprechpartners sind in zwei Spalten abgelegt. So weit, so gut. Aber: Bei weiteren Ansprechpartnern stehen diese rechts daneben in zusätzlichen Spaltensets. Das Problem: Eine solche Datenstruktur lässt sich mit Pivot nicht auswerten. Und auch für den Re-Import in eine Datenbank ist sie ungeeignet. Wie bringe ich die Daten automatisiert in die in der blauen Tabelle gezeigte passende Struktur? Dazu muss ich die Inhalte aus Spalten auf Zeilen verteilen mit Hilfe von Power Query und den Befehlen Entpivotieren und Pivotieren.
Spaltensets des gleichen Typs in einer Zeile sind für eine Weiterverarbeitung ungeeignet. Jedes Set benötigt eine eigene Zeile.
Wie ich das auch für eine variable Anzahl von Spaltensets löse, zeige ich in der folgenden Anleitung.
1) Die Daten in Power Query einlesen und entpivotieren
Zunächst lese ich die Daten in Power Query ein und entpivotiere alle Ansprechpartnerspalten so, dass sowohl die Anzahl der Spalten in einem Set als auch die Anzahl der Ansprechpartner flexibel groß sein kann.
Mit diesen Schritten sorge ich für ein flexibles Entpivotieren der Ansprechpartnerdaten:
- In Excel wähle ich im Register Daten > Aus Tabelle/Bereich.
Um sich mühsames Copy & Paste zu ersparen, wird die Tabelle nach Power Query eingelesen
- Es öffnet sich der Power Query-Editor. Dort markiere ich alle Spalten, die keine Ansprechpartnerspalten sind. Hier im Beispiel die Spalten Firma bis Ort.
- Im Register Transformieren klicke ich auf Andere Spalten entpivotieren.
Mit dem Befehl Andere Spalte entpivotieren werden die Spaltensets (unabhängig von ihrer Anzahl) zu Zeilen
Als Folge des Entpivotierens entstehen aus den x gefüllten Ansprechpartnerspalten pro Firma x-1 neue Zeilen und zwei neue Spalten. Die erste Spalte namens Attribut enthält die vorherigen Spaltenüberschriften wie Vorname1, Name1, Vorname2 etc. Die zweite Spalte namens Wert listet die Information zum Ansprechpartner auf: Vorname oder Nachname.
2) Informationen zum Spaltenset sichern
Die Spalte Attribut enthält einerseits die Information, ob es sich um einen Vornamen oder Nachnamen handelt. Andererseits liefert sie eine Identifizierung, hier in Form einer Nummer für das jeweilige Set. Diese beiden Informationen teile ich auf zwei Spalten auf.
- Dazu markiere ich die Spalte Attribut.
- Anschließend wähle ich im Register Transformieren > Spalte teilen > Nach Wechsel von Nicht-Ziffer zu Ziffer.
Im Ergebnis dessen entstehen aus der Spalte Attribut die Spalten Attribut.1 und Attribut.2.
Mit dem Befehl Spalte teilen trenne ich die ursprünglichen Spaltenbezeichnungen von der Setnummer
3) Neue Spalten für Nachname und Vorname generieren
Neue Spalten mit den Bezeichnungen Nachname und Name entstehen durch Pivotieren der Spalte Attribut.1.
Die Spalte Attribut.2 dient während des Pivotierens dazu, die jeweiligen Ansprechpartnerdaten – hier Nachname und Vorname – zusammenzuhalten.
- Ich markiere die Spalte Attribut.1.
- Anschließend wähle ich im Register Transformieren > Spalte pivotieren.
Mit Spalte pivotieren erzeuge ich die zwei neuen Spalten Vorname und Name mit den Daten aus der Spalte Wert
- Die Spalte Attribut.2 wird nun nicht mehr benötigt. Daher lösche ich sie über Start > Spalten entfernen.
Für jeden Ansprechpartner gibt es nun eine eigene Zeile mit den entsprechenden Firmendaten und nur die Datentypen müssen noch angepasst werden
Fazit: Mit dieser Methode kann ich beliebig viele Spaltensets mit beliebig vielen Spalten entpivotieren und so die Daten für eine Analyse oder einen Re-Import in eine Datenbank in die richtige Struktur bringen. Der Schlüssel dafür: Inhalte aus Spalten auf Zeilen verteilen mit Power Query.
Mehr nützliche Praxis-Techniken in Power Query …
… zeige ich bei den Excel-Kompetenztagen vom 27. bis 29. Juni 2022 in Fulda in der Kursreihe 3. Hier der Flyer inkl. Kursplan zum Herunterladen.
Flyer zu den Excel-Kompetenztagen 2022 per Klick auf das Bild herunterladen
