x Spalten zu 1 Spalte, Leerzeilen ("") entfernen
(→ „lückenlose Liste erstellen“)

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

In einem Forum wurde die Frage aufgeworfen, wie eine Kreuztabelle mit vielen Spalten so zu einer einspaltigen Liste umformatiert werden kann, dass die Überschriften erhalten bleiben und auch die Reihenfolge der Einträge nicht verändert wird. Zum Zeitpunkt der Erstellung dieses Beitrages (19.10.2019) lag eine Formel-Lösung vor und von einem weiteren Helfer der Hinweis auf Power Query. Ich habe die Gelegenheit genutzt, in PQ eine mögliche Lösung zu erstellen.

Laden Sie zu Beginn diese Datei aus unserem Blog-Server herunter. Es ist praktisch ein Nachbau der als HTML-Tabelle vorgestellten Daten im Forum. Klicken Sie in den Datenbereich A1:C9 und erstellen Sie auf beliebigem Wege (beispielsweise StrgT oder StrgL eine „Intelligente“ Tabelle. Achten Sie dabei darauf, dass in diesem Fall das Häkchen bei Tabelle hat Überschriften nicht gesetzt ist (normalerweise auch die Vorgabe). Die erste Zeile, welche ja die eigentlichen Überschriften darstellt, soll ja nach der Umwandlung auch jeweils in die 1-spaltige Liste integriert und nicht als Überschrift gewertet werden. Importieren Sie nun diese eben erstellte Tabelle in Power Query. Direkt nach dem Import stellt sich das im Editor so dar:

Direkt nach dem Import

Auf den ersten Blick sieht das alles sehr „vernünftig“ aus. Wenn Sie schon etwas Erfahrung mit Power Query gemacht haben werden Sie aber wissen, dass wirklich leere Zeilen im Editor nicht leer dargestellt werden (wie hier teilweise in den Zeilen 4-8) sondern den Wert null enthalten. Hier ist das scheinbar ungewohnte Verhalten darin begründet, dass in den Original-Daten die scheinbar leeren Zellen die Formel ="" enthalten ist, was typischerweise in der Fachsprache als Leerstring bezeichnet wird. Die Zelle ist also nicht leer sondern enthält eine Zeichenfolge, einen Text der Länge null. 💡 

Für das weitere Vorgehen ist es wichtig, dass diese Zellen wirklich leer sind. Dazu markieren Sie alle Spalten, Rechtsklick in eine der Überschriften und wählen Sie im Kontextmenü den Punkt Werte ersetzen… Im Dialog geben Sie bei Zu suchender Wert nichts ein (das Feld bleibt also leer), bei Ersetzen durch schreiben Sie in Kleinbuchstaben null. Nach einem OK stellt sich die Abfrage nun so dar:

Leere Zellen sind nun wirklich leer

Im nächsten Schritt werden Sie die Daten der Abfrage transponieren. Dazu wechseln Sie zum Register Transformieren und im Menüband, Gruppe Tabelle klicken Sie auf den Eintrag Vertauschen. Aus drei Spalten werden nun drei Zeilen und das stellt sich dann so dar:

Die transponierte Tabelle

Um die einzelnen Spalten in der gewünschten Reihenfolge untereinander zu platzieren werden Sie im nächsten Schritt eine Index-Spalte erzeugen. Wechseln Sie dazu zum Register Spalte hinzufügen und klicken der Gruppe Allgemein auf den Eintrag Indexspalte.

Die Spalte Index ist markiert. Führen Sie der Überschrift einen Rechtsklick durch und wählen Sie Andere Spalten entpivotieren. Gleich danach sieht ihre Abfrage so aus:

Die entpivotierte Tabelle/Abfrage

Es ist unschwer zu erkennen, dass die Spalte Wert dem Wunschergebnis entspricht. Löschen Sie also die ersten beiden Spalten auf beliebigem Wege (zum Beispiel markieren und Entf) und die Abfrage hat ihre endgültige Form. Nutzen Sie bei Bedarf die Gelegenheit, die Überschrift der verbliebenen Spalte Ihren Wünschen entsprechend anzupassen. Register Datei, Schließen & laden in… | Bestehendes Arbeitsblatt und klicken Sie im Tabellenblatt in jene Zelle, wo die Liste eingetragen werden soll. Sollte ein Ausnahmefällen einmal die Möglichkeit Bestehendes Arbeitsblatt ausgegraut, also nicht wählbar sein, dann belassen Sie es bei der Option Tabelle; in dem Fall wird diese einspaltige Tabelle in ein neues Arbeitsblatt geschrieben. Schneiden Sie dann die komplette Tabelle aus und fügen Sie diese an der gewünschten Position ein.

Selbstverständlich ist das ganze PQ-like dynamisch. 😎 Das bedeutet, dass bei Änderung der Quelldaten selbstverständlich alle Änderungen automatisch übernommen und entsprechend verarbeitet werden. Dazu reicht dann ein Klick auf Aktualisieren aus.

▲ nach oben …

Hinweis: Ich muss gestehen, dass ich mit der Anordnung der Überschriften in der einspaltigen Liste keinen Sinn sehe. Der Fragesteller deutete an, dass das für eine weitere Auswertung genau so sein müsse… 🙄 Nun ja, es kann ja sein, dass durch ein Tool nach Schlüsselbegriffen in dieser Spalte gesucht wird und diese dann entsprechend verarbeitet werden. Ich würde es für erheblich sinnvoller halten, eine 2-spaltige Liste zu erstellen, wo in der ersten Spalte die jeweilige Überschrift steht und in der zweiten der dazugehörige Begriff. Solch eine Tabelle dann noch per PivotTable auswerten, das kann dann richtig Spaß machen. 😎 Ein auf Einsteiger zugeschnittenes Muster können Sie hier herunterladen. Den Weg zum Ziel sollten Sie sich aber selber erarbeiten …

Und noch ein Hinweis (zum Hinweis hierüber): Wenn Sie aus dem Einsteiger-Niveau in Sachen PQ herausgewachsen sind, werden Sie auch den Weg finden, die PivotTable direkt zu erstellen, ohne den „Umweg“ des Speicherns als Tabelle in einem Arbeitsblatt.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 1,00  freuen …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Daten zusammenführen, Filtern & Sortieren, Foren-Q&A, Power Query, Spalten bearbeiten abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.