Xtract: Aus einer Liste von 1.000 Namen und Geburtstagen sollen jene Personen in eine getrennte Tabelle geschrieben werden, die heute Geburtstag haben; und das Alter wird auch berechnet.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Wer hat heute Geburtstag und wie alt wird sie/er?
Von unserem Server können Sie hier eine Liste mit 1000 Namen herunterladen. Jeder dieser Personen habe ich per ZUFALLSBEREICH()-Funktion einen Geburtstag zugeordnet. Dabei gibt es jedoch 4 Namen, die eine Besonderheit darstellen: Carolin Lenz, Peter Sommer, Ina Herbst und John Winter; diesen Damen und Herren habe ich per Formel eine Geburtstag „verpasst”, der immer am aktuellen, heutigen Tag jedoch in verschiedenen Jahren liegt. So ist gewährleistet, dass auf jeden Fall diese Personen als Treffer ausgegeben werden. Da ich aber auch den Geburts-Jahrgang per Zufall berechnen lasse, wird das berechnete Alter bei ihren Tests gewiss von meinen bildlichen Darstellungen mehr oder weniger abweichen.
Um festzustellen ob (beispielsweise) ein Vereinsmitglied heute Geburtstag hat muss ja vom Geburtsdatum der Tag und der Monat mit den gleichen Parametern des heutigen Datums verglichen werden. In Plain Excel gibt es recht viele Möglichkeiten, diesen Vergleich durchzuführen. Ich habe mir vorgenommen, mit Power Query eine sinnvolle und praktikable Lösung zu finden.
Natürlich gibt es auch in PQ mehrere Wege, zum Ziel zu gelangen. Prinzipiell gilt für meine Vorgehensweise, dass ich den minimalistischen Weg so gut wie nie beschreite; ich gehe meist den „goldenen Mittelweg”, weil dieser in den meisten Fällen besser nachvollziehbar ist. Und oft ist es auch einfach nur eine Geschmacksfrage.
Importieren sie erst einmal meine Beispieldatei in den Power Query-Editor. Da mich die Angabe der Uhrzeit in den kalendarischen Daten in diesem und auch den allermeisten anderen Fällen einfach nur nervt, ändere ich als erstes den Datentyp auf Datum. Sie können das auf dem üblichen Weg über das Menüband oder per Rechtsklick in die Überschrift Geburtstag und dann im Kontextmenü Datentyp erledigen, ich habe mir angewöhnt, in der Editierzeile den Datentyp direkt zu ändern, indem ich die letzten 4 Zeichen beim Schlüsselbegriff datetime
lösche und nur date
stehen bleibt:
Diese Vorgehensweise hat den Vorteil, dass ich nicht noch einmal das ändern des Datentyps bestätigen muss. Im nächsten Schritt werden sie Tag und Monat aus dem Feld mit der Überschrift Geburtstag extrahieren. Ich gehe dazu so vor:
- Wechsel zum Menü Spalte hinzufügen
- Im Menüband ein Klick auf Benutzerdefinierte Spalte
- Bei Neuer Spaltenname trage ich TT.MM. GebTag ein.
- Als Benutzerdefinierte Spaltenformel schreibe ich
Date.ToText([Geburtstag], "dd.MM.")
was sich dann im Dialog so darstellt:
Und denken Sie daran, dass in Formeln die Groß- Kleinschreibung beachtet werden muss. Power Query hat anschließend eine neue Spalte vom Datentyp: Beliebig generiert, wobei ich der Eindeutigkeit wegen den Datentyp auf Text anpasse. Und ich räume ein, dass ich bei einem Kunden-Projekt in diesem Falle ein echtes Datum des Geburtstages des aktuellen Jahres für den Vergleich in dieser Spalte verwenden würde und nicht nur Tag und Monat. Dieser Datentyp wäre dann natürlich auch Datum. Sollten Sie zu den Usern gehören, die nach Möglichkeit keine Formeln bzw. Funktionen direkt eingeben mögen, sondern lieber per Mausklick zum Ziel gelangen, gibt es noch folgende Möglichkeit:
-
- Markieren Sie die Spalte Geburtstag,
- Rechtsklick in die Überschrift und im Kontextmenü Spalte aus Beispielen hinzufügen. Alternativ geht das auch über das Menü Spalte hinzufügen.
- Tragen Sie in die weit rechts stehende Spalte bei Spalte1 das Datum in der gewünschten Form ein, also hier 15.06.
- Lassen Sie sich von den Vorschlägen nicht irritieren, einfach nur mit Return (Enter) bestätigen.
- Da vermutlich hier oder da noch einmal auch das Jahr bei den in grau vorgeschlagenen Ergebnissen angezeigt wird, tragen Sie in die zweite Zeile auch noch einmal das Wunschergebnis im richtigen Format, hier 07.06. ein.
- Der Titel oberhalb der rechten Beispiel-Spalte hat sich auf Benutzerdefiniert geändert.
- Eine kurze Kontrolle wird Ihnen zeigen, dass nun alle vorgeschlagenen Datumswerte ihrem Wunsch entsprechen und sie bestätigen mit OK.
- Anschließend wäre es hilfreich, die Überschrift den Gegebenheiten anzupassen.
Im nächsten Schritt erstelle ich wiederum eine neue, Benutzerdefinierte Spalte und trage dort nach dem gleichen Muster wie vorher im Formel-Editor eine Formel zur Berechnung des heutigen Tages ein. Also auch nur Tag und Monat, wobei das Datenformat mit dem des Geburtstages dieses Jahres übereinstimmen muss.
Nun werden Sie sich gewiss fragen, wie ich automatisch das aktuelle Tagesdatum in diese Spalte einfüge. Eine fertige Funktion mit dem Namen heute, today oder ähnlich gibt es nicht in Power Query. Aber natürlich gibt es in PQ eine Möglichkeit, das heutige Datum als Zellinhalt zu generieren. Und hier haben Sie zwei, drei Möglichkeiten, das durchzuführen:
- Sie erstellen eine eigene, Benutzerdefinierte Funktion in der Sprache M, die dann beispielsweise fn_Heute heißt.
- Sie schreiben in das Excel-Arbeitsblatt die Funktion HEUTE() und referenzieren auf diese Zelle.
- Sie gehen den für Einsteiger gewiss leichtesten Weg und wählen Spalte hinzufügen | Benutzerdefinierte Spalte und tragen dort erst einmal diese Formel ein:
= DateTime.LocalNow()
- Vergeben sie einen sinnvollen Spaltennamen, bevor sie auf OK Klicken.
- Passen Sie nun die Spalte auf die Gegebenheiten der vorherigen Spalte an. Steht dort ein 6‑stelliger Text mit Tag und Datum im Format Text, dann muss das hier gleichermaßen der Fall sein. Steht der links benachbarten Spalte ein echtes Datum, müssenen Sie die Spalte Heute zwingend als Datentyp: Datum einzurichten.
Ich selbst mache es mir etwas leichter. Ich öffne noch einmal den Dialog des letzten Schritts durch Doppelklick auf den Eintrag Hinzugefügt Benutzerdefinierte Spalte im rechten Seitenfenster und ergänze die dort eingetragene Formel so:
DateTime.ToText(DateTime.LocalNow(), "dd.MM.")
… und anschließend weise ich dieser Spalte den gleichen Datentyp zu wie in der Spalte links daneben. Natürlich ist die Spalte links der schon angesprochene 6‑stellige Text. Im nächsten Schritt werden Sie wiederum eine Benutzerdefinierte Spalte erstellen. Auch wenn die Überschrift so bleiben könnte nenne ich sie aus Gründen der Transparenz check oder Vergleich. Unter der Annahme, dass die Spalte mit den Geburtstagen des aktuellen Jahres die Überschrift TT.MM. GebTag hat gebe ich hier diese Formel ein:
[TT.MM. GebTag]=[Heute]
Vermutlich werden sie zu Anfang nur den Eintrag FALSE in den Feldern sehen. Das ist auch richtig so, denn es wäre reiner Zufall, wenn in den ersten Zeilen einer der „Kandidaten” gerade heute Geburtstag hätte. Erweitern Sie die Spalte check und entfernen Sie das Häkchen bei FALSE, damit nur noch jene Einträge mit dem Ergebnis TRUE sichtbar sind. Sie werden mindestens die vier bereits oben aufgeführten „Geburtstagskinder” sehen, wahrscheinlich sogar den einen oder anderen Namen mehr:
Nach einer visuellen Kontrolle werden Sie vielleicht mit Ausnahme der Spalten Name und Vorname und eventuell Geburtstag alle anderen Spalten löschen und über Schließen & laden bzw. Schließen & laden in… an dezidierter Stelle speichern. Damit ist dann der eigentliche, wichtigste Teil der „Aufgabe” gelöst.
Hinweis: Dieses ist nur einer von mehreren möglichen Wegen. Insbesondere wenn Sie gerne mit Formeln arbeiten werden Sie im Fundus der Sprache M im Bereich der Datum-Funktionen fündig. – Und selbstredend ist es auch möglich, mit einer kleinen Anpassung den Datumsbereich Ihren Wünschen entsprechend anzupassen.
Das Alter berechnen
Als zweite Fragestellung war gegeben, das am Tage des Geburtstages erreichte Alter durch Power Query berechnen zu lassen. Wenn Sie auf der eben erstellten Abfrage aufbauen achten Sie bitte darauf, das die Spalte Geburtstag zu Beginn unbedingt eingeblendet sein muss. Von der Idee her muss ja „nur” das Jahr der Geburt vom aktuellen Jahr subtrahiert werden, um das korrekte Ergebnis ab dem Tag des Geburtstags zu bekommen. Erstellen Sie eine Benutzerdefinierte Spalte mit der Überschrift Alter und verwenden Sie diese Formel:
= Date.Year(DateTime.LocalNow())-Date.Year([Geburtstag])
Das Ergebnis stimmt. Sie könnten dieses Ergebnis innerhalb Power Query mit dem Zusatz ” Jahre” versehen und somit als Datentyp Text einrichten; ich würde solch eine Formatierung aber in Excel über das Zahlenformat durchführen, denn wenn in einer Zelle wirklich eine Zahl steht, welche gerne durch ein benutzerdefiniertes Zahlenformat ihr Aussehen ändert, kann dieser Bereich auch sortiert werden oder auch in eine Berechnung einbezogen werden.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00 € freuen …