Xtract: In einer Tabelle/Liste soll für jede Zeile der letzte eingetragene Wert in einer gesonderten Spalte per Power Query ausgegeben werden.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Ab und zu wird in Excel-Foren die Frage gestellt, welchen Inhalt die letzte Spalte mit einem beliebigen Wert hat. Leere Zellen oder ein LeerString ("") sollen nicht berücksichtigt werden. Da ich auf dem gemieteten Web-Server Platz sparen muss, habe ich Ihnen hier eine Muster-Datei im *.csv – Format bereit gestellt. Und da es in Plain Excel bereits diverse Lösungen in Foren und auch im weiteren Netz gibt habe ich mich für eine Power Query-Lösung entschieden. Falls Sie Hilfe beim Import in PQ brauchen, schauen Sie bitte hier nach.
Sehen Sie sich die 20 Daten-Zeilen einmal in Ruhe an und erkennen Sie, dass unterschiedliche Spalten den letzten Wert der Zeile enthalten und auch ganz leere Zeilen dabei sind. Ich stelle Ihnen hier 2 sehr ähnliche Lösungen vor, welche ‑jede für sich- ihren eigenen Reiz hat. Für beide Lösungswege gilt, dass Sie nach dem Importhauptsächlich mit einer Arbeitskopie arbeiten.
Lösung 1
Nach dem Import in den PQ-Editor erstellen Sie die Arbeitskopie per Duplizieren und benennen diese gleich zu LetzterWertSpalte (Arbeitskopie) um. Bis auf Widerruf werden Sie nur in dieser Query arbeiten. – Markieren Sie alle Spalten, Rechtsklick in eine der Überschriften und Spalten entpivotieren. Die erste Idee könnte sein, nach Attribut zu Gruppieren und das Maximum bei Vorgang und bei Spalte Wert auszuwählen. Aber das ist ein Denkfehler, denn das klappt zwar bei den ersten beiden Zeilen (de ersten Gruppe) aber beim nächsten Block (Zeile 2 bis Zeile 10) stimmt das Ergebnis nicht. 🙁
Also ist ein neuer Denkansatz erforderlich. Irgendwie muss dem Attribut noch die jeweilige Zeilennummer der Rohdaten zugewiesen werden. Klicken Sie dazu im rechten Seitenfenster bei Angewendete Schritte auf den Schritt vor dem Entpivotieren (also Geänderter Typ) und wechseln Sie im Menüband zu Spalte hinzufügen. Ein Klick auf Indexspalte und nach der Bestätigung per Klick auf Einfügen ist die Index-Spalte eingefügt. Klicken Sie im Seitenfenster wieder auf die letzte Zeile der Liste und Überraschung, es hat sich nichts getan. 🙁 Der aus meiner Sicht einfachste Weg ist nun, die Entpivotieren-Zeile zu löschen und jetzt die Spalte Index markieren, Rechtsklick und Andere Spalten entpivotieren. Und nun ist jedem Wert in Attribut eine Zeilenzahl (Index) zugeordnet.
Durch das Entpivotieren sind ja alle leeren Zellen eliminiert worden. Nun geht es darum, die jeweils letzte Zeile eines Blocks festzustellen und idealerweise die anderen Zeilen zu löschen. Die eine Möglichkeit wäre, jede Gruppe in sich fortlaufend zu nummerieren und dann den höchsten Wert herauszufiltern. Ich biete Ihnen hier einen anderen Weg an:
- Wechseln Sie zum Menü Transformieren
- Klicken Sie in der Menüband-Gruppe Tabelle auf den Eintrag Zeilen umkehren
- Markieren Sie die Spalte Index
- Rechtsklick in die Überschrift und Duplikate entfernen
- Um nun die Original-Reihenfolge herzustellen wiederum Zeilen umkehren
- … und jetzt die Spalte Wert (irgendwie) der Ursprungstabelle anfügen.
„Irgendwie” ist ja nun keine detaillierte Beschreibung. 😎 Und auf direktem Wege geht das auch nicht. Aber wenn Sie auch hier einen Index, der mit Sicherheit identisch zu dem in der Arbeitskopie erstellten Index ist erstellen, dann klappt das auch. Ausnahmsweise reicht es hier, dass Sie direkt mit dem Erstellen einen (auch) nullbasierten Index erstellen. Danach Start | Abfragen zusammenführen und wählen Sie als zweite Abfrage LetzterWertSpalte (Arbeitskopie). Markieren Sie in beiden Abfrage-Fenstern die Spalte Index und bestätigen mit OK. Erweitern Sie die neu erstellte Spalte durch einen Klick auf 
und lassen Sie im gesamten Dialogfenster ausschließlich das Häkchen bei Wert bestehen. Als letzten Schritt können Sie noch die Überschrift auf (beispielsweise) Wert letzte Spalte ändern. Und eine Sichtkontrolle wird Ihnen zeigen, dass das Ergebnis korrekt ist, auch wenn einmal eine leere Zelle mittendrin ist oder gar kein Wert vorhanden war. Je nach Wunsch können Sie nun die Abfrage in einem existierenden oder einem neuen Abeitsblatt speichern.
Hier noch ein Hinweis: So ganz „sauber” ist diese Vorgehensweise mit dem nachträglich eingetragenen Index in der Basistabelle nicht. Prinzipiell sollte der Index dort vor dem Duplizieren erstellt werden; es gibt einige Situationen, wo das sonst zu falschen Ergebnissen führen kann. Oder Sie gehen den nachfolgend gezeigten Weg.
Lösung 2
Wie gehabt importieren Sie in einer neuen Mappe die *.csv. Im PQ-Editor erstellen Sie nun ebenfalls als erstes eine Arbeitskopie. Aber Sie gehen nicht den Weg über Duplizieren sondern über Verweis. Auch hier vergebe ich den Namen LetzterWertSpalte (Arbeitskopie). Sie sehen, dass beide Abfragen identisch sind. Wechseln Sie nun zur Ursprungs-Abfrage und fügen Sie hier einen Index ein. Wechseln Sie nun zur Arbeitskopie und siehe da, wie von Geisterhand ist nun auch hier diese Spalte vorhanden. Prima! Markieren Sie hier nun Index und über einen Rechtsklick Andere Spalten entpivotieren. Da ich gerne dem Prinzip folge, der Verarbeitungsgeschwindigkeit/Performance wegen überflüssige Daten so früh wie möglich zu entfernen, lösche ich jetzt schon die Spalte Attribut. Nun wieder Zeilen umkehren, in der Spalte Index Duplikate entfernen und die ursprüngliche Reihenfolge wieder herstellen (auch wenn das gar nicht erforderlich ist).
Möglichkeit 1
Nun geht es darum, die beiden Abfragen wieder zusammenzufügen. Sie wechseln also zur Ursprungstabelle LetzterWertSpalte und Start | Abfragen zusammenführen. Und bei der Auswahl der zu verknüpfenden Tabelle kommt die große Überraschung, denn die Arbeitskopie ist nicht in der Auswahl verfügbar. Und das liegt daran, dass kein Duplikat erstellt worden ist sondern ein Verweis. Also gehen Sie über Abbrechen, um den Dialog zu schließen. Und jetzt bieten sich Ihnen 2 Wege an, die ich Ihnen kurz vorstelle:
- Start | Abfragen zusammenführen
| Abfragen als neue Abfrage zusammenführen - Auswahl der Abfrage LetzterWertSpalte
- In beiden Abfragen Index markieren
- … und den Dialog per OK schließen.
- Die neu erstellte Spalte per Klick auf den Doppelpfeil erweitern und ausschließlich das Häkchen bei Wert stehen lassen.
Das Vorgehen entspricht weitgehend dem aus dem ersten Teil weiter oben. Und auch hier wird die visuelle Kontrolle zeigen, dass die Werte stimmen. Das Anpassen der Überschrift überlasse ich Ihnen nach eigenem Geschmack. Zwei Anmerkungen seien mir an dieser Stelle gestattet: Es sind jetzt 3 Abfragen im Editor und das Ergebnis hat 20 Zeilen. Das ist selbstverständlich? Na ja…
Möglichkeit 2
Die zweite Möglichkeit der Lösung 2 ist etwas anders. Wiederum am Schnittpunkt, wo die Daten zusammengeführt werden sollen, bleiben Sie in der Query LetzterWertSpalte (Arbeitskopie). Der Transparenz wegen können Sie auch gerne die eben erstellte Abfrage löschen, damit wieder nur 2 Abfragen gegeben sind. Sie bleiben in dieser Arbeits-Abfrage und
- Start | Abfragen zusammenführen
- Auswahl der Abfrage LetzterWertSpalte
- In beiden Abfragen Index markieren
- … und den Dialog per OK schließen.
- Die neu erstellte Spalte per Klick auf den Doppelpfeil erweitern und nun ausschließlich das Häkchen bei Index entfernen.
Sie erkennen, dass sich eigentlich gar nicht so viel in der Vorgehensweise geändert hat. Aber das Ergebnis ist auf den ersten Blick ziemlich unstrukturiert. Index und Wert stehen prominent ganz links und die Reihenfolge der Zeilen stimmt auch nicht so wirklich. 😎 Und es sind nur 18 Zeilen in der Abfrage! Warum fehlen 2 Zeilen und welche sind das? Nun ja, es sind genau die 2 Zeilen, wo in den Ursprungsdaten keine einzige Spalte gefüllt war, alle Spalten enthielten den Wert null. Das können Sie als Vorteil oder auch als Nachteil sehen; eine Änderung wäre mit etwas Aufwand durchaus möglich. – Bleibt eigentlich nur noch, die Query nach Index aufsteigend zu sortieren, diese Spalte danach zu löschen und Wert an das Ende (als letzte Spalte) zu verschieben.
Während Sie bei Lösung 1 und der Möglichkeit 1 bei der Lösung 2 die Ausgabe der Daten auf die Spalte mit dem berechneten Wert beschränken könnten und diese dann im Arbeitsblatt neben die Excel-Ursprungsdaten platzieren könnten weil die Reihenfolge und die Anzahl der Ergebnisse ja gleich ist, würde die verkürzte Darstellung der Möglichkeit 2 der gewollten Aussage nicht gerecht 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!)