Xtract: In Power Query ist das Sortieren von Spalten so ähnlich wie bei einer Intelligenten Excel-Tabelle. Einige Besonderheiten erleichtern gewiss das Leben vieler Anwender… Teil 2: Leere Zellen ans Ende sortieren.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Sonderfall leere Zellen
Wenn in einer Spalte leere Zellen enthalten sind stellt sich immer wieder die Frage, wie die beim sortieren behandelt werden sollen. Für diese und die nachfolgende Übung laden Sie bitte dieses File herunter. In ersten Arbeitsbaltt sind in Spalten A:B und Spalten D:E unterschiedliche Tiere und deren Halter angegeben. Da manche Halter gerade kein Tier haben, ist dort die Zelle leer (oder sieht leer aus).
Die beiden Tabellen scheinen sich auf den ersten Blick nicht zu unterscheiden, dazu aber gleich mehr… Importieren Sie erst einmal die erste (die linke) Tabelle (Tabelle1) in den Power Query-Editor:
Fällt Ihnen etwas auf?
Es ist offensichtlich, dass Spalte_B bereits ohne Ihr Zutun aufsteigend sortiert ist. Und in Spalte_A sind einige Zellen leer, was ja auch hierüber schon beschrieben ist. Ihr Wunsch sei es nun, dass Spalte_A aufsteigend sortiert wird. Und da in dieser Abfrage ja noch keine aktive Sortierung vorliegt, genügt ein Klick in das Erweitern-Symbol dieser Spalte und dann Aufsteigend sortieren. – Aha…
Die leeren zuerst, na ja…
Prinzipiell und aus der Sicht der IT ist das auch korrekt so. Ein oft geäußerter Wunsch ist aber, dass die leeren Zellen nicht dominant am Anfang der Aufstellung stehen sondern am Ende, hier also nach den Haustieren. Einfach nur absteigend sortieren wird nicht zum Erfolg führen; dann sind zwar die Haustiere in den ersten Zeilen, aber die Reihenfolge wäre dann natürlich Schwein bis Esel, also auch absteigend. Das fällt also schon einmal flach. 😥
Ich gehe da einen steinigen, dafür aber nachvollziehbaren und vor allen Dingen konsequenten Weg. Ich erstelle erst einmal 2 Kopien dieser Abfrage. Dazu öffne ich falls erforderlich das linke Seitenfenster¿ und nach einem Rechtsklick auf Tabelle1 wähle ich Duplizieren und dann noch einmal Tabelle1 | Duplizieren.
Die Zeile Tabelle1 (3) ist ja in der Auflistung der Abfragen noch markiert; also F2, um diesen Eintrag umzubennen. Ich vergebe den Namen leere und bestätige einfach mit Return (Eingabe, Zeilenschaltung). Danach beispielsweise Rechtsklick in Tabelle1 (2) und Umbennnen, um auch dieser Abfrage einen anderen Namen zu geben; ich wähle Tiere als neuen Namen.
Immer noch in der Abfrage Tiere erweitere ich die Überschrift Spalte_A. Und spätestens jetzt wird Ihnen auffallen, dass neben den Tiernamen zwei unterschiedliche Typen Leererzellen existieren:
Nach dem erweitern der Spalte_A
Ganz oben sind die Einträge NULL und (leer), darunter die Namen der Tiere. Der Wert NULL bedeutet ja, dass die Zelle leer ist. Und (leer) heißt ebenfalls, dass die Zelle leer ist. Wo der Unterschied liegt, soll an dieser Stelle nicht diskutiert werden. Nur so viel: NULL ist wirklich leer, (leer) enthält meist einen String, einen Text der Länge 0 (entspricht ""), welcher oft durch eine Formel in Excel erzeugt wird. – Entfernen Sie nun erst einmal das Häkchen bei diesen beiden Einträgen und nach einem Klick auf OK werden sie erkennen, dass in der Abfrage ausschließlich die Namen der Tiere aufgeführt sind. Die vorhandene Sortierung wurde selbstverständlich übernommen; diese sollte aber aufsteigend sein, darum ein Klick auf das Erweiten-Symbol (mit dem großen Filter-Symbol) und dann Klicken Sie auf Aufsteigend sortieren.
Wechseln Sie nun zur Abfrage leere, indem sie im linken Seitenfenster auf diesen Eintrag Klicken. Hier sollen ausschließlich die leeren Zeilen erhalten bleiben, darum nach dem erweitern erst einmal ein Klick auf (Alles auswählen) und anschließend markieren Sie die beiden Zeilen (NULL) und (leer). OK und machen Sie sich bewusst, dass Sie neben der ursprünglichen Abfrage noch 2 gesplittete Abfragen mit und ohne Einträgen in der ersten Spalte haben. 💡
Wechseln Sie nun wiederum zur Abfrage Tiere. Im Menü Start Klicken Sie nun auf Kombinieren | Abfragen Anfügen ▼ (erweitern) | Abfragen als neu anfügen. Füllen Sie den Dialog so aus wie in der folgenden Abbildung gezeigt (leere anklicken):
Der Dialog zum anhängen von Abfragen
Es sollen jetzt zwei Tabellen zusammengefügt werden, die Primäre Tabelle ist Tiere und daran (darunter) soll die Tabelle (Abfrage) leer stehen. Kontrollieren, bestätigen und der Erfolg ist wie gewünscht gegeben:
Die beiden Abfragen, zusammengefügt
Auch wenn die letzten 4 Zellen in Spalte_A hier in der Query nicht identisch sind, im Endergebnis, im Arbeitsblatt sehen die gleich aus. Damit ist auch dieser erste Teil der Sonder-Aufgabe erledigt. Nutzen Sie die Gelegenheit, dieses Ergebnis erst einmal zu sichern. Je nach Geschmack wählen Sie Schließen & laden oder Schließen & laden in…¿, um sich gleich um die Tabelle2 zu kümmern.
Die zweite Tabelle…
In dem Excel Arbeitsblatt unterscheiden sich die beiden Tabellen der Spalten A:B und Spalten D:E nicht. Zumindest nicht in der Ansicht im eigentlichen Arbeitsbereich. Wenn Sie aber einmal in D6 und/oder D11 Klicken werden Sie in der Editierzeile (Eingabezeile) erkennen, dass hier im Gegensatz zur linken Tabelle Formeln enthalten sind. Sonst ist alles gleich, auch das erwünschte Ergebnis, dass nach Haustieren aufsteigend sortiert wird und die leeren Zellen unten stehen. Nur der Weg zum Ziel ist ein anderer.
Meine Empfehlung zur besseren Übersicht bzw. Transparenz: Schließen Sie die eben erstellte Mappe, speichern Sie diese unter einem beliebigen (anderen) Namen und rufen Sie die ursprüngliche Mappe wieder auf. Importieren Sie dann den Bereich D1:E12 in den Power Query-Editor. Normalerweise wird der Name der Abfrage Tabelle2 sein, weil dieser dem des Tabellennamens entspricht.
Um zum gewünschten Ergebnis zu kommen gehe ich hier einen anderen, vielleicht ungewöhnlichen Weg. Er ist auch wie in der ersten Lösung mit Umwegen verbunden, aber es bleibt bei einer einzigen Abfrage. Und an dieser Stelle gleich ein Hinweis: Das funktioniert nur, wenn das erste Zeichen jeder Zelle in Spalte_D (also die Tiere) innerhalb des lateinischen Alphabets angesiedelt ist. Also A bis Z und die Umlaute jeweils in Groß- oder Kleinschreibung sowie das ß.
Sie wissen vielleicht, dass der „normale” Zeichensatz aus 256 Zeichen besteht, wobei jedes dieser Zeichen im Computer mit einer Nummer, einer Zahl verwaltet wird. So hat das Leerzeichen die 32, die Ziffer 0 die 48 und das große A die 65. Und das letzte darstellbare Zeichen dieses Codes ist 255. Nicht 256, denn die Zählung beginnt (wie auch grundsätzlich in Power Query) mit 0.
Ausgerüstet mit diesem Wissen werden Sie nun dafür sorgen, dass die leeren und leer erscheinden Zellen vorübergehend mit dem höchstmöglichen Wert dieses Code-Bereichs gefüllt werden. Markieren Sie die Spalte mit den Tieren durch einen Klick in die Überschrift, Rechtsklick in die Überschrift und im Kontextmenü Werte ersetzen. Für den ersten Durchgang geben Sie bei Zu suchender Wert null (in Kleinbuchstaben) ein und bei Ersetzen durch …
Ja, wie schaffen Sie es, dieses Zeichen mit dem Wert 255 ÿ in das Feld zu bringen? Ich zeige Ihnen hier zwei gangbare Wege auf, die beide zum Ziel führen; derSchreibcursor blinkt im Feld für den künftigen Wert:
- Möglichkeit: Die wohl am meisten genutze Methode: Drücken Sie Alt und halten Sie diese gedrückt. Auf dem Zehnerblock/Ziffernblock (rechts der Tastatur) nacheinander 0255, dann erst lassen Sie Alt los. Im Eingabe-Feld erscheint dann das Zeichen ÿ.
- Weg: Wenn Ihre Tastatur keinen Ziffernblock hat (beispielsweise Notebook) oder Ihnen dieser Weg zu umständlich ist, dann geben Sie ein beliebiges Zeichen ein, welches Sie leicht wiedererkennen. Das kann beispielsweise die Raute # sein. Auch am Beispiel der wirklich leeren Zeile ist dann nicht mehr der Wert null sichtbar; die Editierzeile sieht nach der Bestätigung dann so aus:
= Table.ReplaceValue(#"Ersetzter Wert",null,"#",Replacer.ReplaceValue,{"Spalte_D"})Löschen Sie dann in der Editierzeile das Ersatz-Zeichen mit den Anführungszeichen (hierüber gelb markiert) und tragen Sie an der Stelle diese Funktion ein:
Character.FromNumber(255)
und anschließend beispielsweise auf das Häkchen im Bereich links der Eingabezeile oder einfach ein Klick in den freien Bereich neben der Abfrage.
Wiederholen Sie den Vorgang, nur dass Sie diesmal das Feld Zu suchender Wert leer lassen (also auch kein Leerzeichen). Anschließend sortieren Sie Spalte_D aufsteigend und ersetzen dann noch das Ersatzzeichen ÿ durch null. Sie haben mit relativ wenigen Schritten und in einer einzigen Abfrage Ihr Ziel erreicht. 😎
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen …