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

In einem Forum wurde die Frage aufge­wor­fen, wie eine Kreuzta­belle mit vie­len Spal­ten so zu ein­er einspalti­gen Liste umfor­matiert wer­den kann, dass die Über­schriften erhal­ten bleiben und auch die Rei­hen­folge der Ein­träge nicht verän­dert wird. Zum Zeit­punkt der Erstel­lung dieses Beitrages (19.10.2019) lag eine Formel-Lösung vor und von einem weit­eren Helfer der Hin­weis auf Pow­er Query. Ich habe die Gele­gen­heit genutzt, in PQ eine mögliche Lösung zu erstellen.

Laden Sie zu Beginn diese Datei aus unserem Blog-Serv­er herunter. Es ist prak­tisch ein Nach­bau der als HTML-Tabelle vorgestell­ten Dat­en im Forum. Klick­en Sie in den Daten­bere­ich A1:C9 und erstellen Sie auf beliebigem Wege (beispiel­sweise StrgT oder StrgL eine „Intel­li­gente” Tabelle. Acht­en Sie dabei darauf, dass in diesem Fall das Häkchen bei Tabelle hat Über­schriften nicht geset­zt ist (nor­maler­weise auch die Vor­gabe). Die erste Zeile, welche ja die eigentlichen Über­schriften darstellt, soll ja nach der Umwand­lung auch jew­eils in die 1‑spaltige Liste inte­gri­ert und nicht als Über­schrift gew­ertet wer­den. Importieren Sie nun diese eben erstellte Tabelle in Pow­er Query. Direkt nach dem Import stellt sich das im Edi­tor so dar:

Direkt nach dem Import

Auf den ersten Blick sieht das alles sehr „vernün­ftig” aus. Wenn Sie schon etwas Erfahrung mit Pow­er Query gemacht haben wer­den Sie aber wis­sen, dass wirk­lich leere Zeilen im Edi­tor nicht leer dargestellt wer­den (wie hier teil­weise in den Zeilen 4–8) son­dern den Wert null enthal­ten. Hier ist das schein­bar unge­wohnte Ver­hal­ten darin begrün­det, dass in den Orig­i­nal-Dat­en die schein­bar leeren Zellen die Formel ="" enthal­ten ist, was typ­is­cher­weise in der Fach­sprache als Leer­String beze­ich­net wird. Die Zelle ist also nicht leer son­dern enthält eine Zeichen­folge, einen Text der Länge null. 💡 

Für das weit­ere Vorge­hen ist es wichtig, dass diese Zellen wirk­lich leer sind. Dazu markieren Sie alle Spal­ten, Recht­sklick in eine der Über­schriften und wählen Sie im Kon­textmenü den Punkt Werte erset­zen… Im Dia­log geben Sie bei Zu suchen­der Wert nichts ein (das Feld bleibt also leer), bei Erset­zen durch schreiben Sie in Klein­buch­staben null. Nach einem OK stellt sich die Abfrage nun so dar:

Leere Zellen sind nun wirk­lich leer

Im näch­sten Schritt wer­den Sie die Dat­en der Abfrage transponieren. Dazu wech­seln Sie zum Reg­is­ter Trans­formieren und im Menüband, Gruppe Tabelle Klick­en Sie auf den Ein­trag Ver­tauschen. Aus drei Spal­ten wer­den nun drei Zeilen und das stellt sich dann so dar:

Die transponierte Tabelle

Um die einzel­nen Spal­ten in der gewün­scht­en Rei­hen­folge untere­inan­der zu platzieren wer­den Sie im näch­sten Schritt eine Index-Spalte erzeu­gen. Wech­seln Sie dazu zum Reg­is­ter Spalte hinzufü­gen und Klick­en der Gruppe All­ge­mein auf den Ein­trag Indexs­palte.

Die Spalte Index ist markiert. Führen Sie der Über­schrift einen Recht­sklick durch und wählen Sie Andere Spal­ten ent­piv­otieren. Gle­ich danach sieht ihre Abfrage so aus:

Die ent­piv­otierte Tabelle/Abfrage

Es ist unschw­er zu erken­nen, dass die Spalte Wert dem Wun­schergeb­nis entspricht. Löschen Sie also die ersten bei­den Spal­ten auf beliebigem Wege (zum Beispiel markieren und Entf) und die Abfrage hat ihre endgültige Form. Nutzen Sie bei Bedarf die Gele­gen­heit, die Über­schrift der verbliebe­nen Spalte Ihren Wün­schen entsprechend anzu­passen. Reg­is­ter Datei, Schließen & laden in… | Beste­hen­des Arbeits­blatt und Klick­en Sie im Tabel­len­blatt in jene Zelle, wo die Liste einge­tra­gen wer­den soll. Sollte ein Aus­nah­me­fällen ein­mal die Möglichkeit Beste­hen­des Arbeits­blatt aus­ge­graut, also nicht wählbar sein, dann belassen Sie es bei der Option Tabelle; in dem Fall wird diese einspaltige Tabelle in ein neues Arbeits­blatt geschrieben. Schnei­den Sie dann die kom­plette Tabelle aus und fügen Sie diese an der gewün­scht­en Posi­tion ein.

Selb­stver­ständlich ist das ganze PQ-like dynamisch. 😎 Das bedeutet, dass bei Änderung der Quell­dat­en selb­stver­ständlich alle Änderun­gen automa­tisch über­nom­men und entsprechend ver­ar­beit­et wer­den. Dazu reicht dann ein Klick auf Aktu­al­isieren aus.

▲ nach oben …

Hin­weis: Ich muss geste­hen, dass ich mit der Anord­nung der Über­schriften in der einspalti­gen Liste keinen Sinn sehe. Der Fragesteller deutete an, dass das für eine weit­ere Auswer­tung genau so sein müsse… 🙄 Nun ja, es kann ja sein, dass durch ein Tool nach Schlüs­sel­be­grif­f­en in dieser Spalte gesucht wird und diese dann entsprechend ver­ar­beit­et wer­den. Ich würde es für erhe­blich sin­nvoller hal­ten, eine 2‑spaltige Liste zu erstellen, wo in der ersten Spalte die jew­eilige Über­schrift ste­ht und in der zweit­en der dazuge­hörige Begriff. Solch eine Tabelle dann noch per Piv­ot­Table auswerten, das kann dann richtig Spaß machen. 😎 Ein auf Ein­steiger zugeschnittenes Muster kön­nen Sie hier herun­ter­laden. Den Weg zum Ziel soll­ten Sie sich aber sel­ber erar­beit­en …

Und noch ein Hin­weis (zum Hin­weis hierüber): Wenn Sie aus dem Ein­steiger-Niveau in Sachen PQ her­aus­gewach­sen sind, wer­den Sie auch den Weg find­en, die Piv­ot­Table direkt zu erstellen, ohne den „Umweg” des Spe­ich­erns als Tabelle in einem Arbeits­blatt.

Nach­trag: Zwei ähn­liche The­men, mehrere Spal­ten zu 1 zusam­men­z­u­fassen find­en Sie eben­falls hier und hier in unserem Blog. Bei­de Beiträge sind Ein­steiger-gerecht.

▲ nach oben …