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.
(2) Die Funktion List.Range()
Zugegeben, das ist in den meisten Fällen nur die zweite Wahl für diesen Zweck. Der hauptsächliche Grund, warum diese Möglichkeit so gut wie nie genutzt wird: Sie arbeiten nicht mit dem Datentyp Datum sondern mit dem Datentyp Ganze Zahl, also mit dem seriellen Wert des Datums. Wenn Sie mehr zum Thema serielle Zahl/Datum wissen möchten, schauen Sie gerne hier nach. Trotz dieses scheinbaren Handicaps hat diese Funktion durchaus ihre Berechtigung; angenommen Sie wollen beispielsweise jeweils 7 Tage vor und nach dem Beginn des Kalender- oder Geschäftsjahres in die zu erstellende Tabelle einfügen, dann kann diese Funktion ganz praktisch sein. Aber auch wenn Sie ‑aus welchen Gründen auch immer- in den Roh-Daten oder den Import-Daten im Editor schon die seriellen Zahlen statt eines „typischen” Datums stehen haben.
Sagt Ihnen die Zahl 43101 etwas? Sie werden sich nach diesem Vorgeplänkel vielleicht, ja sogar wahrscheinlich denken können, dass es sich um die serielle Zahl eines Datums handelt. Und so ist es auch. Dieser Wert entspricht dem 1. Januar 2018. Ich stelle Ihnen hier ganz einfach einmal die List.Range() Formel vor, welche Sie für einen „normalen” Jahreskalender per Power Query nutzen würden:
List.Range({43101..43101 + 364}, 0)
… und sie werden gewiss erst einmal etwas irritiert sein. Die Syntax, die Schreibweise ist doch etwas anders, als typisch für eine Excel-Anwendung. Ich zitiere hier erst einmal aus der (englischen) Onlinehilfe zu dieser Funktion:
List.Range(list as list, offset as number, optional count as number) as list
Wie bereits erwähnt, gewöhnungsbedürftig … 😉 Der Funktionsname List.Range gefolgt von der öffnenden runden Klammer sollte vom Prinzip her klar sein. Das nächste Argument ist list as list, also die Liste im Format einer PQ-Liste.
Aha … 🙄
Dazu ist es wichtig zu wissen, dass eine Power Query-Liste in den Formeln und Funktionen der Sprache M stets von geschweiften Klammern eingefasst eingegeben wird. Und wenn Sie jetzt die aufgezeigte Beispiel-Formel als Vergleich heranziehen werden Sie feststellen, dass dort als Liste steht: {43101..43101+364} was übersetzt heißt: Die Liste startet mit dem Wert 43101 und endet mit dem Wert 43101 +364. Die beiden Punkte zwischen dem Anfangs-und Endwert werden genauso eingegeben und sind das verbindende Element. Und die geschweiften Klammern geben Sie über AltGr7 und AltGr0 ein. Als zweites Argument nach dem Komma folgt in der Formel eine 0, was in der Syntax als Offset beschrieben ist. Das bedeutet, dass am Start 0 (keine) Werte übersprungen werden. Und vielleicht denken Sie nun, dass ich in der Formel beim zweiten Teil der Liste einen Schreibfehler drin habe, nämlich statt 365 den Wert 364 geschrieben habe.
Nein, das ist kein Tippfehler! Für reine Excel-Anwender ist das gewiss irritierend. Wenn Sie aber schon mit Programmiersprachen außerhalb VBA gearbeitet haben, dann wird Ihnen geläufig sein, dass ein Index, die Zählung stets mit 0 beginnt. Und so ist es auch hier.
Nach diesem ganzen Vorgeplänkel nun „Ran an den Speck”! Ich gehe einfach einmal davon aus, dass Sie ausschließlich diese Kalender-Tabelle erstellen wollen und zeige Ihnen darum den direkten Weg auf. Prinzipiell könnten Sie diese angewendete Formel auch innerhalb einer bereits existierenden Abfrage verwenden.
- Beginnen sie mit einer neuen, leeren Mappe.
- Register Daten | Neue Abfrage | Aus anderen Quellen | Leere Abfrage.
- Geben Sie direkt in die Eingabezeile die bereits oben aufgeführte Formel exakt so ein, die geschweiften Klammern {} geben Sie ebenfalls direkt über die Tastatur ein:
= List.Range({43101..43101+364},0)
(es ist also keine Array-Formel wie in Excel). - Bestätigen Sie mit Enter.
Das Ganze stellt sich nun so dar:
Auf den ersten Blick ein etwas irritierendes Ergebnis …
Die Überschrift dieser Spalte sagt es schon: Es handelt sich um eine Liste im Sinne des Power Query, nicht um eine Tabelle. Wenn Sie in diesem Stadium nämlich versuchen, den Datentyp in Datum zu ändern, wird Ihnen das nicht gelingen. Darum werden sie im nächsten Schritt auf das Symbol Zu Tabelle Klicken und die Vorgaben im Dialog so wie sie sind per OK bestätigen.
Sinnvollerweise wurde die Überschrift der Spalte auch geändert, denn es ist jetzt ja die erste Spalte einer ganz normalen Abfrage-Tabelle. Ich ändere die Überschrift jetzt schon im Vorwege auf den eigentlichen Inhalt: Datum. Anschließend in der Gruppe Transformieren ein Klick auf Datentyp: Beliebig und sie wählen sinnvollerweise Datum als Typ aus. Bingo! Das sieht schon einmal sehr gut aus. Aber nach dem Motto „Vertrauen ist gut, Kontrolle ist besser” überprüfen Sie als erstes die Statuszeile (unten) und sie erkennen, dass es 365 Zeilen sind; und obwohl sich ein Computer so gut wie nie irrt, scrollen Sie nach unten und überprüfen Sie, ob der letzte Wert auch tatsächlich der 31. Dezember 2018 ist. Übrigens: Meistens funktioniert auch StrgEnde, um zur letzten Zeile der Abfrage zu gelangen.
Ich hatte ja bereits erwähnt, dass dank der rein numerischen Angaben auch ein erweiterter Kalender dargestellt werden kann. Angenommen sie wollen den Zeitbereich 5 Tage vor Beginn und 14 Tage nach dem Jahresende bzw. dem Ende des Fiskaljahres in der Tabelle darstellen, dann sähe die Formel so aus:
= List.Range({43101-5..43101+364+14},0)
und mit einem Schlag wäre das gewünschte Ergebnis als Liste und nach der Konvertierung auch als Abfrage vorhanden. Dieses ist eine der Stärken dieser Funktion.
Das 3. List.Range – Argument nutzen
Sie erinnern sich vielleicht, dass in der Syntax auch ein optionales drittes Argument gegeben ist. Das haben sie bislang nicht genutzt, schließlich habe ich es Ihnen ja auch „unterschlagen”. 😳 Dabei kann das sehr nützlich sein, die Formel etwas flexibler, vielleicht auch transparenter zu gestalten. Ich bleibe einfach einmal bei dem Jahreskalender 2018 und würde unter Verwendung des 3. Parameters folgende Formel nutzen:
= List.Range({43101..99999}, 0, 365)
Auf jeden Fall ist diese Formel „schlanker”. Das 2. Argument innerhalb der geschweiften Klammern (der Wert nach den beiden Punkten) ist derart hoch angesetzt, dass der darzustellende Zeitraum auf jeden Fall reichen wird. Offset bleibt bei 0, denn es soll ja keinen Versatz in Sachen Beginn geben. Und das 3. Argument sollte selbsterklärend sein; die Mehrzahl der Jahre hat ja 365 Tage. Und natürlich können Sie hier auch bei den numerischen Tages-Werten händisch Subtraktionen (beim Start-Tag) oder Additionen (bei der Anzahl der Tage, nach dem Offset) eingeben. – Ich denke, dass sie sich mit dieser Form eher anfreunden werden als mit der im vorherigen Beitrag zu diesem Thema dargestellten Möglichkeit. 💡
Wochentag in eine extra Spalte
Beim lesen der Überschrift werden Sie vielleicht gedacht haben: „In Excel kein Problem! Bezug auf die Spalte mit dem Datum und in einem gewünschten Zahlenformat darstellen.” Stimmt. Aber Sie sind hier ja nur indirekt in Excel zugange, es geht um Power Query.
Egal, in welcher Abfrage Sie sich gerade befinden, Sie enthält ja mindestens 365 unterschiedliche kalendarische Daten in einer listenartigen Aufstellung bzw. Tabelle. Markieren Sie die Spalte Datum, auf welches sich ja die neue Spalte mit den Wochentag beziehen soll. Register Spalte hinzufügen Gruppe Aus Datum & Uhrzeit und ein Klick auf das Symbol Datum. Im aufgeklappten Menü Tag | Name des Tags. Und wie gewohnt erscheint blitzschnell das Ergebnis, der (ausgeschriebene) Wochentag des entsprechenden Datums.
Möchten Sie nur die Kurzform des Wochentages darstellen, dann bieten sich grundsätzlich zwei Möglichkeiten an. Die erste und wohl einfachste wäre: Markieren Sie die Überschrift Name des Tages, Register Start, Spalte teilen | Nach Anzahl von Zeichnen und im Kontextmenü bei Anzahl von Zeichen eine 2 einzugeben und Einmal, Soweit links wie möglich auszuwählen. Die erste Spalte enthält dann den gewünschten Wert, und die zweite Spalte werden Sie löschen. Sinnvollerweise werden Sie die Überschrift auch noch anpassen, indem sie die letzten beiden Zeichen (.1) löschen.
Der zweite gleichermaßen sinnvolle Weg geht nicht über Spalte teilen sondern Sie werden eine kleine Formel schreiben. Verwechseln Sie zum Register Spalte hinzufügen, Benutzerdefinierte Spalte und geben bei Neuer Spaltenname beispielsweise Wochentag ein. In dem Feld Benutzerdefinierte Spaltenformel: geben Sie in exakt dieser Groß- Kleinschreibung diese Formel ein:
= Date.ToText(/Datum/, "ddd"))
… und nach einem OK erkennen Sie die Kurzform des Wochentages in der Spalte. Bei der Gelegenheit ist es durchaus erfreulich zu erkennen, dass automatisch die Windows- Landes Einstellungen verwendet worden sind. – Das funktioniert mit den Monatsnamen übrigens genau so, nur werden Sie dort dann "MMM" in Großbuchstaben eingeben.
Schaltjahr
Hier gestaltet sich die Ein- bzw. Umstellung auf ein Schaltjahr gewiss genau so einfach wie im ersten Beitrag dieses Themas. Ihre Formel lautet dann eben:
= List.Range({43101..99999}, 0, 366)
… und im Folgejahr dann natürlich wieder mit 1 Tag weniger.