Kalendertabellen gehören in (fast) jede Auswertung. Aber was sind Kalendertabellen? Wozu und wie viele benötigt man? Wie geht man mit Urlaub und Betriebsferien um? Wie verhält sich Power BI ohne Kalendertabelle? Wir finden: genug Stoff für eine Serie. In diesem Teil: Wie ergänzen wir Feiertage mit Power Query?

Kalendertabellen

Im ersten Teil der Serie habe ich dir gezeigt, warum man Kalendertabellen verwendet, wie diese eigentlich definiert sind und wie viele man davon benötigt. Im zweiten Teil der Serie ging es darum, wie man Kalendertabellen erzeugt - natürlich gibt es mehrere Wege. In diesem Artikel beschäftigen wir uns mit Feiertagen. Und zwar, wie man mit Power Query Feiertage in der Kalendertabelle ergänzt.

Feiertage

Deutschland ist das Land der Feiertage. Nicht nur, dass wir viele davon haben - nein, wir haben in den verschiedenen Bundesländern auch verschiedene Feiertage. Für eine Datenauswertung ist das...sagen wir: anstrengend.

Bundeseinheitliche Feiertage sind noch recht einfach zu behandeln. Länderspezifische schon nicht mehr. Hier sei allerdings der Hinweis erlaubt, dass fachlich immer geklärt sein muss, wie mit solchen Feiertagen umgegangen werden soll. Sitzt beispielsweise das Callcenter für den Support in München und am 6.1. (hl. drei Könige) ruft jemand aus Hamburg an - ist der Feiertag in München dann relevant für eine Kennzahl Tickets/Tag? Sicherlich interessiert der Feiertag die Lohnbuchhaltung, da der Mitarbeiter in München vermutlich einen Feiertagszuschlag erhält. Im diesem Artikel belassen wir es bei bundeseinheitlichen Feiertagen.

Wozu brauche ich Feiertage in meiner Auswertung?

Möchte man berechnen, wie hoch das durchschnittliche Auftragsvolumen am Tag ist, benötigt man die effektiven Arbeitstage. Sagen wir Samstag und Sonntag kann es keinen Auftragseingang geben. An einem Feiertag ebenfalls nicht. Für Samstag und Sonntag legen wir in unserer Kalendertabelle eine Spalte an IstWochenende:

Wenn wir in der Kalendertabelle eine Spalte mit Feiertagen hätten, können wir eine weitere Spalte anlegen:

Das durchschnittliche Auftragsvolumen wäre dann:

Bundeseinheitliche Feiertage

Folgende einheitliche Feiertage kennen wir in Deutschland:

  • Neujahr (1.1.)
  • Karfreitag (6.1.)
  • Ostermontag
  • Christi Himmelfahrt
  • Pfingstmontag
  • Tag der Arbeit (1.5.)
  • Tag der Deutschen Einheit (3.10.)
  • erster Weihnachtstag (25.12.)
  • zweiter Weihnachtstag (26.12)

Es gibt nun verschiedene Möglichkeiten, das Datenmodell aufzubauen. Entweder erstellt man eine separate Feiertags-Tabelle mit zwei Spalten Datum und Feiertag und verknüpft die Tabelle mit der Kalendertabelle über das Datum oder man fügt der Kalendertabelle eine Spalte Feiertag hinzu, die mit dem entsprechenden Feiertag gefüllt oder Blank ist. Das ist wenig Geschmackssache. Rein aus Performance-Aspekten ist das bei diesen Datenmengen ohnehin nicht relevant. Ich versuche den Weg mit einer weiteren Spalte. Dafür generiere ich allerdings erst eine Feiertags-Tabelle und füge die Spalte anschließend an die Kalendertabelle.

Im letzten Artikel haben wir gezeigt, wie man Kalendertabellen erzeugen kann. Entweder liegt diese schon vor (darauf gehe ich nicht mehr ein), via Power Query oder via DAX. Wir benötigen für diesen Artikel die Power Query Variante.

Einschub: Ostern

Neujahr ist ein einfacher Feiertag. Neujahr ist einfach jedes Jahr am 1.1. Ostern, Karfreitag, Pfingsten, Christi Himmelfahrt sind etwas schwierigere Kandidaten. Dafür gibt es allerdings die Gaußsche Osterformel.

In Power Query können wir eine Funktion zur Berechnung des Osterdatums anlegen (Hinweis: Die Osterberechnung setzt zwei Konstanten N und M voraus. Diese ändern sich alle 100 Jahre. Mit der hier angegebenen Funktion kann Ostern von 2000-2099 berechnet werden):

Feiertagstabelle in Power Query anlegen

Dazu lege ich eine Funktion an, die zwei Übergabeparameter erhält: ein Startjahr und ein Endjahr für die ich die Feiertage erzeugt haben möchte. Die oben gezeigte Funktion zu Ermittlung von Ostern bette ich in die Abfrage ein. Funktionen müssen nicht in einer eigenen Abfrage definiert werden, sondern können Teil einer Abfrage sein. Damit sind sie von anderen Abfragen zwar nicht mehr aufrufbar - in unserem Fall wollen wir das ohnehin nicht. Außerdem lässt sich die Power Query Funktion später viel einfach in andere Auswertungen kopieren.

Eine weitere Funktion getFeiertage4Year ist ebenfalls eine lokal definierte Funktion. Diese Funktion gibt für ein übergebenes Jahr eine Tabelle mit den Feiertagen zurück. Mit zwei Spalten Datum und Feiertag.

Und dann wird alles zusammengesetzt. Mit List.Generate (diese Funktion haben wir in einem Artikel erläutert](/powerquery-funktionen-paginated-data)) erzeugen wir für jedes Jahr (fromYear bis toYear) mit der Funktion getFeiertage4Year alle Feiertage des Jahres und geben diese Tabelle zurück. Das Ergebnis von List.Generate ist also eine Liste mit Ergebnistabellen. Der Rest ist in Power Query einfach:

  • die Liste wird in eine Tabelle umgewandelt (Tabelle mit einer Spalte)
  • Power Query erkennt, dass in der einen Spalten wiederum Tabellen sind und bietet an, die Spalten zu expandieren
  • dann ändern wir noch den Datentyp der expandierten Spalten.

Die komplette Funktion sieht wie folgt aus:

Wenn alles korrekt ist, dann kannst du die Abfrage testen:

Power Query Funktionstest

Wenn du diese Funktion beispielsweise mit den Jahren 2010 bis 2020 ausführst, solltest du dieses Ergebnis in einer neuen Abfrage erhalten:

Power Query Ergebnis der Feiertagsfunktion

Left Join in Power Query

Wir verwenden jetzt die Kalendertabelle, die wir im letzten Beitrag mit Power Query erzeugt haben und nenne diese KalenderMitFeiertagen. Die Feiertagstabelle verknüpfe ich jetzt mit der Kalendertabelle. Dazu wähle ich links in den Abfragen die Kalendertabelle aus und in der Menüleiste im Reiter Home die Funktion Abfragen zusammenführen.

Power Query Abfragen zusammenführen

Es öffnet sich ein Dialog, in dem ich festlegen muss, welche andere Tabelle ich mit der Kalendertabelle zusammenführen möchte - und anhand welches Merkmals die Tabelle angefügt werden soll. Das Merkmal wird einfach markiert, in dem oben und unten das Feld Datum mit der Maus ausgewählt wird.

Power Query Dialog Abfragen zusammenführen

In der unteren Auswahl lege ich fest, wie die Verknüpfung durchgführt werden soll. Hier verwenden wir Linker äußerer Join. "Links" ist hier die obere Tabelle, "Rechts" die untere. Ein Left Join bedeutet, dass in jedem Fall alle Zeilen der linken Tabelle in das Ergebnis überführt werden. Alle Zeilen der "rechten" Tabelle, deren Merkmal (oder Merkmale - es dürfen auch mehrere sein) mit den Spalten der "linken" Tabelle übereinstimmen, werden an die Zeile als neue Spalte angehängt. Wenn die Merkmale nicht passen, dann erhält die Zeile der linken Tabelle einen leeren Wert. Ein Bild sagt mehr als tausend Worte:

Power Query Ergebnis der Zusammenführung

Die Kalendertabelle hat jetzt also eine neue Spalte Feiertage. Inhalt dieser Spalte ist wiederum eine Tabelle. Das fühlt sich ungewohnt an. Es muss aber so sein, da für eine Zeile in der Kalendertabelle theoretisch auch ZWEI oder mehr Zeilen in der Feiertagstabelle gefunden werden könnten (fachlich kann das natürlich nicht sein). Im nächsten Schritt "expandieren" wir die Spalte. Das bedeutet, dass alle Spalten angehängt werden. Hat die Tabelle in der neuen Spalte jetzt mehr als ein Ergebnis, wird die Zeile der Tabelle Kalender entsprechend oft vervielfältigt (das darf bei einer Kalendertabelle natürlich nicht sein und ist in unserem Fall ausgeschlossen). Ist die Tabelle leer, werden die Spalten ebenfalls leer gelassen.

Power Query Spalte expandieren

Beim Expandieren kann man wählen, ob man alle Spalten oder nur ausgewählte Spalten expandieren möchte (die anderen fallen einfach unter den Tisch). In unserem Fall interessiert nur die Spalte Feiertag. Das Datum ist ja bereits in der Kalendertabelle vorhanden.

Fertig! An den entsprechenden Feiertagen steht in der Spalte Feiertag jetzt die korrekte Bezeichnung drin, in den anderen Zeilen einfach null.

Geht das auch mit DAX?

Im letzten Beitrag gab es auch den Weg, eine Kalendertabelle via DAX anzulegen. Gibt es eine weitere Tabelle, eine Feiertagstabelle, können wir die in DAX erzeugte Kalendertabelle ebenfalls mit Feiertagen ergänzen. Ob die Feiertagstabelle aus Power Query kommt (man lässt den Left Join einfach weg) oder in DAX erstellt wurde, ist erstmal unerheblich.

Wie man die Feiertagstabelle in DAX mit der Kalendertabelle verknüpft - und natürlich, wie man eine Feiertagstabelle in DAX statt in Power Query anlegt - zeigen wir im nächsten Teil der Serie.