Zeile leer – löschen mit Power Query

Ganze Zeile(n) mit Power Query löschen, wenn in definierter Spalte diese Zelle leer ist

Power Query ist eine mächtige Erweiterung für Excel ab der Version 2010. Sie können dieses Excel Add-In bei Microsoft kostenlos herunterladen. Hier der aktuelle Link (Stand März 2016). Auf jener Seite finden Sie auch Anleitungen zur Installation. Sie rufen einfach unter Windows die *.msi (Installationsdatei) auf,  und anschließend haben Sie in Ihrer Menü-Leiste einen neuen Punk, Power Query.

Speziell für diesen Test haben wir Ihnen eine richtig „fette“ Tabelle erstellt. Eine Millionen Zeilen mit mehreren leeren Zellen in jeder der sechs Spalten. In diesem Projekt sollen als Beispiel alle Zeilen, wo in Spalte A eine leere Zelle ist, komplett gelöscht werden. Es kann aber auch jede andere Spalte oder ein beliebiger anderer Wert, Zahl oder Text gewählt werden. Das Prinzip ist identisch. Wichtig ist nur, dass nicht gefiltert werden soll sondern dass wirklich eine neue Tabelle erstellt wird, wo tatsächlich nur jene Zeilen enthalten sind, die (hier) in Spalte A einen beliebigen Wert enthalten. Und die Original-Daten sollen natürlich erhalten bleiben.

Öffnen Sie nun folgende Datei in Ihrem Excel. Zur Überprüfung, ob es auch wirklich eine Millionen Zeilen sind, einfach einmal StrgEnde. Alles klar, es stimmt. Sicher wissen Sie, dass Sie mit StrgPos1 wieder zu A1 kommen. Das wäre hier auch mehr als sinnvoll, um einen später beschriebenen Effekt kennen zu lernen. In jedem Fall ist es wichtig, dass die aktive, die ausgewählte Zelle also, im Datenbereich der oberen ca. 15.000 Zeilen liegt. Danach ein Klick auf den Menüpunkt Power Query (in der Abbildung durch uns rot markiert) und es zeigt sich ein komplett neues Menüband:

Menüband für das Power Query-Tool

Menüband für das Power Query-Tool

Zu Beginn geht es darum, die entsprechenden Daten dem Abfrage-Tool zur Verfügung zu stellen. Gleich im Anschluss an die erste Menü-Gruppe (Externe Daten abrufen) ist die Gruppe Excel-Daten mit der Schaltfläche Von Tabelle. Ein Klick darauf und da die aktive Zelle (beispielsweise A1) ja innerhalb des Datenbereichs ist, sucht sich PQ (Power Query) den passenden Bereich, markiert ihn und zeigt ihn in einem solchen Fenster an:

Automatisch markierten Bereich bestätigen

Automatisch markierten Bereich bestätigen

Und wie Sie immer vorgehen sollten: Überprüfen Sie, was Excel vorschlägt. Meistens ist es richtig. Meistens, aber nicht immer. Die Zeile 17.646 liegt ja „leicht“ unter 1 Millionen. Natürlich werden Sie die Aktion Abbrechen. Überprüfen Sie also einmal, warum Excel hier einen (oder vielleicht doch keinen) Fehler macht. OK, im zweiten Anlauf können Sie erst A1 markieren, dann StrgShiftEnde und nochmals auf die gleiche Schaltfläche. So sind alle Zellen des Datenbereichs markiert. Nun passt es, oder? Nein, es passt immer noch nicht. Excel widerspricht Ihnen und wählt wieder den gleichen Bereich wie vorher aus. Das mag etwas nervig sein, hat aber durchaus seinen tieferen Sinn. Korrigieren Sie also den Wert für die letzte Zeile von Hand auf 1000000 und dann OK? – Nein, immer noch nicht ganz. Es ist nämlich durchaus wahrscheinlich, dass auch bei Ihnen das Häkchen bei Meine Tabelle hat Überschriften. gesetzt ist. Und es ist ja offensichtlich, dass die Tabelle keine Überschriften hat. Also muss das korrigiert werden. Aber dann wirklich und endgültig OK.  🙂

Alles so wie gewünscht bzw. erforderlich? Nun ja, es ist durchaus denkbar, dass Excel diese recht große Datenmenge nicht komplett im Arbeitsspeicher halten kann. Dann werden Sie mit dieser Frage konfrontiert:

Sicherheitsabfrage, ob so weiter gemacht werden soll

Sicherheitsabfrage, ob so weiter gemacht werden soll

Da an den Original-Daten nichts geändert wird, dürfen Sie bedenkenlos auf OK klicken. Es wird ein neues Fenster geöffnet, ein komplett anderes Menü ist wieder einmal da und die Tabelle sieht auch etwas anders aus. Besonders fällt gewiss auf, dass bei den Leerfeldern der Begriff null drin steht. Und es wurde automatisch eine Zeile mit Überschriften eingefügt. Das ist auch unabdingbar, denn solch eine Tabelle bzw. Liste braucht in jedem Falle Überschriften:

Daten-Vorschau nach dem Import

Daten-Vorschau nach dem Import

Dieses ist aber nur eine Vorschau-Tabelle. Lassen Sie sich bitte nicht dadurch irritieren, dass nur ein kleiner Teil der Daten angezeigt werden kann. Wenn Sie einmal ganz nach unten scrollen, werden Sie einen entsprechenden Hinweis bekommen.

Spalte A soll ja der Vorgabe entsprechend erst einmal gefiltert werden, darum klicken Sie in der Überschriftzeile im Feld Spalte 1 auf  die Schaltfläche :

Filter zum ausblenden von Werten einer Spalte

Filter zum ausblenden von Werten einer Spalte

Die eventuell erscheinende Anmerkung direkt über den beiden unteren Buttons, dass die Liste möglicherweise unvollständig sei, ist seitens des Inhalts ja bekannt. Ignorieren Sie diese einfach. Was Sie aber jetzt irritieren könnte, das ist der nächste Schritt. Scheinbar genau umgekehrt von bisherigen Erfahrungen werden Sie nämlich folgendes tun: Sie nehmen das Häkchen bei (NULL) heraus, selbst wenn es vielleicht unlogisch erscheint. 

Bedenken Sie jedoch: Hier wird festgelegt, was alles angezeigt und auch im Anschluss in ein Tabellenblatt überführt werden soll. Darum die Null-Werte entfernen. Anschließend OK und zum Abschluss ein Klick auf die allererste Menü-Schaltfläche Schließen und laden. Nach (mehr oder weniger) kurzer Wartezeit wird sich vielleicht wieder das Warn-Fenster zeigen, auch hier machen Sie einfach weiter. Nochmals einen Moment warten und schließlich zeigt sich ein neues Tabellenblatt-Fenster; der Register-Name (wahrscheinlich ist es Tabelle1) wird eigenständig vergeben. Sie erkennen eine Liste mit einer grünen Tabellierung (die Einfärbung jeder 2. Zeile) und rechts einen Regie- und Informationsbereich:

Rechts der Regie- und Informationsbereich

Rechts der Regie- und Informationsbereich

Das Ergebnis der Abfrage sind also 900.058 Zeilen. Sie können es auch gerne prüfen, ob das stimmt.  😎 Dabei handelt es sich keineswegs um eine Filterung, die Werte stehen da tatsächlich ohne Lücken in den einzelnen Zeilen. Oder anders ausgedrückt: In diese neu erstellte Tabelle wurden nur die gefilterten, die sichtbaren Zeilen aus der Ursprungstabelle übernommen:

Die endgültige Tabelle

Die endgültige Tabelle

Mit diesen Werten, mit dieser Tabelle können Sie prinzipiell wie gewohnt arbeiten. Es ist erst einmal eine ganz normale Intelligente Tabelle mit all ihren Möglichkeiten. Zusätzlich haben Sie noch einige weitere Angebote, welche sich speziell auf das PQ-Modell beziehen. Es ist möglich, die Tabelle so wie sie ist zu kopieren und beispielsweise in einem anderen Arbeitsblatt als Wert einzufügen. Dann ist sie gegen versehentliche Veränderung geschützt. Nachteil: Diese kopierte Tabelle ist dann natürlich auch nicht dynamisch, was ja durchaus gewünscht sein kann. Die gerade eben automatisch erstellte Tabelle ist jedoch dynamisch und Sie können alle Vorteile des Power Query nutzen. Die Diskussion darüber, welche Form angebracht ist, würde allerdings hier entschieden zu weit führen, denn das gesetzte Ziel ist erreicht.


Eine Erklärung sind wir Ihnen vielleicht doch noch schuldig. Warum werden zu Beginn nur die ersten 17.646 Zeilen markiert? Nun ja, wenn Sie sich die eine Zeile darunter einmal ansehen so werden Sie feststellen, dass diese komplett leer ist. Und da durch den Klick auf die Schaltfläche Aus Tabelle eine Intelligente Tabelle erzeugt werden soll, wird Excel automatisch den gesamten zusammenhängenden Bereich nutzen. Und durch diese Leerzeile wird genau der an der Stelle unterbrochen, begrenzt. Alternative: In eine nicht auszuwertende Spalte dieser Zeile ein Leerzeichen schreiben, dann erkennt Excel, dass diese Zeile nicht komplett leer ist und wird gleich den ganzen Bereich markieren bzw. vorschlagen.

▲ nach oben …

Dieser Beitrag wurde unter Daten-Import / -Export, Musterlösungen, Ohne Makro/VBA, Power Query, Suchen und finden, Tabelle und Zelle abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.