$PQ: Wer ist heute jünger als ## Jahre?

In einem Forum wurde die Frage gestellt, wie es in Pow­er Query mit möglichst wenig Aufwand mach­bar sei Geburt­stags­dat­en dergestalt zu fil­tern, dass nur Per­so­n­en gezeigt wer­den, welche heute (!) jünger sind als 30 Jahre. Das bedeutet, dass der Fil­ter dynamisch arbeit­en muss. Als „erschw­erend” kommt aus mein­er Sicht noch hinzu, dass die Quell­dat­en in dem Fall als *.csv vor­liegen und nach Möglichkeit in Excel selb­st keine Zelle mit dem aktuellen Tages­da­tum angelegt wer­den sollte. Auch die Altes­gren­ze sollte wohl nicht vari­abel sein (auch wenn das in PQ gut mach­bar ist).

Für die Real­isierung dieser Auf­gabe bietet es sich aus mein­er Sicht an, eine bere­its im Blog existierende Datei mit 1000 Namen und Geburt­sta­gen zu ver­wen­den, auch wenn es sich um keie *.csv son­dern eine *.xlsx han­delt; laden Sie dieses File gerne hier herunter.

Nach dem Import in den Pow­er Query-Edi­tor stellt sich vielle­icht die Frage, ob es der „ein­fache” oder der etwas direk­tere Weg sein soll. Ich stelle Ihnen hier den aus mein­er Sicht deut­lich benutzer­fre­undlicheren Weg vor, da dieser auch für Ein­steiger fast immer gut nachvol­lziehbar ist. An dieser Stelle gle­ich noch ein Hin­weis: Nach dem Laden der Excel-Datei wer­den sie erken­nen, dass die aktuelle Zelle C956 ist. Mit der dort ver­wen­de­ten Formel wird erre­icht, dass die Per­son genau heute Geburt­stag hat, das Jahr wird durch die Funk­tion ZUFALLSBEREICH() fest­gelegt. Hier kön­nen Sie bei Bedarf natür­lich auch ein­tra­gen, dass es genau 30 Jahre sein sollen und beim Fil­ter­vor­gang entsprechend prüfen, ob das Fil­trat ihren Vorstel­lun­gen entspricht. 😉 

Nach kurz­er Über­legung, wie dieses Prob­lem mit den max­i­mal 29 Jahren Alter anzuge­hen sei fiel mir ein, dass PQ ja die Möglichkeit bietet, in ein­er neuen Spalte das Alter direkt berech­nen zu lassen. Das hörte sich gut an und „frisch ans Werk” habe ich gewohn­heits­gemäß erst ein­mal das For­mat der Spalte Geburt­stag auf Datum geän­dert, mich „nervt” ein­fach immer die Zei­tangabe 00:00 Uhr. Wech­sel zum Menü Spalte hinzufü­gen | Datum | Alter. Und das Ergeb­nis war dur­chaus ernüchternd: Error, Error, Error … Na gut, ein Klick in den Zell­bere­ich rechts des Textes Error bringt dann diese Fehler­mel­dung auf den Schirm:

Eine nicht unbe­d­ingt nachvol­lziehbare Fehler­mel­dung…

Aha … 😯 Ver­ste­he ich nicht, denn zur Berech­nung des Alters muss doch in jedem Fall eine Sub­trak­tion (Oper­a­tor -) durchge­führt wer­den. Und genau das hat doch Pow­er Query auch getan. Zugegeben, es gehört schon einiges an Fan­tasie oder auch mehr als nur Basiswis­sen in Sachen Pow­er Query dazu, um den eigentlichen Fehler zu erken­nen. Was ja auf­fäl­lig ist: Bei der Sub­trak­tion der bei­den kalen­darischen Werte han­delt es sich um zwei unter­schiedliche Daten­typen, näm­lich Datum/Uhrzeit und (nur) Datum. Und genau das ist der Cas­sius Cac­tus. 🙂 Markieren Sie Geburt­stag, gehen Sie im recht­en Seit­en­fen­ster auf die Zeile Geän­dert­er Typ, ändern Sie den Typ der Spalte auf Datum/Uhrzeit und wenn Sie anschließend auf die let­zte Zeile bei Angewen­dete Schritte Klick­en, ist die Fehler­mel­dung ver­schwun­den.

Ich bin mit dem Ergeb­nis nicht wirk­lich zufrieden. Viele, auf den ersten Blick nicht wirk­lich aus­sagekräftige Zahlen. Nach kurz­er Ein­schätzung (oder auch ihrem Wis­sen entsprechend) scheint es sich dabei um die Anzahl ver­gan­gener Tage seit dem Tag der Geburt zu han­deln. Und so ist es! Wenn Sie nun über Trans­formieren | Datum & Uhrzeit | Dauer | Jahre gesamt gehen wird Ihnen ein Ergeb­nis aus­gegeben, welch­es vor dem Dez­i­mal­tren­ner die Jahre und danach die Tage als dez­i­mal-numerisch­er Teil des Jahres aus­gibt.

Ich empfinde diese Berech­nungsweise nicht als empfehlenswert. Wenn Sie sich die jew­eils durch Pow­er Query ver­wen­de­ten Funktionen/Formeln anse­hen erken­nen Sie, dass der Wert der Spalte Alter ein­fach nur durch 365 divi­diert wor­den ist. Und das kann nicht das kor­rek­te Ergeb­nis sein, denn es gibt schließlich auch noch Schalt­jahre, welche 366 Tage haben. – Soll­ten Sie diese Unge­nauigkeit nicht stören, kön­nten Sie natür­lich Alter nach dem Wert klein­er 30 fil­tern und das in den meis­ten Fällen liegt das richtige Ergeb­nis vor. Dann noch die Spalte Alter löschen und Geburt­stag ide­al­er­weise nur als Datum for­matieren, dann ist eine sub­op­ti­male Lösung gegeben.

▲ nach oben …

Besser ist es …

… Wenn Sie wirk­lich Tag-genau arbeit­en, was ja auch gefordert war. Also ide­al­er­weise das Ganze noch ein­mal von vorne. Schließen Sie diese Übungs­datei (mit oder ohne zu spe­ich­ern) und importieren Sie das File mit den Quell­dat­en wiederum in den Pow­er Query-Edi­tor. Und gle­ich zu Beginn soll­ten, nein müssen Sie eine Entschei­dung tre­f­fen, wie sie mit dem The­ma „Schalt­jahre” umge­hen wollen. Denn wenn jemand am 29. Feb­ru­ar Geburt­stag hat und das aktuelle Jahr kein Schalt­jahr ist, dann kann das am Tag nach dem 28. Feb­ru­ar zu Irri­ta­tio­nen kom­men. Yan­nick Schloss­er (Tabel­len­blatt Zeile 173) ist solch ein „Kan­di­dat”. Grund­sät­zlich bieten sich 2 Möglichkeit­en:

1. Der Geburt­stag ist, also das neue Leben­s­jahr begin­nt am let­zten Tag des Monats oder
2. in Nicht-Schalt­jahren begin­nt das neue Leben­s­jahr am 1. März, wie es auch in Plain Excel automa­tisch gehand­habt wird. Ich bevorzuge (auch in Pow­er Query) die zweit­ge­nan­nte Möglichkeit.

Okay, öff­nen Sie noch ein­mal die 1000_Namen_mit_Geburtstagen.xlsx, die dann „jungfräulich” und als einzige Abfrage in den PQ-Edi­tor importiert wer­den kann. Und weil mich das Daten­for­mat in Geburt­stag immer noch „nervt”, ändere ich dieses auf Datum (ich kann’s nicht lassen 😉 ). In jedem Fall muss ich nun eine Spalte erzeu­gen, wo das zu ver­wen­dende (eventuell kor­rigierte) Geburts­da­tum drin­nen ste­ht. Also bei Yan­nik Schloss­er soll es in diesem Jahr (2022) der 01.03.2000, in 2024 jedoch kor­rek­ter­weise der 29.02.2000 sein. Sie kön­nten zwar eine zusät­zliche Hil­f­ss­palte anle­gen wo ein Wahrheitswert aus­gegeben wird, ob das aktuelle Jahr ein Schalt­jahr ist oder nicht, aber das sorgt aus mein­er Sicht nicht für wirk­lich mehr Trans­parenz. Also gle­ich eine neue Spalte mit dem Spal­tenna­men Geburt­stag (berech­net) und dieser etwas kom­plex­eren Formel anle­gen:

if
   Date.IsLeapYear(DateTime.LocalNow()) then
      [Geburtstag]
else
   if
      Date.ToText([Geburtstag], "dd.MM.")="29.02." then
         Date.AddDays([Geburtstag],1)
   else
       [Geburtstag]

Dass die Groß- Klein­schrei­bung exakt so sein muss, wis­sen Sie? Und eine kleine Erk­lärung bin ich Ihnen vielle­icht doch schuldig, was die Formel bet­rifft. „Über­set­zt” heißt das prinzip­iell:

wenn
   das aktuelle Jahr ein Schaltjahr ist dann
      das Feld Geburtstag übernehmen, denn es gibt ja den 29. Februar
ansonsten
   wenn
      der in Text konvertierte Geburtstag der 29.02. ist dann
         addiere zu Geburtstag 1 Tag
   ansonsten
      übernehme den Wert aus Geburtstag

Das Ergeb­nis sieht erst ein­mal recht gut aus. Und in Zeile 172 ste­ht auch tat­säch­lich der 1. März als Datum. Oder…? Wirk­lich als Daten­typ Datum? Nein, die gesamte Spalte ist vom Daten­typ Beliebig. Und da im näch­sten Schritt das Alter abgeglichen wer­den soll, passen Sie den Daten­typ der Spalte auch auf Datum/Zeit an.

Noch ein­mal die Ursprungs­frage: Ist die jew­eilige Per­son heute jünger als 30 Jahre? Dass das nicht mit der Funk­tion­al­ität Datum | Alter geht, haben Sie ja bere­its fest­gestellt. Aber Pow­er Query bietet auch hier eine gute Möglichkeit, in ein­er Benutzerdefinierten Spalte das kor­rek­te Ergeb­nis darzustellen. Als Spal­tenüber­schrift nehme ich Jünger 30 Jahre und die Formel ist hier recht über­sichtlich:
Date.AddYears(DateTime.LocalNow(), -30) < [#"Geburtstag (kalkuliert)"]
Das Ergeb­nis ist naturgemäß TRUE oder FALSE und wenn Sie dann diese Spalte nach TRUE fil­tern, kön­nen Sie die bei­den let­zten Spal­ten (Geburt­stag (kalkuliert) und Jünger 30 Jahre?) gerne löschen; es wer­den nur die Namen mit den realen Geburt­sta­gen der unter 30-jähri­gen angezeigt. Die Auf­gabe ist gelöst.

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,50 € freuen … (← Klick mich!)