• Zur Hauptnavigation springen
  • Zum Inhalt springen
  • Zur Seitenspalte springen
  • Zur Fußzeile springen

THE SELF-SERVICE-BI BLOG

Wir lieben Microsoft Power BI

  • Videokurse
    • Power Query: Dein umfassender Einstieg!
  • Live Online Power BI-Schulungen
  • Beratung
    • Was ich für Dich tun kann
    • Showcases
  • Kunden
  • BLOG
    • Business Topics
    • Technical Topics (english)
    • Tools
  • Podcast
  • Kontakt
  • Über

Business Topics, Tools / 6. Januar 2020

Automatisch alle deutschen Feiertage je Bundesland ermitteln

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.

let func = (Kalenderjahr as number) as table =>
let
//Liste aller Bundeländer für den Aufbau der URL bei arbeitstage.org
List_Bundeslaender = {
"baden-wuerttemberg",
"bayern", "berlin",
"brandenburg",
"bremen",
"hamburg",
"hessen",
"mecklenburg-vorpommern",
"niedersachsen",
"nordrhein-westfalen",
"rheinland-pfalz",
"saarland",
"sachsen",
"sachsen-anhalt",
"schleswig-holstein",
"thueringen"
},
ToTable =
Table.FromList(
List_Bundeslaender,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Umbenannte Spalten" =
Table.RenameColumns(
ToTable,
{
{
"Column1",
"BundeslaenderURL"
}
}
),
#"Geänderter Typ" =
Table.TransformColumnTypes(
#"Umbenannte Spalten",
{
{
"BundeslaenderURL",
type text
}
}
),
//Funktion für den Retrieve der Feiertage der Website
fnGetFeiertageJeBundesland =
(Bundesland as text, Jahr as number) =>
let
//Aufbau der parametrisierten URL
Quelle =
Web.Page(
Web.Contents("https://www.arbeitstage.org/" & Bundesland &"/feiertage-"& Text.From(Jahr) &"-"& Bundesland &"/")),
Data0 = Quelle{0}[Data],
#"Geänderter Typ" =
Table.TransformColumnTypes(
Data0,
{
{
"Feiertag",
type text
},
{
"Datum",
type date
},
{
"Wann",
type text
}
}
),
#"Entfernte Spalten" = Table.RemoveColumns(#"Geänderter Typ",{"Wann"}),
#"Hinzugefügte benutzerdefinierte Spalte" =
Table.AddColumn(
#"Entfernte Spalten",
"Bundesland",
each Text.Proper(Bundesland)
),
Output =
Table.ReplaceValue(
#"Hinzugefügte benutzerdefinierte Spalte",
"ue",
"ü",
Replacer.ReplaceText,{"Bundesland"}
)
in
Output,
GetFeiertage =
Table.AddColumn(
#"Geänderter Typ",
"Feiertage",
each fnGetFeiertageJeBundesland([BundeslaenderURL], Kalenderjahr)
),
#"Entfernte Spalten" =
Table.RemoveColumns(#"GetFeiertage",{"BundeslaenderURL"}),
#"Erweiterte Feiertage" =
Table.ExpandTableColumn(
#"Entfernte Spalten",
"Feiertage",
{
"Feiertag",
"Datum",
"Bundesland"
},
{
"Feiertag",
"Datum",
"Bundesland"
}
),
Output =
Table.TransformColumnTypes(
#"Erweiterte Feiertage",
{
{
"Datum",
type date
},
{
"Bundesland",
type text
},
{
"Feiertag",
type text
}
}
)
in
Output,
documentation =
[
Documentation.Name = "fnGetDeutscheFeiertageJeBundesland",
Documentation.Description =
"Liefert eine Tabelle mit den deutschen Feiertagen je Bundesland für das angegebene Jahr zurück ",
Documentation.Parameters = "Kalenderjahr vom Typ number",
Documentation.Category = " ",
Documentation.Author = " Lars Schreiber: www.ssbi-blog.de ",
Documentation.Examples =
{
[
Description = " " ,
Code = "fnGetDeutscheFeiertageJeBundesland(2020)",
Result = " "
]
}
]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
view raw fnGetDeutscheFeiertageJeBundesland hosted with ❤ by GitHub

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 Schreiber

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…

Kategorie: Business Topics, Tools Stichworte: Excel, Feiertage, Power BI Desktop, Power Query

Leser-Interaktionen

Kommentare

  1. Nelinax meint

    18. Oktober 2021 um 9:28 am

    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

      18. Oktober 2021 um 12:12 pm

      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

        18. Oktober 2021 um 12:26 pm

        Hallo Lars,

        ich nutze sie über Excel Version 2102 und bin deiner Anleitung mit dem Einfügen auch soweit gefolgt.

        Liebe Grüße

  2. Richard meint

    14. Januar 2020 um 12:44 pm

    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

      14. Januar 2020 um 3:26 pm

      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

        20. Januar 2020 um 3:07 pm

        Hallo Lars,

        vielen Dank dir für die schnelle Antwort. Hat perfekt funktioniert!

        MfG
        Richard

      • Richard meint

        20. Januar 2020 um 3:08 pm

        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

Seitenspalte

WEBSITE DURCHSUCHEN

Power Query: Dein umfassender Einstieg…

🎓 Starte jetzt mit Power Query!

160+ Videos zeigen dir, wie du Datenimport, Bereinigung & Berechnungen einmal baust und immer wieder nutzt

Jetzt Kurs starten 💪

LERNE MIT JEDEM ARTIKEL

Erhalte alle Beispieldateien zu meinen über 200 Artikeln gratis!

Footer

Kontakt

THE SELF-SERVICE-BI BLOG
Mail: lars@ssbi-blog.de

Rechtliches

  • Impressum
  • Datenschutz

Über THE SELF-SERVICE-BI BLOG

Ich bin ein freiberuflicher Power BI-Berater, -Entwickler und -Trainer und wurde von Microsoft mehrfach mit dem MVP Award ausgezeichnet. Ich arbeite mit einem kompetenten Netzwerk aus freiberuflichen und ambitionierten Kollegen zusammen. Erfahre hier mehr.

Social

Folge mir...

Copyright © 2025 · Digital Pro on Genesis Framework