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.
(3) Jahreskalender, ausschließlich Arbeitstage
In manchen Fällen wird ein Jahreskalender gebraucht, wo ausschließlich die Arbeitstage gelistet sind. Prinzipiell wäre es durchaus machbar, das direkt und ohne Umwege mit Power Query zu erledigen. Aber da es sich hier um ein Quicky handelt, beschränke ich mich auf die „schlanke” Form. Das meiste aber geschieht natürlich in Power Query.
Sie beginnen damit, auf eine beliebige Weise einen Jahreskalender mit allen Tagen des Jahres zu erstellen. Vorzugsweise naturgemäß in Power Query. 😎 Anschließend werden Sie eine neue Spalte generieren und dort den Tag der Woche berechnen lassen. Natürlich gibt es auch hier verschiedene Möglichkeiten, ich stelle Ihnen die meiner Meinung nach am leichtesten nachzuvollziehende Methode vor.
Spalte hinzufügen, Gruppe Aus Datum und Uhrzeit | Tag | Tag der Woche. Automatisch wird eine Spalte mit den Werten 0 bis 6 erstellt. Da sie wissen (oder nachgeschaut haben), dass der 1. Januar 2018 ein Montag war und dieser in der produzierten Liste mit der Zahl 0 dargestellt wird ist auch klar, dass die Werte 5 und 6 der Samstag und der Sonntag sind, und aus diesem Grunde ausgeblendet bzw. aus der Abfrage gelöscht werden sollen. Erweitern Sie nun die Spalte Tag der Woche und filtern Sie diese so, dass das Häkchen bei 5 und 6 entfernt wird. In der Spalte Datum bleiben dadurch nur die Arbeitstage über.
Betriebsurlaub
Eigentlich war’s das. 😉 In manchen Unternehmen gibt es aber auch einen Betriebsurlaub, wo grundsätzlich nicht gearbeitet wird. Auch solch ein Zeitraum lässt sich relativ problemlos mit Power Query erstellen bzw. verarbeiten. Dazu erstellen Sie eine neue Abfrage. Den Weg dazu kennen Sie ja bereits, idealerweise gehen Sie über Start, Neue Quelle | Andere Quellen | Leere Abfrage. Als Test-Zeitraum für den Betriebsurlaub habe ich den 30. Juli 2018 bis zum 26. August 2018 festgelegt. Geben Sie in die Funktions-Textzeile diese Formel ein:
= List.Dates(#date(2018,7,30), Number.From(#date(2018,8,26)-#date(2018,7,30))+1,#duration(1,0,0,0))
Wie üblich eine kurze Kontrolle, ob nach einem Return/Enter die erstellte Liste auch den korrekten Datumsbereich umfasst. Bei mir stimmt alles, darum werden Sie die Liste auch in eine Tabelle konvertieren, indem Sie auf die Schaltfläche Zu Tabelle Klicken.
Damit sie den Überblick behalten, sollten Sie den Namen der Abfrage entweder im linken oder den rechten Seitenfenster so anpassen, dass der Inhalt deutlich wird. Da bietet sich Betriebsurlaub an. Bei der Gelegenheit können Sie auch die zuerst erstellten Abfrage eine entsprechenden Namen geben, beispielsweise Arbeitstage.
Nun müssen sie ihrer Abfrage Arbeitstage irgendwie beibringen, dass auch noch die in der Abfrage Betriebsurlaub aufgeführten Kalendertage entfernt werden. Dazu wechseln Sie erst einmal in die Abfrage Arbeitstage. Die Spalte Tag der Woche ist jetzt nicht mehr relevant, löschen Sie diese einfach. Wechseln Sie erforderlichenfalls zum Register Start und Klicken und dort auf die Schaltfläche Kombinieren. Im Pulldown-Menü wählen Sie den oberen Punkt Abfragen zusammenführen und im Dialog werden sie im mittleren, einzeiligen Eingabefeld die Abfrage Betriebsurlaub wählen. Klicken Sie anschließend in den beiden großen Feldern jeweils in ein Datum, damit in beiden Bereichen die gesamte Spalte markiert ist.
Das Ziel ist nun ja, aus der Tabelle Arbeitstage nur jene Zeilen zu übernehmen, die nicht in der Abfrage Betriebsurlaub aufgeführt sind. Dazu werden sie im Bereich Join-Art den Punkt Linker Anti-Join (Zeilen nur ein erster) auswählen. Nach einem OK wird in der Abfrage Arbeitstage eine neue Spalte mit dem Namen Betriebsurlaub erstellt. Sie stellen fest: Durchgängig ist der Inhalt jeder einzelnen Zelle das Wort Table.
Um es kurz zu machen: Die Abfrage ist jetzt korrekt gefiltert und sie werden bei einer Kontrolle feststellen, dass die Tage des Betriebsurlaubs nicht mehr in der Auflistung enthalten sind. Löschen Sie auch die Spalte Betriebsurlaub und sie haben Ihr Ziel erreicht.
Feiertage
Es wird ihn vielleicht schon unter den Nägeln gebrannt haben, das Thema „Feiertage”. Und genau dieses ist auch der Punkt, welchen ich hier im Quicky aus dem nur-Power Query Bereich heraus genommen habe. Es ist zwar durchaus möglich, eine Liste oder Abfrage der festen und beweglichen Feiertage innerhalb des Power Query zu erstellen aber hier gehe ich den Weg, das in Plain Excel zu generieren. Wie sie eine solche Aufstellung der Feiertage in Excel generieren können, lesen Sie beispielsweise hier nach.
Falls die in Excel erstellte Auflistung der Feiertage noch nicht als Formatierte Tabelle vorliegt, sollten Sie die Daten als Tabelle formatieren. Diese Aufstellung des Feiertage importieren Sie nun als weitere Abfrage in ihr Power Query-Projekt. Achten Sie darauf, Dass die importierten Daten anschließend im Datenformat Datum vorliegen. Erforderlichenfalls werden Sie den Datentyp anpassen.
Im nächsten Schritt werden sie exakt so vorgehen, wie bereits beim Betriebsurlaub geschehen. Sie geben der Abfrage einen sinnvollen Namen: Feiertage und gehen dann den Weg über das Zusammenführen der Abfrage. Damit ist das Ziel erreicht, im Jahreskalender stehen nur noch Tage, an denen auch gearbeitet wird.
Die Abfragen mit den Feiertagen und dem Betriebsurlaub lasse ich nicht als Excel-Tabelle anzeigen, darum werde ich diese via Schließen & laden in… als Nur Verbindung erstellen speichern, nur die eigentliche Datum-Abfrage werde ich naturgemäß „ganz normal” über Schließen & laden in… und dann Tabelle | Bestehendes Arbeitsblatt | Zelle A1 speichern und anzeigen lassen.