Daten, die aus einer Druckdatei oder per Zwischenablage aus einer PDF-Datei oder Webseite in ein Excel-Tabellenblatt eingefügt werden, sind oft nicht so aufgebaut, dass Sie direkt mit ihnen weiterarbeiten können. Ein Thema sind zusammengehörige Daten, die in einer Spalte und in mehreren Zeilen untereinanderstehen. In unserem Blogbeitrag vom Dezember 2021 wurde eine Lösung mit Excel vorgestellt. In diesem Tipp erfahren Sie, wie Sie mit Power Query die zusammengehörigen Daten jeweils auf eine Zeile und mehrere Spalten verteilen.

Beispieldaten

Die Aufteilung von Daten aus einer Spalte in mehrere Zeilen soll anhand von Firmenadressen gezeigt werden. Die Angaben zu einer Firma stehen in einer Spalte und umfassen jeweils 3 Zeilen. Die Ausgangsdaten sehen so aus:

Um es Ihnen zu erleichtern, die zusammengehörigen Daten einer Firma erkennen, haben wir in unserem Beispiel den zusammengehörigen Daten, den gleichen Farbhintergrund gegeben.

Die Firmendaten sollen so umgestellt werden, dass eine Tabelle mit einer Zeile und 3 Spalten für jede Firma erzeugt wird. Die Überschriften der Tabelle sollen Firma, Straße und PLZ/Ort lauten.

Lösung mit Power Query

Die Lösung mit Power Query sieht so aus:

1. Vergeben Sie für den Zellbereich A1:A16 einen Namen. In diesem Beispiel wird der Name Importadressen
2. Klicken Sie im Menüband auf der Registerkarte Daten in der Gruppe Daten abrufen und transformieren auf die Schaltfläche Vom Blatt. Der Editor für Power Query wird geöffnet.
3. Als ersten Schritt soll die erste Zeile als Überschrift verwendet werden. Klicken Sie auf der Registerkarte Start in der Gruppe Transformieren auf die Schaltfläche Erste Zeile als Überschriften verwenden.
4. Fügen Sie zur besseren Identifizierung der Zeilen eine Indexspalte, beginnend mit 1, hinzu. Klicken Sie dazu im Menüband auf der Registerkarte Spalte hinzufügen in der Gruppe Alllgemein auf die Schaltfläche Indexspalte Von 1.
5. Um die 3 benötigten Spalten Firma, Straße und PLZ/Ort zu erhalten, soll später eine noch zu erzeugende Spalte pivotiert werden. Die neue Spalte soll mit Hilfe einer Formel erzeugt werden. Klicken Sie auf der Registerkarte Spalte hinzufügen in der Gruppe Allgemein auf die Schaltfläche Benutzerdefinierte Spalte: Tragen Sie in dem Dialogbild in dem Eingabefeld Neuer Spaltenname den Begriff Überschriften Im Feld Benutzerdefinierte Spaltenformel geben Sie die folgende Formel ein:= if Number.Mod([Index],3)=1 then “Firma2” else if Number.Mod([Index],3)=2 then “Straße” else “PLZ/Ort”Erläuterung: Wenn die Zahl in der Indexspalte durch 3 dividiert und der Rest 1 beträgt, soll in der Zeile Firma2 erscheinen. Wenn der Rest einen Wert von 2 hat, soll Straße eingetragen werden und ansonsten PLZ/Ort. Der Begriff Firma2 wurde gewählt, weil es bereits eine Spalte Firma in der Tabelle gibt.
   Das Dialogbild dazu sieht dann so aus:
   
6. Nachdem Sie die neue Spalte erzeugt worden ist, soll diese pivotiert werden, um neue Spalten in der Tabelle zu generieren. Beim Pivotieren werden so viel neue Spalten in einer Tabelle erzeugt, wie es unterschiedliche Einträge in der zu pivotierenden Spalte gibt. In der Spalte Überschriften sind es die Einträge Firma2, Straße und PLZ/Ort. Markieren Sie die Spalte Überschriften.
7. Klicken Sie auf der Registerkarte Transformieren in der Gruppe Beliebige Spalte auf die Schaltfläche Spalte pivotieren.
8. Wählen Sie im Dialogbild die Wertespalte Firma aus und wählen Sie als erweiterte Option den Eintrag Nicht aggregieren Das Dialogbild sieht dann so aus:
   
9. Nach einem Klick auf die Schaltfläche OK hat die Tabelle folgenden Aussehen:
   
10. Im nächsten Schritt sollen die leeren Zellen in der Tabelle aufgefüllt werden. Sie erkennen Sie an dem Eintrag null. Markieren Sie die Spalte Firma2 und rufen Sie mit der rechten Maustaste das Kontextmenü auf: Klicken Sie auf den Menüpunkt Ausfüllen und dann auf Nach unten.
11. Markieren Sie die beiden Spalten Straße und PLZ/Ort.
12. Rufen Sie mit der rechten Maustaste erneut das Kontextmenü auf: Klicken Sie auf den Menüpunkt Ausfüllen und dieses Mal auf Nach oben.
13. In einem weiteren Schritt werden die überflüssigen Zeilen nach einem bestimmten Muster gelöscht. Klicken Sie auf der Registerkarte Start in der Gruppe Zeilen verringern auf den nach unten zeigendem Pfeil neben der Schaltfläche Zeilen entfernen. Wählen Sie Menüpunkt Alternative Zeilen entfernen. Es erscheint das folgende Dialogbild:
    
14. Die erste zu entfernende Zeile ist die Zeile 2, danach sollen 2 Zeilen entfernt werden und eine Zeile beibehalten werden. Das Dialolgbild sieht so aus:
    
    Klicken Sie auf die Schaltfläche OK.
15. Löschen die abschließend noch die Spalte Index und benennen Sie die Spalte Firma2 in Firma Die Tabelle sieht danach so aus:
    
16. Schließen und laden Sie die Daten in ein Excel-Tabellenblatt.

Hinweis

Die hier vorgestellte Lösung funktioniert nur, wenn die Anzahl der zugehörigen Zellen pro Firma identisch ist.