Xtract: In eine gegebene Liste/Tabelle/Aufstellung sollen nach jeder ursprünglichen Zeile 3 Leerzeilen eingefügt werden. Das soll ausschließlich per Power Query erfolgen.
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Gegeben ist eine Liste mit Texten (hier: Obstsorten), welche in einem neuen Tabellenblatt mit jeweils 3 anzufügenden Leerzeilen gespeichert werden soll. Sie können die hier als Screenshot gezeigte kleine Liste selber in ein leeres Arbeitsblatt schreiben (wie in der Abildung gezeigt) oder als Textdatei (Obstsorten.txt) hier von unserem Server herunterladen. Diese Aufgabenstellung habe ich übrigens in einem Excel-Forum gelesen und nach meinem Dafürhalten etwas modifiziert. 😎
Die in ein Arbeitsblatt eingetragenen Werte
Zugegeben, es gibt auch andere, vielleicht „professionellere“ Wege, innerhalb Power Query das gewünschte Ziel zu erreichen. Und natürlich gelangen Sie auch per VBA bzw. mit einer Formel-Lösung zum Ziel. Aber hier geht es nun einmal um Power Query und mein Anliegen ist es, auch für Nicht-Profis einen möglichst gut nachvollziehbaren Weg anzubieten.
Der derzeitige Stand ist, dass Sie die kleine Liste mit den Obstsorten wie in der Abbildung gezeigt in ein Arbeitsblatt eingetragen oder die Textdatei entsprechend in einem Editor komplett kopiert und dann in ein Tabellenblatt eingefügt haben. Alternativ können Sie natürlich bei entsprechendem Wissenstand die Textdatei direkt in Power Query importieren. In dem Fall entfällt der erste Schritt des folgenden Absatzes (der Import in PQ).
Die Daten liegen als Text vor und beginnen beispielsweise in Zelle A1 und enden hier in A12. Und Sie erkennen auch, dass die erste Zeile (A1) eine Überschrift sein soll und nicht zu den eigentlichen Daten gehört. Markieren Sie irgendeine Zelle im Bereich A1:A12 und importieren Sie über Daten | Aus Tabelle/Bereich die Liste. Achten Sie darauf, dass Sie das Häkchen bei Tabelle hat Überschriften gesetzt ist:
Import in den PQ-Editor
Nach einem OK hat sich der Power Query-Editor geöffnet und die Daten sind hierin importiert worden. Die oberste Zeile der tabellarischen Ansicht (ohne die Nummerierung links der Daten) muss die Überschrift Obstsorten enthalten. Falls das nicht der Fall ist, Klicken Sie im Menüband auf Erste Zeile als Überschriften verwenden. Damit ist der Import der Daten komplett erledigt Und Sie können sich den weiteren Schritten widmen.
Ich selber lege Wert darauf, auf die Original-Daten jederzeit zugreifen zu können. So bin ich vor unangenehmen Überraschungen weitgehend geschützt. Bei Bedarf kann (und werde) ich diese zu Beginn erstellte Abfrage am Schluss löschen. Sofern das linke Seitenfenster mit den Abfragen noch nicht sichtbar ist, blende ich es über diesen Weg ein. Rechtsklick auf den (noch) einzigen Eintrag Tabelle1 und wählen Sie dann den Eintrag Duplizieren. Alternativ geht das auch über Start | Verwalten | Duplizieren. PQ hat für Sie nun automatisch die Abfrage Tabelle1 (2) erstellt und dieses ist nun auch ihre Arbeitskopie. Um dieser Abfrage einen „sprechenden“ Namen zu geben, markiere ich diesen Eintrag, F2 und benenne diese Query zu Obstsorten um.
Register Spalte hinzufügen | Indexspalte ▼ erweitern und im Dropdown anschließend Benutzerdefiniert… wählen. Tragen Sie bei Startindex die Zahl 0 und bei Schrittweite die Zahl 4 ein. In der neuen Spalte Index hat Power Query nun die Ziffern 0, 4, 8, … eingetragen. Sie erkennen, dass jeweils 3 Zahlen in der fortlaufenden Reihenfolge fehlen. Und genau dieses sollen die jeweils 3 geforderten Leerzeilen werden.
Um das zu erreichen, müssen Sie sich eine neue, leere Abfrage erstellen. Wechseln Sie dazu wieder zum Register Start | Neue Quelle | Andere Quellen | Leere Abfrage. Im linken Seitenfenster erkennen Sie den neuen Eintrag Abfrage1, welche als Symbol links des Query-Namens ABC hat. Der Cursor blinkt in der langen, einzeiligen leeren Eingabezeile. Geben Sie dort diese Formel, beginnend mit dem Gleichheitszeichen ein: = {0..43}. Die Klammern sind übrigens geschweifte Klammern, nicht die runden. PQ wird sofort eine Liste / Tabelle mit den Ziffern 0 bis 43 generieren, als Überschrift steht dort Liste. Und das Symbol dieser Query hat sich auch verändert, es ist nun so eine Art einspaltige Tabelle.
Ich bin Ihnen gewiss eine Erklärung schuldig, wieso, warum, weshalb, … In der Eingabe-bzw. Befehlszeile wird durch die geschweiften Klammern eine Liste erzeugt. Der numerische Startwert ist 0, die beiden Punkte stehen für die Anweisung, dass eine logische Reihenfolge in einer Schrittweite 1 bis zum Endwert (hier: 43) generiert werden soll. Es bleibt vielleicht die Frage, warum der Endwert 43 ist. Wenn Sie in der Query Obstsorten nachsehen werden Sie erkennen, dass der Index der untersten Zeile 40 ist. Und da dort nach dieser Zelle noch 3 Leerzeilen eingefügt werden sollen, ergibt das als höchste Zeilenzahl 43. Bei diesen wenigen Einträgen ist das ja noch einigermaßen übersichtlich und sie können rasch nachschauen, welches der höchste Index war. Weiter unten zeige ich Ihnen einen Weg auf, wie Power Query eigenständig den Ziel-Wert berechnet und auch verwerten kann.
Solch eine PQ-Liste ist eine Sonderform einer Abfrage. Für die weitere Verarbeitung brauchen Sie eine „normale“ Query; dazu Klicken Sie in dieser wenige Elemente enthaltenden Symbolleiste ganz links auf das Ribbon Zu Tabelle. Den Dialog können Sie direkt mit OK bestätigen und dadurch schließen. Im linken Seitenfenster erkennen Sie nun, dass dieser letzte Eintrag umbenannt worden ist zu Abfrage1 und das Symbol links des Namens einer Tabelle entspricht. Über Start | Abfragen zusammenführen öffnet sich das Dialogfenster Zusammenführen. Klicken Sie hier in die einzeilige Textzeile und wählen Sie im Dialog den Eintrag Obstsorten. Umgehend wird sich im unteren der beiden größeren Datenbereiche der obere Teil der Query Obstsorten einblenden:
So führen Sie die beiden Abfragen zusammen (erste Schritte)
Klicken Sie hier im oberen Kasten irgendwo in die Spalte Column1 und im unteren Kasten irgendwo in die Spalte Index. Diese beiden Spalten sind nun markiert. Die Auswahl Join-Art kann so bleiben, also ein Klick auf OK. PQ hat in dieser Abfrage eine neue Column mit dem Namen Obstsorten angelegt und jeder einzelne Zeile enthält den Eintrag Table. Erweitern Sie diese Spalte durch einen Klick in den Doppelpfeil 
der Überschrift und sorgen Sie dafür, dass ausschließlich bei Obstsorten das Häkchen bestehen bleibt:
Nur 1 Kästchen bleibt markiert
Die Überschrift dieser Spalte ist jetzt Obstsorten.1 und Sie erkennen, dass nun ausser den einzelnen Obstsorten auch eine ganze Menge Leerzeilen enthalten sind. Nur die angestrebte Lösung mit 1 Zeile Text und dann 3 Zeilen leer stimmt noch nicht. 🙁 Die Ursache werden Sie aber rasch erkennen, wenn Sie sich erinnern, was eigentlich in Column1 steht; es ist der Index gewesen. Und der sollte doch… 😉
Sortieren Sie die Spalte Column1 aufsteigend und siehe da, das Wunschergebnis ist erreicht. Nun können Sie diese erste Spalte auch entfernen, sie hat ihren Dienst getan. Schließen & laden oder Schließen & laden in… und die Aufgabe ist erfüllt.
Ich hatte Ihnen weiter oben versprochen, dass es auch eine Lösung für fortgeschrittenere oder interessierte Anwender gibt. Okay, „here we go…” – Wenn Sie schon etwas erfahrener in PQ sind oder tiefer in die Materie einsteigen wollen, dann können Sie die Berechnung des numerischen Werts der letzten Zeile auch durch Power Query vornehmen lassen. Die ersten Schritte sind genau so wie gehabt. Sie sind also in der duplizierten Tabelle und haben die Indexspalte mit der 4er-Schrittweite eingefügt. Ein Klick auf die Überschrift Index und sie wählen im Kontextmenü wie gehabt den untersten Punkt der Auflistung: Als neue Abfrage hinzufügen.
Wie auch schon im vorherigen Kapitel hat Power Query eine neue Abfrage, eine Liste mit dem Namen Index erstellt. Ab hier ist die Vorgehensweise anders. Klicken Sie in dem Menüband auf Statistiken und im Dropdown wählen Sie Maximum. Sie erkennen, dass sich das Symbol im linken Seitenfenster geändert hat. Und Sie sehen im Hauptteil des Fensters keine Liste mehr sondern nur das Ergebnis dieser Funktion, nämlich 40. Die Liste enthielt (und enthält auch noch) 40 Elemente bzw. Zeilen.
Sie können sich nun entscheiden, ob sie an dieser Stelle oder später in der Auswertung noch die 3 (Leerzeilen) hinzufügen wollen. Ich entscheide mich meist dafür, dass so früh wie möglich abzuarbeiten. Also klicke ich direkt in die Eingabezeile mit der Formel und ändere sie so, dass der Wert 3 addiert wird: = List.Max(Index1)+3. Wenn Sie anschließend irgendwo in den Ergebnis-Bereich Klicken werden Sie sofort die korrigierte Berechnung (43) erkennen. Der Deutlichkeit und besseren Verständlichkeit wegen ändere ich nun noch den Namen der Abfrage/Liste von Index auf MaxWert.
Ich gehe jetzt und in diesem Fall von der Annahme aus, dass Sie noch keine leere Abfrage mit der Zahlenfolge 0 bis 43 erstellt haben. Darum wie bereits beschrieben der Weg über Start | Neue Quelle | Andere Quellen | Leere Abfrage. In die Eingabezeile für die Funktion geben Sie nun folgende Formel ein: = {0..MaxWert} und nach einem Klick in den Freiraum oder auf das Häkchen-Symbol links des Eingabefeldes wird ruckzuck die Liste von 0 bis zum berechneten Endwert (der ja in der Liste MaxWert errechnet worden ist) erstellt.
Der riesige Vorteil dieser Vorgehensweise ist, dass diese Liste dynamisch ist, sprich sie wird automatisch auf die Anzahl der Einträge in der Ursprungstabelle angepasst. Die einzige Änderung, welche Sie eventuell vornehmen müssen ist, die Anzahl der einzufügenden Leerzeilen anzupassen. Und selbst die könnten Sie im Excel-Arbeitsblatt in einer Zelle hinterlegen und durch PQ auswerten lassen. Und dass diese Vorgehensweise viel angenehmer ist, wenn es hunderte von Einträgen in der Quelldatei sind, das versteht sich von alleine. 😎 Diese Liste werden Sie anschließend auf dem bekannten Weg über Zu Tabelle entsprechend umwandeln und anschließend als Basis für das Zusammenführen verwenden.
Dieser Vorgang ist dann wieder genau wie weiter oben beschriebenen. Es bleibt eigentlich nur noch der Schritt, die ursprüngliche, erste erstellte Query (Tabelle1) zu löschen. In diesem Fall haben Sie diese nicht gebraucht, aber wenn es nicht hunderttausende von Datensätzen sind, ist das auch keine Bremse. Wenn Sie im Umgang mit Power Query sicherer sind werden Sie auch auf das Duplikat am Beginn einer Session verzichten und dieses bei Bedarf aus der existierenden Abfrage erstellen.
BTW: Merry Christmas! 💡 💡 💡 (released @ 12/25/2021)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen … (← Klick mich!)