PQ: Wer hat heute Geburtstag?

Von unserem Serv­er kön­nen Sie hier eine Liste mit 1000 Namen herun­ter­laden. Jed­er dieser Per­so­n­en habe ich per ZUFALLSBEREICH()-Funk­tion einen Geburt­stag zuge­ord­net. Dabei gibt es jedoch 4 Namen, die eine Beson­der­heit darstellen: Car­olin Lenz, Peter Som­mer, Ina Herb­st und John Win­ter; diesen Damen und Her­ren habe ich per Formel eine Geburt­stag „ver­passt”, der immer am aktuellen, heuti­gen Tag jedoch in ver­schiede­nen Jahren liegt. So ist gewährleis­tet, dass auf jeden Fall diese Per­so­n­en als Tre­f­fer aus­gegeben wer­den. Da ich aber auch den Geburts-Jahrgang per Zufall berech­nen lasse, wird das berech­nete Alter bei ihren Tests gewiss von meinen bildlichen Darstel­lun­gen mehr oder weniger abwe­ichen.

Um festzustellen ob (beispiel­sweise) ein Vere­ins­mit­glied heute Geburt­stag hat muss ja vom Geburts­da­tum der Tag und der Monat mit den gle­ichen Para­me­tern des heuti­gen Datums ver­glichen wer­den. In Plain Excel gibt es recht viele Möglichkeit­en, diesen Ver­gle­ich durchzuführen. Ich habe mir vorgenom­men, mit Pow­er Query eine sin­nvolle und prak­tik­able Lösung zu find­en.

Natür­lich gibt es auch in PQ mehrere Wege, zum Ziel zu gelan­gen. Prinzip­iell gilt für meine Vorge­hensweise, dass ich den min­i­mal­is­tis­chen Weg so gut wie nie beschre­ite; ich gehe meist den „gold­e­nen Mit­tel­weg”, weil dieser in den meis­ten Fällen bess­er nachvol­lziehbar ist. Und oft ist es auch ein­fach nur eine Geschmacks­frage.

▲ nach oben …

Importieren sie erst ein­mal meine Beispiel­d­atei in den Pow­er Query-Edi­tor. Da mich die Angabe der Uhrzeit in den kalen­darischen Dat­en in diesem und auch den aller­meis­ten anderen Fällen ein­fach nur nervt, ändere ich als erstes den Daten­typ auf Datum. Sie kön­nen das auf dem üblichen Weg über das Menüband oder per Recht­sklick in die Über­schrift Geburt­stag und dann im Kon­textmenü Daten­typ erledi­gen, ich habe mir angewöh­nt, in der Edi­tierzeile den Daten­typ direkt zu ändern, indem ich die let­zten 4 Zeichen beim Schlüs­sel­be­griff datetime lösche und nur date ste­hen bleibt:

Mein Weg, den Daten­typ kom­fort­a­bel anzu­passen

Diese Vorge­hensweise hat den Vorteil, dass ich nicht noch ein­mal das ändern des Daten­typs bestäti­gen muss. Im näch­sten Schritt wer­den sie Tag und Monat aus dem Feld mit der Über­schrift Geburt­stag extrahieren. Ich gehe dazu so vor:

• Wech­sel zum Menü Spalte hinzufü­gen
• Im Menüband ein Klick auf Benutzerdefinierte Spalte
• Bei Neuer Spal­tenname trage ich TT.MM. Geb­Tag ein.
• Als Benutzerdefinierte Spal­tenformel schreibe ich
  Date.ToText([Geburtstag], "dd.MM.")
  was sich dann im Dia­log so darstellt:

Geben Sie diese Formel in exakt diert Groß- / Klein­schrei­bung ein

Und denken Sie daran, dass in Formeln die Groß- Klein­schrei­bung beachtet wer­den muss. Pow­er Query hat anschließend eine neue Spalte vom Daten­typ:  Beliebig gener­iert, wobei ich der Ein­deutigkeit wegen den Daten­typ auf Text anpasse. Und ich räume ein, dass ich bei einem Kun­den-Pro­jekt in diesem Falle ein echt­es Datum des Geburt­stages des aktuellen Jahres für den Ver­gle­ich in dieser Spalte ver­wen­den würde und nicht nur Tag und Monat. Dieser Daten­typ wäre dann natür­lich auch Datum. Soll­ten Sie zu den Usern gehören, die nach Möglichkeit keine Formeln bzw. Funk­tio­nen direkt eingeben mögen, son­dern lieber per Mausklick zum Ziel gelan­gen, gibt es noch fol­gende Möglichkeit:

• • Markieren Sie die Spalte Geburt­stag,
  • Recht­sklick in die Über­schrift und im Kon­textmenü Spalte aus Beispie­len hinzufü­gen. Alter­na­tiv geht das auch über das Menü Spalte hinzufü­gen.
  • Tra­gen Sie in die weit rechts ste­hende Spalte bei Spalte1 das Datum in der gewün­scht­en Form ein, also hier 15.06.

Vielfach eine bequeme Möglichkeit, Dat­en ohne Formeln umzuwan­deln (ähn­lich Blitzvorschau)

Ver­größert­er Auss­chnitt der recht­en Spalte

• Lassen Sie sich von den Vorschlä­gen nicht irri­tieren, ein­fach nur mit Return (Enter) bestäti­gen.
• Da ver­mut­lich hier oder da noch ein­mal auch das Jahr bei den in grau vorgeschla­ge­nen Ergeb­nis­sen angezeigt wird, tra­gen Sie in die zweite Zeile auch noch ein­mal das Wun­schergeb­nis im richti­gen For­mat, hier 07.06. ein.
• Der Titel ober­halb der recht­en Beispiel-Spalte hat sich auf Benutzerdefiniert geän­dert.
• Eine kurze Kon­trolle wird Ihnen zeigen, dass nun alle vorgeschla­ge­nen Datum­swerte ihrem Wun­sch entsprechen und sie bestäti­gen mit OK.
• Anschließend wäre es hil­fre­ich, die Über­schrift den Gegeben­heit­en anzu­passen.

Im näch­sten Schritt erstelle ich wiederum eine neue, Benutzerdefinierte Spalte und trage dort nach dem gle­ichen Muster wie vorher im Formel-Edi­tor eine Formel zur Berech­nung des heuti­gen Tages ein. Also auch nur Tag und Monat, wobei das Daten­for­mat mit dem des Geburt­stages dieses Jahres übere­in­stim­men muss.

Nun wer­den Sie sich gewiss fra­gen, wie ich automa­tisch das aktuelle Tages­da­tum in diese Spalte ein­füge. Eine fer­tige Funk­tion mit dem Namen heute, today oder ähn­lich gibt es nicht in Pow­er Query. Aber natür­lich gibt es in PQ eine Möglichkeit, das heutige Datum als Zellinhalt zu gener­ieren. Und hier haben Sie zwei, drei Möglichkeit­en, das durchzuführen:

1. Sie erstellen eine eigene, Benutzerdefinierte Funk­tion in der Sprache M, die dann beispiel­sweise fn_Heute heißt.
2. Sie schreiben in das Excel-Arbeits­blatt die Funk­tion HEUTE() und ref­eren­zieren auf diese Zelle.
3. Sie gehen den für Ein­steiger gewiss leicht­esten Weg und wählen Spalte hinzufü­gen | Benutzerdefinierte Spalte und tra­gen dort erst ein­mal diese Formel ein:
   = DateTime.LocalNow()
4. Vergeben sie einen sin­nvollen Spal­tenna­men, bevor sie auf OK Klick­en.
5. Passen Sie nun die Spalte auf die Gegeben­heit­en der vorheri­gen Spalte an. Ste­ht dort ein 6‑stelliger Text mit Tag und Datum im For­mat Text, dann muss das hier gle­icher­maßen der Fall sein. Ste­ht der links benach­barten Spalte ein echt­es Datum, müsse­nen Sie die Spalte Heute zwin­gend als Daten­typ:  Datum einzuricht­en.

Ich selb­st mache es mir etwas leichter. Ich öffne noch ein­mal den Dia­log des let­zten Schritts durch Dop­pelk­lick auf den Ein­trag Hinzuge­fügt Benutzerdefinierte Spalte im recht­en Seit­en­fen­ster und ergänze die dort einge­tra­gene Formel so:
DateTime.ToText(DateTime.LocalNow(), "dd.MM.")

… und anschließend weise ich dieser Spalte den gle­ichen Daten­typ zu wie in der Spalte links daneben. Natür­lich ist die Spalte links der schon ange­sproch­ene 6‑stellige Text. Im näch­sten Schritt wer­den Sie wiederum eine Benutzerdefinierte Spalte erstellen. Auch wenn die Über­schrift so bleiben kön­nte nenne ich sie aus Grün­den der Trans­parenz check oder Ver­gle­ich. Unter der Annahme, dass die Spalte mit den Geburt­sta­gen des aktuellen Jahres die Über­schrift TT.MM. Geb­Tag hat gebe ich hier diese Formel ein:
[TT.MM. GebTag]=[Heute]

Ver­mut­lich wer­den sie zu Anfang nur den Ein­trag FALSE in den Feldern sehen. Das ist auch richtig so, denn es wäre rein­er Zufall, wenn in den ersten Zeilen ein­er der „Kan­di­dat­en” ger­ade heute Geburt­stag hätte. Erweit­ern Sie die Spalte check und ent­fer­nen Sie das Häkchen bei FALSE, damit nur noch jene Ein­träge mit dem Ergeb­nis TRUE sicht­bar sind. Sie wer­den min­destens die vier bere­its oben aufge­führten „Geburt­stagskinder” sehen, wahrschein­lich sog­ar den einen oder anderen Namen mehr:

Die gefilterten Dat­en des aktuellen (heuti­gen) Tages

Nach ein­er visuellen Kon­trolle wer­den Sie vielle­icht mit Aus­nahme der Spal­ten Name und Vor­name und eventuell Geburt­stag alle anderen Spal­ten löschen und über Schließen & laden bzw. Schließen & laden in… an dezi­diert­er Stelle spe­ich­ern. Damit ist dann der eigentliche, wichtig­ste Teil der „Auf­gabe” gelöst.

Hin­weis: Dieses ist nur ein­er von mehreren möglichen Wegen. Ins­beson­dere wenn Sie gerne mit Formeln arbeit­en wer­den Sie im Fun­dus der Sprache M im Bere­ich der Datum-Funk­tio­nen fündig. – Und selb­stre­dend ist es auch möglich, mit ein­er kleinen Anpas­sung den Datums­bere­ich Ihren Wün­schen entsprechend anzu­passen.

▲ nach oben …

Das Alter berechnen

Als zweite Fragestel­lung war gegeben, das am Tage des Geburt­stages erre­ichte Alter durch Pow­er Query berech­nen zu lassen. Wenn Sie auf der eben erstell­ten Abfrage auf­bauen acht­en Sie bitte darauf, das die Spalte Geburt­stag zu Beginn unbe­d­ingt einge­blendet sein muss. Von der Idee her muss ja „nur” das Jahr der Geburt vom aktuellen Jahr sub­trahiert wer­den, um das kor­rek­te Ergeb­nis ab dem Tag des Geburt­stags zu bekom­men. Erstellen Sie eine Benutzerdefinierte Spalte mit der Über­schrift Alter und ver­wen­den Sie diese Formel:
= Date.Year(DateTime.LocalNow())-Date.Year([Geburtstag])

Das Ergeb­nis stimmt. Sie kön­nten dieses Ergeb­nis inner­halb Pow­er Query mit dem Zusatz ” Jahre” verse­hen und somit als Daten­typ Text ein­richt­en; ich würde solch eine For­matierung aber in Excel über das Zahlen­for­mat durch­führen, denn wenn in ein­er Zelle wirk­lich eine Zahl ste­ht, welche gerne durch ein benutzerdefiniertes Zahlen­for­mat ihr Ausse­hen ändert, kann dieser Bere­ich auch sortiert wer­den oder auch in eine Berech­nung ein­be­zo­gen wer­den.

▲ nach oben …