Xtract: In eine existierende Kreuztabelle per Power Query nach jeder existierenden Spalte eine definierte Anzahl von komplett leeren Spalten einfügen (warum auch immer).
Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query
Erstellt mit Excel 2019, in anderen Versionen können Abweichungen auftreten
PQ: Leerzeilen (Leerspalten) einfügen
In einem Excel-Forum (Herber.de) wurde eine Anfrage gestellt, die zwar schon durch Formeln/Funktionen gelöst worden ist mich aber reizte, sie durch Power Query zu lösen. Ich zitiere hier den Text der Anfrage und stelle auch die (Original-) Muster-xlsx auf unserem Server für Ihren Download zur Verfügung:
Hallo,
wie kann ich Daten von Spalte A und B in die Zeile 1 und 2 Transponieren und Zwischenspalten einfügen?
Mit der M Trans Formel kann ich zwar die Daten transponieren, aber keine leere Zwischenspalten einfügen.
Ich hab eine Liste Spalte A mit vielen hunderten Einträgen bei denen immer wieder welche dazukommen und ich diese in der Zeile 1 und 2 haben sollte, allerdings mit Zwischenspalten.
„Eigentlich ganz einfach” dachte ich zu Beginn; obwohl ich konstatieren kann: Den eigentlichen Sinn des Vorhabens habe ich bis heute nicht klar erkannt, denn das genannte Ziel widerspricht aus meiner Sicht einfach den Regeln einer ordentlichen Datenhaltung.
Wie auch immer, recht schnell merkte ich, dass das gewünschte Ergebnis doch nicht so ganz einfach mit Power Query zu generieren ist. Zusammengefasst und auch ergänzend noch einmal folgende Informationen: Eine zweispaltige Liste variabler Länge soll transponiert werden (also die Spalten zu Zeilen) und es soll eine definierte Anzahl von (leeren) Spalten in der anschließend zweizeiligen Tabelle nach jeder ursprünglichen Zeile eingefügt sein. In der Muster-Datei des Fragestellers ist das sehr gut erkennbar.
Erste Schritte
Die Ausgangslage (hier in meiner Datei):
Die Beispiel-Ausgangslage
Der Bereich A2:B9 ist bereits als Tabelle formatiert und kann darum ohne weitere Vorarbeit in den Power Query-Editor importiert werden. Im ersten Schritt ist es für mich (und für Sie) wichtig, die Anzahl der Datenzeilen (der Datensätze) festzustellen. Natürlich reicht ein Blick in den Editor, denn neben der untersten Zeile ist ja auch die Zeilennummer vermerkt. Da diese Tabelle/Liste aber dynamisch gestaltet werden soll, muss dieser numerische Wert in irgend einer Form festgehalten, gespeichert werden. Um das zu erreichen gehen Sie so vor:
- Falls Sie das linke Seitenfenster mit den Abfragen nicht sehen, blenden Sie es ein.¿
- Rechtsklick auf den einzigen Eintrag Tabelle1 und wählen Sie im Kontextmenü Verweis.
- Geben Sie dieser neuen Abfrage den Namen AnzZeilen.
- Markieren Sie die Spalte Daten, Rechtsklick in die Überschrift und im Kontextmenü ein Klick auf Drilldown ausführen.
- Im rechten Seitenfenster bei Angewendete Schritte wurde der Schritt Daten1 (bzw. in Version 2016 Navigation)eingefügt, die Abfrage besteht nur noch aus einer Spalte mit der Überschrift Liste.
- Markieren Sie diese Spalte durch einen Klick in die Überschrift, Symbol Statistiken | Werte zählen.
- Speichern Sie diese Abfrage jetzt über Schließen & laden in… als Nur Verbindung, damit diese Liste später nicht in einem eigenen Arbeitsblatt gespeichert sondern nur im Arbeitsspeicher gehalten wird.
- Öffnen Sie wieder den Power Query-Editor und wechseln Sie erforderlichenfalls zu Abfrage Tabelle1.
Zwischenergebnis als Liste/Tabelle
Damit sind die Vorarbeiten prinzipiell erledigt. Im nächsten Schritt werden Sie eine ganz neue Tabelle erstellen welche im Endeffekt so viele leere Zelle enthält, wie später leere Spalten erzeugt werden sollen. Also bei den 7 Datensätzen der ursprünglichen Abfrage jeweils 2, also gesamt 14 Datensätze. Dazu gehen Sie über Menü Start, Gruppe Neue Abfrage | Neue Quelle | Andere Quellen | Leere Abfrage. Im linken Seitenfenster wird ein Eintrag Abfrage1 erstellt und das Hauptfenster, wo typischerweise die Daten der Abfrage sichtbar sind, ist absolut leer.
Oberhalb dieser großen grauen Fläche ist nur das leere Textfeld für die Formeln zu sehen. Geben Sie hier in die Eingabezeile folgende Formel einschließlich des führenden Gleichheitszeichens ein: = {1..AnzZeilen * 2}. Hinweis: Da es auf dem Bildschirm nicht immer optimal erkennbar ist, es handelt sich bei dieser Formel um die geschweiften Klammern, welche Sie über AltGr7 und AltGr0 einfügen können. Umgehend wird eine Liste mit den Zahlen 1 bis 14 erstellt.
Sie fragen sich nun vielleicht, wieso, warum, weshalb … Grundsätzlich soll eine Liste erzeugt werden, wo bezogen auf die Anzahl der existierenden Datensätze (in diesem Fall 7) jeweils 2 leere Zellen/Zeilen erzeugt werden. Hätten Sie sich die Anzahl der Datensätze nur gemerkt und nicht in einer Abfrage gespeichert dann würde sich die Formel so darstellen: = {1..7*2}. Power Query erkennt alleine, dass das zweite Argument die Multiplikation 7*2 ist und führt die Berechnung durch, bevor der Teil der Formel nach den beiden Punkten (..) ausgeführt wird. Und der Wert 7 wird bei der eigentlichen Formel aus der gespeicherten Liste AnzZeilen geholt.
Da Sie (in diesem Fall) 14 leere Zellen haben wollen, Klicken Sie im Menüband auf Zu Tabelle und im nächsten Schritt sollten Sie auch gleich den Namen dieser Abfrage auf Leerzeilen ändern. Dass in der einzigen Spalte die numerischen Werte von 1 bis 14 stehen, ist richtig und erwünscht. Fügen Sie nun über Spalte hinzufügen | Benutzerdefinierte Spalte eine neue Spalte ein und als Überschrift verwenden Sie Daten. In das Feld für Benutzerdefinierte Spaltenformel tragen Sie nach dem bereits existierenden = nur in Kleinschrift den Wert null ein. OK und anschließend entfernen Sie die erste Spalte Column1, Sie brauchen ja nur die 14 Zeilen mit dem Wert null (also leer).
Durch Transponieren zum Ziel
Wechseln Sie nun wieder zur Abfrage Tabelle1. Menü Start, Gruppe Kombinieren | Abfragen anfügen und wählen Sie bei Anzufügende Tabelle die Abfrage Leerzeilen. Danach stellt sich die ursprüngliche Abfrage so dar:
Die gewünschte Zahl von Leerzeilen wurde angefügt
Im folgenden Schritt werden Sie dafür sorgen, dass je zwei von diesen Leerzeilen nach jeder der (noch) 7 ersten Zeilen der ehemaligen Überschriften eingefügt bzw. verschoben werden. Im ersten Schritt gehen Sie über Spalten hinzufügen | Indexspalte einen Index, welcher mit 0 beginnt. Weiterhin im Menü Spalte hinzufügen markieren Sie nun die Spalte Index und in der Gruppe Aus Zahl wählen Sie Standard | Modulo. In das Feld Wert geben Sie erst einmal den Wert 2 ein; das ist nur ein Platzhalter, damit Power Query nach dem Abschluss eine funktionierende Formel und ein darauf basierendes Ergebnis erstellen kann. Das Ergebnis:
Das Ergebnis, der Dummy-Wert 2 ist von mir gelb gemarkert worden
Sie sehen, dass eine Art Gruppierung in Zweiergruppen erstellt worden ist. Es bieten sich nun zwei unterschiedliche Wege an, die gleichermaßen zum Ziel der dynamischen Gruppengröße führen. Entweder Sie ändern direkt in der Eingabezeile den (von mir gelb markierten) Wert 2 zu AnzZeilen und Klicken dann irgendwo in den grauen Bereich darunter. Oder Sie führen alternativ im rechten Seitenfenster einen Doppelklick auf die zuletzt erstellte Zeile Rest eingefügt durch, löschen im Editor die 2 und schreiben stattdessen den Namen der Liste AnzZeilen an die Stelle. Nach einem OK werden Sie erkennen, dass in der Spalte Modulo Gruppen in jener Größe gebildet worden sind, die vorher in der Abfrage/Liste AnzZeilen berechnet worden ist.
Um die Leerzeilen an die gewünschten Stellen zu positionieren, sortieren Sie zu Beginn die Spalte Modulo aufsteigend. Anschließend sortieren Sie nun auch noch die Spalte Index aufsteigend und das Ergebnis passt. Entfernen Sie nun auf beliebige Weise die beiden letzten Spalten, sodass nur Daten und Zahlen erhalten bleiben. Wechseln Sie nun zum Menü Transformieren und Klicken in der Gruppe Tabelle auf den Eintrag Vertauschen. Dieser Vorgang entspricht dem, was Sie in Excel unter Transponieren kennen.
Schließen & laden und Sie werden vermutlich sehen, dass Sie nichts sehen. 😉 Der Abfrage-Editor ist zwar geschlossen und Sie befinden sich in der Excel-Arbeitsmappe aber die eben generierte Kreuztabelle mit den entsprechenden Leerspalten ist weder im aktuellen Blatt noch in einem gesondert erzeugten Sheet zu sehen. Das liegt daran, dass Sie vorher die Abfrage AnzZeilen im Modus Schließen & laden in… | Als Verbindung gesichert haben und Power Query die anderen Abfragen automatisch auch in diesem Modus geschaltet hat. Falls Ihnen nicht gegenwärtig ist, wie Sie diese eine Tabelle1 sichtbar an gewünschter Position platzieren können, sehen Sie einfach einmal hier in unserem Blog nach.
Anmerkung: Genauso „eigenwillig” und aus meiner Sicht auch regelwidrig wie die komplett leeren Spalten in einer Kreuztabelle ist der Wunsch des Fragestellers, das Ergebnis (auch noch) ohne Überschriften darzustellen. Die eine Möglichkeit ist dann natürlich, die komplette Zeile auszublenden. Das wäre aber nicht so gut, wenn irgendwo in diesem Arbeitsblatt links oder rechts in dieser Zeile noch Werte stehen, die sichtbar bleiben sollen. Die zweite Möglichkeit sehe ich darin, dass Sie in der Excel-Tabelle die Überschriften alle mit dem Benutzerdefinierten Zahlenformat ;;; versehen und damit unsichtbar und auch nicht druckbar machen; dann noch das Tabellenformat so anpassen, dass weder Überschriften noch die Filter-Schaltflächen sichtbar sind. Da wäre dann aber händische Nacharbeit erforderlich, wenn sich die Anzahl der Spalten der Kreuztabelle erhöht. Die sauberste und damit beste Möglichkeit ist natürlich, noch einmal in den Power Query-Editor zu wechseln und dort die Überschriften mit einem jeweils einmaligen und sinnvollen Text zu gestalten.
Fertig. – Es bleibt bei mir das Unbehagen, dass ich dort eine Aufgabe gelöst habe, die ich an sich für nicht nachvollziehbar oder gar unsinnig erachte. Ich sehe erst einmal keinen Sinn darin, ein „Werk” zu erstellen, was gegen mehrere Regeln einer ordentlichen Datenhaltung verstößt. Aber es hatte mich einfach gereizt, solch eine Aufgabenstellung mit Power Query zu lösen. Und als Idee im Hinterkopf und eventuelle Prognose hatte ich, dass die leeren Spalten im Laufe des Lebenszyklus der Tabelle vielleicht nach und nach mit Daten gefüllt und natürlich dann auch entsprechende Überschriften in der Titelzeile stehen werden.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen … (← Klick mich!)