Das neue Jahr 2020 hat begonnen und in vielen Unternehmen werden jetzt die Routinen zu Jahresbeginn stattfinden, d. h.: Alte Dateien aus dem Vorjahr auf das aktuelle Jahr umstellen. Als ich noch ein angestellter Controller war, bedeutete dies für mich häufig auch, meine Dateien mit den Feiertagen je Bundesland auf den aktuellen Stand zu bringen. Diese Arbeit möchte ich Dir für dieses (und alle kommenden) Jahr(e) abnehmen. Dafür habe ich eine Funktion in M (Power Query) geschrieben, die für Dich automatisch alle deutschen Feiertage je Bundesland ermitteln wird.
Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!
Die Datenquelle
Um die Feiertage je Bundesland zu ermitteln, bediene ich mich der Dienste der Website https://www.arbeitstage.org/. Neben Feiertagen liefert diese Website auch Arbeitstage und Ferientage je Bundesland. Die Feiertage werden seit dem Jahr 2000 dokumentiert. Ein sehr nützlicher Dienst, den ich prima mit Power Query automatisiert auslesen kann.
Die Funktion
Um die Web-Abfrage nicht jedes Mal von Hand erstellen zu müssen, habe ich eine Funktion geschrieben, der lediglich das gewünschte Kalenderjahr als Parameter übergeben werden muss. Das Ergebnis ist dann eine Tabelle mit allen deutschen Bundesländern und den dazugehörigen Feiertagen. Ich bin mir sehr wohl bewußt darüber, dass es innerhalb Deutschlands Feiertage gibt, die sich nicht nur je Bundesland unterscheiden, sondern auch innerhalb eines Bundeslandes je Region verschieden sein können. Dem wird hier nicht Rechnung getragen.
Falls Du wissen möchtest, wie Du meine M-Funktionen in Deinen Projekten wiederverwenden kannst, schau bitte hier nach. Sofern Du diese Lösung für Qlik benötigst, findest Du eine entsprechende Lösung bei meinem Kollegen Mathias Schreiber (nein, wir sind nicht verwandt :-))
Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de
Viele Grüße aus Hamburg,
Lars
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
Nelinax meint
Hallo Lars,
ich bekomme folgende Fehlermeldung obwohl ich die URL im Browser ohne Probleme öffnen kann.
DataFormat.Error: Der Server oder Proxy wurde nicht gefunden.
Details:
https://www.arbeitstage.org/baden-wuerttemberg/feiertage-2021-baden-wuerttemberg/
Lars Schreiber meint
Hi Nelinax,
ich kann den Fehler nicht reproduzieren. Nutzt Du meine Funktion innerhalb von Excel oder Power BI Desktop?
Viele Grüße,
Lars
Nelinax meint
Hallo Lars,
ich nutze sie über Excel Version 2102 und bin deiner Anleitung mit dem Einfügen auch soweit gefolgt.
Liebe Grüße
Richard meint
Hallo,
vielen Dank für den Beitrag, sehr hilfreich und genau was ich gesucht habe.
Mich würde jetzt noch interessieren, wie man in diese Funktion noch einbauen kann, dass man direkt mehrere Jahre hinzufügen kann.
D.h. man würde bspw. einen Zeitraum eingeben wie: 2000 – 2030 .
Ich weiß, ich könnte die Funktion nun 30 mal ausführen und die einzelnen Tabellen aneinander anhängen und hätte das Ergebnis.
Um jedoch auch zu erlernen, wie man richtig Funktionen schreibt, würde mich das interessieren.
Lars Schreiber meint
Hallo Richard,
der wohl einfachste Weg ist, diese Funktion zeilenweise in einer Tabelle mit einer Jahresspalte aufzurufen… Ich füge Dir mal ein entsprechendes Skript mit ein:
let
//Manuelles Erstellen einer Liste der gewünschten Jahre
Quelle = {2000..2020},
//Konvertieren der Liste in eien Tabelle
#“In Tabelle konvertiert“ = Table.FromList(Quelle, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Umbenannte Spalten“ = Table.RenameColumns(#“In Tabelle konvertiert“,{{„Column1“, „Jahre“}}),
#“Geänderter Typ“ = Table.TransformColumnTypes(#“Umbenannte Spalten“,{{„Jahre“, Int64.Type}}),
//Aufruf der custom function innerhalb einer neuen benutzerdefinierten Spalte
#“Hinzugefügte benutzerdefinierte Spalte“ = Table.AddColumn(#“Geänderter Typ“, „Feiertage“, each fnGetDeutscheFeiertageJeBundesland([Jahre])),
#“Entfernte Spalten“ = Table.RemoveColumns(#“Hinzugefügte benutzerdefinierte Spalte“,{„Jahre“}),
#“Erweiterte Feiertage“ = Table.ExpandTableColumn(#“Entfernte Spalten“, „Feiertage“, {„Feiertag“, „Datum“, „Bundesland“}, {„Feiertag“, „Datum“, „Bundesland“})
in
#“Erweiterte Feiertage“
Hierfür musst Du die von mir geschriebene Funktion „fnGetDeutscheFeiertageJeBundesland“ mit exakt diesem Namen in Deinem Power Query speichern.
Ich hoffe das hilft Dir weiter 🙂
Viele Grüße aus Hamburg,
Lars
Richard meint
Hallo Lars,
vielen Dank dir für die schnelle Antwort. Hat perfekt funktioniert!
MfG
Richard
Richard meint
Hallo Lars,
vielen Dank dir für die schnelle Antwort. Hat perfekt funktioniert!
(Komischerweise hat das Antworten am 15. Jan auf deiner Seite nicht funktioniert, deswegen erst jetzt das Danke)
MfG
Richard