Xtract: Mittels Power Query soll aus eine Tabelle der jeweils letzte Datensatz (Zeile) einer zusammengehörenden Gruppe gefiltert werden. 2 mögliche Lösungswege: Das Datum und ein eingefügter Index.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Fragestellung in einem Forum
In einem Forum wurde eine Frage gestellt, die ich hier der Einfachheit halber einfach einmal zitiere:
Letzten Wert in einer Auswahl von Daten – pro Person
Hallo!
Mir stellt sich folgendes Problem. Wie zu sehen habe ich vier Spalten. In der ersten Spalte sind die Mitarbeiter hinterlegt. Chronologisch nach unten verlaufen Jahr, Monate und dann in der vierten Spalte die gewünschten Werte. In einem neuen Tabellblatt sollen immer pro Mitarbeiter der letzte Wert aus Spalte 4 für den jeweiligen Mitarbeiter ausgebenen werden.
Ich dachte schon an eine Kombination aus MAX, Summemprodukt und / oder Index… aber da hapert es nun.
Name Jahr Monat OO max.Wo
Max Mustermann 2020 1 54
Max Mustermann 2020 2 54
Max Mustermann 2020 3 54
Max Mustermann 2020 4 48
Max Mustermann 2020 5 48
Max Mustermann 2020 6 48
Susi Wolke 2020 1 54
Susi Wolke 2020 2 54
Susi Wolke 2020 3 54
Susi Wolke 2020 4 54
Susi Wolke 2020 5 54
Susi Wolke 2020 6 50
Als Ergebnis müsste er den Namen in meiner neuen Tabelle in Spalte A abgleichen und dann in Spalte B den Wert ausgeben:
A B
Max Mustermann 48
Susi Wolke 50
LG Nico
So weit die Anfrage aus dem Forum. Ein gewisses Maß an Klarheit wird ja durch die Aussage geschaffen, dass die Daten jedes Names chronologisch geordnet sind. Dadurch wird deutlich, dass es immer der letzte Eintrag auch die letzte Zeile immerhalb der Namen-Gruppe ist, welcher gesucht wird. Und ein Helfer-Kollege hat auch rasch einen Formel-Vorschlag gemacht, den ich allerdings nicht geprüft habe.
Ich habe mich rasch entschlossen, eine möglichtst praktikable Lösung per PQ zu finden. Und nach wenigen Minuten war mir klar, dass es zumindest drei gut nachvollziehbare Wege gibt, um zum Ziel zu gelangen. Ich stelle Ihen hier 2 Wege vor, die ganz ohne von Hand eingegebene Formeln und Funktionen der Sprache M auskommen. Die dritte von mir angedachte Möglichkeit basiert auf dem Prinzip, welches hier im Blog diskutiert wird.
Für die beiden hier vorgestellten Wege habe ich eine von der Idee her gleichartige, inhaltlich jedoch unterschiedliche Arbeitsmappe erstellt. Möchten Sie mit den Original-Daten arbeiten, dann kopieren Sie diese einfach aus dem Zitat-Bereich (siehe weiter oben) und fügen diese in Ihre Excel Arbeitsmappe ein. Meine Version, die ich auch hier nutze, laden Sie hier von unserem Server herunter.
Und bevor Sie lange überlegen, warum ich nicht die Original-Datei verwende: Erst einmal gibt es bis zum jetzigen Zeitpunkt keine Original-*.xlsx sondern nur eine Text-Darstellung der Daten wie Sie diese auch oben sehen. Das war übrigens der Grund, warum ich keine Lösung im Thread angeboten habe. Ich hasse unnötige Arbeit und dazu gehört, dass ich entweder abschreiben und/oder formatieren muss und mir vielleicht auch noch Formeln ausdenken „darf”, die der Ersteller vielleicht verwendet hat. Außerdem habe ich dafür gesorgt, dass pro Person nicht immer die gleiche Anzahl von Zeilen gegeben ist; sonst wäre es ja erheblich einfacher gewesen, mit einer Berechnung immer die n'te Zeile der Tabelle zu filtern. 😎
Referenz: Datum
Der Überschrift entsprechend werden Sie naturgemäß das Arbeitsbaltt Datum verwenden und die Tabelle NachDatum importieren. Alle Namen sind in Gruppen zusammengefasst und jeweils nach Jahr und Monat aufsteigend sortiert. In der letzten Spalte sind dann numerische Werte eingestellt. Das Ziel: Für jeden Namen soll in einer neuen Abfrage der Name und der Wert der letzten Zeile, sprich des letzten Monats des jeweiligen Namens ausgegeben werden. Und natürlich habe ich es bewusst so eingerichtet, dass der letzte Monat nicht immer den numerischen Wert 6 hat. Das ist die notwendige Prise Salz in der Suppe. 👿
Okay, nach dem Import in den Power Query-Editor sollten Sie die Abfrage gleich einmal über Schließen & laden in… so sichern, dass kein neues Arbeitsbaltt erstellt wird. Die Abfrage anschließend gleich wieder öffnen und erst einmal überlegen, wie denn durch PQ automatisch der jeweils letzte Monat festgestellt werden kann.
So wie sich die Tabelle zurzeit darstellt, kann Power Query nicht mit absoluter Sicherheit berechnen, welches das letzte Datum des entsprechenden Namens ist. Das wird Ihnen rasch klar werden, wenn sie sich vorstellen, dass die kalendarischen Daten jahresübergreifend eingetragen sein könnten. Darum ist es hilfreich, aus den beiden Spalten Jahr und Monat eine einzige Spalte mit einem korrekten Datum zu machen.
Markieren Sie dazu als erstes die Spalte Monat, Strg oder Shift und dann ein Klick im Jahr. Wechseln Sie zum Menü Transformieren und wählen Sie dort im Menüband den Eintrag Spalten zusammenführen. Bei Trennzeichen wählen Sie –Benutzerdefiniert– und In das neu eingeblendete freie Feld Tragen Sie den/ ein; bei Neuer Spaltenname schreiben Sie beispielsweise Monate. Nach einem Klick auf OK werden die beiden Spalten zusammengefügt und es ist dort eine typische Datumsangabe für den jeweiligen Monat geschrieben. Das ganze ist natürlich noch ein Text und kein Datum. Markieren Sie Spalte Monate durch einen Klick in die Überschrift. Im Menüband rechts, Gruppe Datum & Uhrzeit ein Klick auf Datum und die einzig mögliche Auswahl ist Analysieren. Ruckzuck steht nach einem Klick darauf in jeder Zeile dieser Spalte nun ein korrektes Datum. Das ist jeweils der Monat erste ist, spielt keine Rolle. Schließlich gibt es je Namen kein Duplikat beim Monat.
Normalerweise ist links der Abfrage nur ein schmaler Streifen mit dem Text Abfragen zu sehen. Klicken Sie auf den Text oder das Größer-Zeichen und aus dem schmalen Streifen wird das linke Seitenfenster sichtbar. Rechtsklick auf den Namen der einzigen Abfrage NachDatum und wählen Sie im Kontextmenü Verweis. Es wird eine neuer Frage erstellt, welche automatisch den Namen NachDatum (2) bekommen hat. Im rechten Seitenfenster erkennen Sie, dass nur ein einziger Schritt Quelle erfasst ist.
Markieren Sie die Spalte Name und nehmen Sie diese Einstellung vor:
- Das Feld mit dem Eintrag Name bleibt so.
- Bei Neuer Spaltenname tragen Sie beispielsweise letzter Monat ein.
- Bei Vorgang wählen Sie im Dropdown Max.
- Und bei Spalte wählen Sie Monate aus, denn es soll ja der letzte, der höchste Wert in dieser Spalte gefunden werden.
- Schließen Sie den Dialog mit OK.
Das ganze stellt sich nun so dar, wie in der folgenden Abbildung zu sehen:
Korrekte Monatsnamen
Die 4 Namen stehen schon einmal da, auch der richtigen Reihenfolge. Auch das Datum des letzten Eintrages ist korrekt vermerkt. Das ist schon mal eine gute Basis, um die dazu passenden numerischen Werte zu ergänzen. Wechseln Sie erforderlichenfalls zum Register (Menü) Start und wählen dort im Menüband Kombinieren. Ein Klick auf die oberste Auswahl Abfragen zusammenführen und sie sind in jenem Dialog, wo Sie 2 Abfragen zusammenfügen können. Erweitern Sie dass mittige, kleine Textfeld und wählen dort den Eintrag NachDatum. Klicken Sie nun im oberen Kasten zuerst auf Name, Strg und dann in die Spalte letzter Monat. Achten Sie darauf, dass die kleinen Ziffern in den Überschriftfeldern entsprechend der Reihenfolge sind. Nun im unteren Kasten auch zuerst in die Spalte Name, Shift oder Strg und dann in die Spalte Monat Monate Klicken. Auch hier muss die Reihenfolge entsprechend sein.
Nach dem Schließen des Dialogs hat Power Query eine neue Spalte erstellt, wo durchgängig der Inhalt Table ist. Erweitern Sie diese Spalte (NachDatum) durch einen Klick auf den Doppelpfeil in der Überschrift. Im Dialog entfernen Sie alle Häkchen mit Ausnahme der unteren Zeile im Kasten, OO max.Wo. Entfernen Sie nun noch die Spalte letzter Monat und Datei | Schließen & laden in… Wählen Sie im Dialog Bestehendes Arbeitsblatt und Klicken Sie im Arbeitsblatt Datum beispielsweise in die Zelle F1, um dort die Ergebnis-Tabelle zu positionieren.
Das Ziel ist fast erreicht. Ich ziehe es vor, der Klarheit wegen die Ergebnisabfrage noch umzubenennen. Wenn auch Sie diese Abfrage den Namen NachDatum (Ergebnis) geben, ist das Ganze doch etwas klarer.
Referenz: Index
Eine weitere Möglichkeit, dieses Ziel zu erreichen besteht darin, eine Index-Spalte zu nutzen. Und wie Sie unschwer erkennen, existiert eine solche nicht. Importieren Sie also die Excel-Tabelle ein weiteres mal und benennen diese Abfrage idealerweise gleich NachIndex. Wechseln Sie nun zum Menü Spalte hinzufügen und anschließend im Menüband ein Klick auf Indexspalte. PQ erstellt eine Spalte Index, mit dem Wert 0 in Zeile 1 beginnend. Genau diesen Stand der Dinge werden Sie in der letzten Phase noch einmal brauchen, darum an dieser Stelle schon einmal Schließen & laden in… (idealerweise nur als Verbindung) und danach die Abfrage sofort wieder öffnen.
Erstellen Sie nun ein Duplikat dieser Query; ich finde, dass das am besten im linken Seitenfenster geht (Rechtsklick auf den Namen der Abfrage) oder aber auch über das Menü Start | Verwalten | Duplizieren.¿ Die weiteren Schritte werden Sie (erst einmal) in dieser duplizierten Abfrage durchführen.
Markieren Sie die Spalte Name und dann ein Rechtsklick in die Überschrift. Wählen Sie im Kontextmenü Gruppieren nach… und vergeben Sie bei Neuer Spaltenname beispielsweise Letzter. Bei Vorgang ist Max die richtige Auswahl, denn es soll ja der höchste Index der entsprechenden Person erkannt werden. Und bei Spalte ist es naturgemäß Index. Das Ergebnis ist eine 4‑zeilige Abfrage, wo jedem Namen der entsprechende höchste Index-Wert zugeordnet ist:
Zugeordneter maximaler Index
Nun geht es darum, den einzelnen Namen den entsprechenden Wert aus der Spalte OO max.Wo zuzuweisen. Die sind aber „nur” in der zuerst erstellten Abfrage vorhanden. Im Hinblick darauf, dass Sie ja nur die hier aufgelisteten 4 Zeilen ergänzen wollen, bleiben Sie in dieser Abfrage und über Start | Kombinieren | Abfragen zusammenführen tragen Sie im Zusammenführen-Dialog in das kleine Textfeld die Abfrage Nach Index ein. Markieren Sie im oberen Kasten die Spalte Letzter und im unteren Kasten Index:
Zusammenführen der beiden Abfragen
Nach dem schließen des Dialogs hat PQ eine neue Spalte mit der Überschrift der Abfrage generiert. Erweitern Sie diese Überschrift durch einen Klick auf den Doppelpfeil 
und entfernen (wirklich) alle Häkchen, mit Ausnahme bei OO max.Wo und dann OK. – Fast perfekt! Es bleibt nu noch, die Spalte Letzter zu löschen und die Abfrage im Arbeitsblatt Index an gewünschter Position zu speichern. – Und meine Lösung können Sie hier von unserem Server herunterladen.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen …