Excel-TippISO-Kalenderwoche in Power Query berechnen
Sie haben eine Reihe von Datumswerten in einer Spalte Ihrer Power-Query-Abfrage (Tabelle) und wollen nun für die entsprechenden Tage die Kalenderwoche ermitteln.
Hinweis: Wie Sie eine Liste mit durchgängigen Datumswerten in Power Query erstellen, erfahren Sie in diesem Excel-Tipp.
Datumswerte für die Datenanalyse aufbereiten
Wenn Sie für Ihre Datenauswertung und Datenaufbereitung zum jeweiligen Datumswert (Tag) weitere Angaben zum Datum benötigen, können Sie in Power Query diese Informationen in weiteren Spalten ergänzen.
Wählen Sie dazu im Menüband die Befehlsfolge Spalte hinzufügen > Datum > …
Sie sehen im folgenden Untermenü, welche Möglichkeiten Sie haben. Sie können beispielsweise eine Spalte ergänzen, die nur das Jahr des entsprechenden Tages enthält. Weitere Möglichkeiten sind Spalten mit
- Quartal
- Monat
- Woche
- Tag
In den jeweiligen Untermenüs können Sie zum Beispiel den Namen des Tages oder den Namen des Monats in einer neuen Spalte ausgeben.
Wenn Sie in Power Query weitere Spalten mit Angaben zum jeweiligen Tag erstellt haben, könnte die neue Tabelle in Excel so aussehen.
Diese Tabelle zeigt: Power Query gibt mit der Funktion Spalte hinzufügen > Datum > Woche > Woche des Jahres nicht die meist gewünschte und übliche ISO-Kalenderwoche an.
Der 01.01.2023 ist ein Sonntag, gehört also noch zur Woche mit den Tagen davor (KW 52). Die folgenden Tage ab dem 02.01.2023 sind nicht die KW 2, wie in der Tabelle angegeben, sondern die erste Kalenderwoche des Jahres 2023 (KW 1).
Hintergrund:
Die ISO-KW ergibt sich aus den Regelungen der ISO 8601. Dort ist festgelegt, dass der Montag der erste Tag der Woche ist und die erste Kalenderwoche eines Jahres die Woche mit dem ersten Januar-Donnerstag ist.
Im Beispiel oben ist der 05.01.2023 der erste Januar-Donnerstag; entsprechend gehört er zur ersten Kalenderwoche – und nicht zur zweiten.
ISO-Kalenderwoche in Power Query berechnen
Um die übliche ISO-Kalenderwoche zu ermitteln, ist eine umfangreichere Berechnung notwendig. Fügen Sie in Power Query eine benutzerdefinierte Spalte hinzu:
Registerkarte Spalte hinzufügen > benutzerdefinierte Spalte
Erfassen Sie dann folgende Formel:
= Number.IntegerDivide(Duration.Days([Datum] - Date.AddDays(#date(Date.Year(Date.AddDays([Datum], 3 - Date.DayOfWeek([Datum], Day.Monday))), 1, 1), 3 - Date.DayOfWeek(#date(Date.Year(Date.AddDays([Datum], 3 - Date.DayOfWeek([Datum], Day.Monday))), 1, 4), Day.Monday))), 7) + 1
Wie ist die Formel aufgebaut?
- Date.DayOfWeek([Datum], Day.Monday) ermittelt den Wochentag (Montag = 0, Sonntag = 6)
- Date.AddDays(…) verschiebt das Datum auf den Donnerstag derselben Woche (wichtig für ISO-Logik)
- Date.Year(…) bestimmt das Jahr des Donnerstags (entscheidend für KW-Zugehörigkeit)
- Date.AddDays(#date(…) findet den Donnerstag der ersten ISO-Woche des Jahres
- Duration.Days(…) berechnet die Differenz zwischen zwei Datumswerten
- Number.IntegerDivide(…) berechnet die Division durch 7 ohne Nachkommastellen
- + 1 lässt die Zählung bei 1 beginnen, statt bei 0
Dabei ist vorausgesetzt, dass sich der jeweilige Datumswert, zum Beispiel der 01.01.2023, in der Spalte Datum befindet.
Liste mit Datumswerten nach Excel übergeben
Abschließend übergeben Sie Ihre Liste wieder an Ihr Tabellenblatt in Excel, indem Sie im Menüband die Befehlsfolge Start > Schließen & laden wählen.
Sie haben nun eine Liste mit dem Datum und den entsprechenden weiteren Angaben zum Datumswert – insbesondere mit der richtigen ISO-Kalenderwoche.
Mit einer solchen Kalendertabelle können Sie zahlreiche Datenauswertungen anschaulich aufbereiten. Beispiele mit entsprechenden Excel-Vorlagen finden Sie hier:
