PQQ: Jahreskalender mit Power Query erstellen (1)

Power Query Quickies: Einen Jahreskalender automatisch erstellen lassen

Auch in Power Query gibt es mehrere Wege, einen Jahreskalender automatisch erstellen zu lassen. In jedem Falle werden Sie dazu Funktionen der Abfragesprache M einsetzen. Und dennoch brauchen Sie keine Programmier-Erfahrung, wenn die hier dargelegten Schritte exakt nachvollziehen. Ein grundlegender Hinweis vorweg: Achten Sie unbedingt darauf, die Groß- Kleinschreibung genau so zu schreiben, wie hier im Beitrag dargestellt.

(1) Die Funktion List.Dates()

In der überwiegenden Zahl der Fälle können Sie die Funktion List.Dates() ohne weitere Einschränkungen einsetzen. Die Syntax dieser Funktion ist folgendermaßen:
List.Dates(Startdatum, Anzahl Intervalle, Länge des Intervalls)

Beispiel: =List.Dates(#date(2018,1,1), 365, #duration(1,0,0,0))

Im Power Query-Editor stellt sich das dann zu Beginn so dar:

Direkt nach der Eingabe der Funktion in die Eingabe-Leise

Direkt nach der Eingabe der Funktion in die Eingabe-Leise

Wie für alle von mir interpretierten Power Query-Funktionen gilt der Hinweis, dass Sie nach Möglichkeit auch auf die Original-Dokumentation von Microsoft (in englischer Sprache) zugreifen sollten. Hier wäre es dieser Link:
msdn.microsoft.com/en-us/query-bi/m/list-dates

Die Argumente dieser Funktion näher betrachtet:

  • Startdatum ist bei einem Jahreskalender naturgemäß der 1. Januar des entsprechenden Jahres. Wollen Sie einen beliebigen Zeitraum (beispielsweise ein Fiskal- bzw. Geschäftsjahr) darstellen, dann ist dieses der erste Tag des darzustellenden Zeitraums. Das Startdatum muss im Format date (Datentyp: Datum) vorliegen.
  • Anzahl Intervalle ist ein numerischer Wert. Wenn sie die Tage eines Jahres darstellen wollen, dann wird dies in der Regel die Zahl 365 sein. Das entspricht der oben dargestellten Formel. Das Thema „Schaltjahr“ wird weiter unten diskutiert.
  • Das Argument Länge des Intervalls erwartet einen Wert in Form einer #duration()-Funktion. Die Argumente/Parameter hier sind: (Tag, Stunde, Minute, Sekunde) und diese sind natürlich vom Typ Zahl.

Erstellen der Abfrage „Jahreskalender“ auf dieser Basis

Idealerweise beginnen Sie mit einem leeren Arbeitsblatt. In Excel 2016 gehen Sie über Daten | Neue Abfrage | Aus anderen Quellen | Leere Abfrage und tragen in die obere Eingabezeile für das Jahr 2018 diese Formel mit dem führenden Gleichheitszeichen ein:
= List.Dates(#date(2018,1,1), 365, #duration(1,0,0,0))

Noch einmal zur Erinnerung: Es soll im Endeffekt eine Abfrage mit kalendarischen Daten erstellt werden, Beginn ist der 1. Januar 2018, die Liste umfasst 365 Zeilen und das Intervall, der zeitliche Sprung zum jeweils nächsten Element soll 1 Tag, 0 Stunden, 0 Minuten, 0 Sekunden sein. Und wenn Sie nach Eingabe des Textes, der Formel die Eingabetaste drücken oder irgendwo in den freien Bereich unterhalb der Eingabezeile klicken, wird eine Liste im Datum-Format mit allen 365 Tagen erstellt:

Die per Funktion erstelle Liste mit den Jahresdaten

Die per Funktion erstelle Liste mit den kalendarischen Jahresdaten

Bleibt eigentlich nach einer Kontrolle nur noch ein Klick auf das Symbol Zu Tabelle, damit eine Abfrage aus dieser Liste erstellt wird. Anschließend werden Sie gewiss noch die Überschrift anpassen, aber das ist ja Routine. Und ein Blick auf den Datentyp dieser Spalte wird vermutlich Beliebig zeigen. In diesem Fall bitte auf Datum ändern, damit anschließend in der Excel-Tabelle auch kalendarische Daten und keine seriellen Zahlen angezeigt werden. Nach Schließen & laden oder Schließen & laden in… haben Sie eine einspaltige Tabelle mit dem Datum jedes Tages des Jahres. In Excel können Sie dann natürlich noch beliebige Formatierungen des Datums vornehmen. Und natürlich kann diese Abfrage auch weiter in Power Query genutzt werden, denn der Aufwand für „nur“ einen Jahreskalender für Excel wäre gewiss zu hoch.

▲ nach oben …

Schaltjahr

Mit schöner Regelmäßigkeit (mit extrem wenigen Ausnahmen) sind alle Jahre, deren Jahreszahl sich „glatt“ durch 4 teilen lassen Schaltjahre. Da gibt es am 29. Februar einen zusätzlichen Tag im Monat und das Jahr hat dann entsprechend 366 Tage. Und wie können Sie das berücksichtigen? Sie erinnern sich gewiss, dass ich Sie habe kontrollieren lassen, ob der letzte Eintrag in der Abfrage auch der 31. Dezember bzw. der letzte Tag des Geschäftsjahres ist. Und hier setzt meine pragmatische Korrektur-Möglichkeit an: Wenn in einem Schaltjahr ein Tag zu wenig angezeigt wird, dann ändern Sie einfach die Formel:
= List.Dates(#date(2018,1,1), 366, #duration(1,0,0,0)) 
und das Jahr ist wieder „komplett“.

Und für „Spezis“ hier der Hinweis, dass es in der Formelsprache M auch noch eine Funktion gibt welche erkennt, ob es sich bei dem Jahr eines definierten Datums um ein Schaltjahr handelt oder nicht. Das Suchwort dazu: Date.IsLeapYear().

▲ nach oben …

Dieser Beitrag wurde unter Datum & Zeit, Power Query, PQ-Quickies abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.