In jeweils einer Zelle (mehrspaltig)
durch [Alt] [Return] getrennte Werte in mehrere Zeilen splitten
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
In einem Forum wurde die anfangs recht einfach scheinende Anfrage gestellt, wie die in der Überschrift dieses Beitrages beschriebenen Aufgabe zu lösen sei. Bevor eine Muster-xlsx vom Fragesteller eingestellt worden ist, wurden verschiedene Antworten auf der Basis VBA und ein Hinweis auf Power Query gegeben. Der Fragesteller hat sich letztendlich für eine recht außergewöhnliche Lösung entschieden, wo die Daten erst nach Word transferiert wurden und nach einer Bearbeitung zurück nach Excel importiert worden sind. Na ja, jedem das seine … 😯
Ich habe mir vorgenommen, dieses Problem mit Power Query zu lösen. Der einzig wirklich existierende „Stolperstein” ist das Anführungszeichen, welches ja mehrfach als einzelne, logische „Zeile” innerhalb einer Zelle existiert. Selbst wenn die entsprechende Spalte in Power Query als Text ausgezeichnet worden ist, wird PQ diese Zeile „unterschlagen” und auch ansonsten nicht das tun, was erwartungsgemäß sein sollte. Aber sehen Sie selbst …
Einstieg und Vorbereitung
Beginnen Sie damit, diese Muster-xlsx (eine Kopie der Datei aus dem Forum) nach Power Query zu importieren. Übrigens: Ich formatierte den Datenbereich grundsätzlich zuerst in Excel als „Intelligente” Tabelle, bevor ich die Daten nach PQ importiere. Das spart mitunter irritierende Nachfragen seitens des Programms. Nach dem Import stellt sich das im Power Query-Editor so dar:
Die Daten direkt nach dem Import
Einsteiger in Sachen Power Query werden sich jetzt gewiss denken, dass hier nur die einzelnen Spalten derart aufgeteilt, gesplittet werden müssen, dass die Zeilenschaltungen jeweils eine neue Zeile erzeugen. Das hört sich gut an, führt aber nach dem Teilen der ersten Spalte zu diesem Ergebnis:
Ein gewiss unerwartetes Ergebnis 🙁
Sie erkennen, dass zwar Spalte1 nach jeder Zeilenschaltung eine neue Zeile generiert worden ist, aber in Spalte2 nach wie vor mehrere (logische) Zeilen in einer Zelle sind. Natürlich werden Sie rasch auf die Idee kommen, gleich beide Spalten zu markieren und dann die den Split-Vorgang durchzuführen; aber wenn sie mehr als eine Spalte markiert haben, steht Ihnen der Punkt Spalte teilen nicht zur Verfügung. 🙁 Den detaillierten Weg, die Zeilenschaltungen als Trenner zu verwenden, werde ich weiter unten beschreiben. Und natürlich auch, wie die Aufteilung in neue Zeilen statt Spalten machbar ist.
Die logische Folgerung: Jede Spalte muss einzeln in dieser Weise „behandelt” werden. Darum löschen Sie entweder im rechten Seitenfenster die bislang fehlerhaften Schritte oder (wenn sie es sich einfach machen wollen) schließen Sie diese Abfrage ohne sie zu speichern und beginnen noch einmal ganz von vorne.
Sie befinden sich jetzt wieder an dem Punkt, wo die Daten frisch in den Editor importiert worden sind. Idealerweise werden sie nun gleich an dieser Stelle die Abfrage sichern. Dazu ein Klick auf den Text unterhalb des Symbols Schließen & laden und wählen Sie Schließen & laden in… aus. Im Dialogfenster Klicken Sie dann auf Nur Verbindung erstellen und bestätigen Sie mit OK. Sie können das direkt übrigens auch über den Menüpunkt Datei erreichen.
Da Power Query durch diesen Vorgang geschlossen wurde, öffnen Sie die Abfrage anschließend auf beliebige Weise, beispielsweise durch einen Doppelklick im rechten Seitenfenster. Erweitern Sie das linke Seitenfenster durch einen Klick auf den senkrecht stehenden Text Abfragen. Rechtsklick auf die Zeile Tabelle1 und wählen Sie dann im Kontextmenü Verweis. Wiederholen Sie diesen Vorgang sofort noch einmal. Obwohl ich durchaus in Sachen PQ erfahren bin, ändere ich der Klarheit und Transparenz wegen den Namen der beiden neu erstellten Abfragen auf Tabelle1 (Spalte1) und Tabelle1 (Spalte2). Das geht gut per Rechtsklick oder F2.
Da ja gewiss immer noch die Abfrage Tabelle1 (Spalte2) markiert ist, ist diese auch aktiv; ein Klick in die Überschrift Spalte1 und die Taste Entf oder (gleich) ein Rechtsklick in die Überschrift und im Kontextmenü Entfernen. So bleibt nur die Spalte2 in dieser Abfrage erhalten (was ja auch dem Namen der Abfrage entspricht).
Wechseln Sie zur Abfrage Tabelle1 (Spalte1) (per Mausklick) und dort löschen Sie naturgemäß Spalte2. An dieser Stelle ein Hinweis, den ich Ihnen auch nur geben kann, weil ich über die schon oben angesprochene Besonderheit in Sachen Anführungszeichen gestolpert bin: Berechnen Sie einfach einmal die Anzahl der Zeilen, die nach dem Splitten entstanden sein müssen. Hier wären das dann für die 4 „echten” Zeilen: 3 + 3 + 4 + 5, also 15 Zeilen. Bitte merken … 😉
Die Überschrift Spalte1 ist ja markiert darum im Register Home in der Gruppe Transformieren ein Klick auf Spalte teilen. Im Kontextmenü wählen Sie natürlich Nach Trennzeichen. Im Dialogfenster sind schon verschiedene Positionen ausgefüllt. Das Trennzeichen #(lf) als Synonym für die Zeilenschaltung ist seitens Power Query korrekt erkannt worden:
Vieles erkennt Power Query automatisch so, wie gewollt
Bei Aufteilen ist Bei jedem Vorkommen des Trennzeichens markiert, auch das ist richtig. Bei Erweiterte Optionen ändern Sie die Auswahl bei Aufteilen in auf Zeilen und Klicken anschließend auf OK:
Nicht so gut … 🙁
… Und es sind nur zwölf statt der erwarteten 15 Zeilen. Insbesondere die Zeilen 9 und 12 sind mehrzeilig geblieben und die jeweils erste logische Zeile der Zelle ist leer. Wenn Sie etwas Ursachenforschung betreiben werden Sie erkennen, dass die Anführungszeichen die Fehlerursache sein müssen. Ich empfinde das als Fehler des Power Query, denn der Inhalt der Spalte ist auch schon im Ursprung (Tabelle1) bereits als Text formatiert und aus diesen Grund sollte auch ein " als normales Zeichen behandelt werden. Also gilt es, diesen Bug, diese Klippe möglichst sicher zu umschiffen.
Fehlerkorrektur
Löschen Sie erst einmal die letzten Schritte. Dazu führen sie im rechten Seitenfenster einen Rechtsklick auf die Zeile Spalte nach Trennzeichen teilen durch und wählen Bis zum Ende löschen; die Sicherheitsabfrage bestätigen Sie mit Löschen. Die Gänsefüßchen sind jetzt wieder in den jeweiligen Zellen sichtbar, also enthalten. Wechseln Sie per Mausklick zur Ursprungsabfrage Tabelle1. Markieren Sie beide Spalten und führen Sie in eine der Überschriften einen Rechtsklick durch. Werte ersetzen… und geben Sie bei Zu suchender Wert das Anführungszeichen " ein, bei Ersetzen durch beispielsweise die Tilde ~, sofern dieses Zeichen nicht im Text der Spalten vorkommt. Im Zweifel verwende ich stattdessen bei diesen Gelegenheiten stets ßß, weil diese Zeichenfolge in der deutschen Sprache (und gewiss auch nicht in Fremdsprachen) vorkommen kann.
Wechseln Sie nun wiederum zur Abfrage Tabelle1 (Spalte1) und jetzt werden Sie gewiss erkennen, warum ich anfangs kein Duplikat der Tabelle1 erstellt habe, sondern einen Verweis. Die Änderungen wurden automatisch übernommen. 😎 Wiederholen Sie nun noch einmal den Vorgang in Sachen Spalte teilen und sie werden erkennen, dass nun die korrekte Anzahl von 15 Zeilen gegeben ist.
Wechseln Sie nun zum Register Spalte hinzufügen und wählen dort in der Gruppe Allgemein den Punkt Indexspalte. Jeder einzelnen Zeile wurde eine fortlaufende Nummer, beginnend mit 0 (null), in der Spalte Index hinzugefügt.
Wechseln Sie zur Abfrage Tabelle1 (Spalte2) und über Home | Spalte teilen | Nach Trennzeichen gelangen sie wieder in den entsprechenden Dialog. Dieser stellt sich nun aber etwas anders dar, denn als Trennzeichen wird hier automatisch das Zeichen & vorgeschlagen. Das ist natürlich nicht richtig, es soll ja wieder die Zeilenschaltung sein.
Löschen Sie das Zeichen & im Textfeld und Klicken Sie dann auf Erweiterte Optionen. Bei der Gelegenheit können Sie auch gleich die Zeilen statt der Spalten markieren. Entscheidend aber ist, dass sie bei Mithilfe von Sonderzeichen teilen ein Häkchen setzen. Ein Klick auf Sonderzeichen einfügen ▼ erweitert diese Auswahl und dort markieren Sie Zeilenvorschub. Automatisch wird nun ganz oben bei Trennzeichen eingeben oder auswählen die Zeichenfolge #(lf) in das Textfeld eingefügt und sie können nun mit OK bestätigen. Sie sehen, auch hier sind es 15 Zeilen. In dieser Query werden sie ebenfalls eine Indexspalte (wie gehabt) generieren.
Speichern Sie nun noch einmal die beiden generierten Verweis-Abfragen als Nur Verbindung. So vermeiden Sie, dass zum Abschluss nicht erforderliche, unnötige Tabellen in Excel gespeichert werden. Übrigens: Wahrscheinlich reicht es,eine der beiden Abfragen in diesem Modus zu sichern. Meist wird die andere Query gleichermaßen als Nur Verbindung gespeichert.
Endspurt
Jetzt ist die Vorarbeit erledigt. Nach dem Schließen & laden werden Sie die Abfrage Tabelle1 (Spalte1) öffnen. Ein Klick auf Kombinieren und im DropDown erweitern Sie den Punkt Abfragen zusammenführen | Abfragen als neue Abfrage zusammenführen. Es öffnet sich dieses Dialog-Fenster:
Der initiale Zusammenführen-Dialog
Der Sinn dieses Vorgehens: Die beiden einzelnen, neu erstellten Abfragen müssen wieder zu einer einzigen, zusammenhängenden Query assembliert werden. Im oberen Fenster sehen Sie einen Auszug jeder Daten, die in der aktuellen Abfrage enthalten sind. Oberhalb dieses „Kastens” ist auch noch der Name der Abfrage angegeben. Wählen Sie nun im mittigen Textfeld die zweite der verknüpften Queries: Tabelle1 (Spalte2) und das untere (große) Fenster wird mit den entsprechenden Daten gefüllt.
Klicken Sie nun im oberen (großen) Fenster als auch unten in die Spalte Index. Damit weisen Sie PQ an, die jeweils zusammengehörenden Werte dieser Spalten so zu verknüpfen, dass die Werte der gleichen Zeile (des identischen Index) korrekt zugeordnet werden. – Sehr weit unten im Fenster sehen Sie die Textzeile Join-Art und darunter in einem Textfeld die Auswahl Linker äußerer Join mit der Erklärung, dass alle Zeilen der ersten (oberen) Abfrage verwendet und die passenden Zeilen der anderen Query angezeigt werden:
Der Zusammenführen-Dialog direkt vor dem Schließen
OK und das Ergebnis ist für Sie, wenn Sie das Kombinieren zum ersten Mal durchführen, gewiss nicht wirklich transparent:
Nur der Inhalt „Table” (Tabelle) in der neuen Spalte …
Erweitern Sie die Überschrift dieser neuen, dritten Spalte durch einen Klick auf den Doppelpfeil _ und im neu erschienenen Dialog löschen Sie das Häkchen bei Index und bei Ursprünglichen Spaltennamen als Präfix verwenden:
Diese Auswahl ist korrekt
OK und das Ergebnis sollte (erst einmal) überzeugen. „Erst einmal”, weil ja die Spalte Index im nächsten Schritt gelöscht wird und auch noch die Ersatz-Zeichen für die Gänsefüßchen zurück umgewandelt werden müssen. Und das geschieht genauso wie im ersten Durchgang, nur dass jetzt das Zeichen ~ (oder beispielsweise die Zeichenfolge ßß) durch ein " ersetzt werden. Bleibt noch ein Klick auf Schließen & laden und diese Abfrage wird in ein neues Excel Arbeitsblatt geschrieben. Nun können Sie die so erstellte Tabelle komplett markieren (geht gut mit ein oder zwei Mal StrgA), ausschneiden und an beliebiger Position auf einem gewünschten Arbeitsblatt einfügen.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen …