Office-Blog

Pivot-Auswertungen nach ISO-Kalenderwoche werden in unterschiedlichsten Bereichen benötigt, ob für Umsätze, Bestellungen oder Produktionszahlen.

In Pivot kann für Datumsspalten eine Gruppierung nach Jahr, Quartal, Monat und/oder Tag gewählt werden, aber Kalenderwochen-Gruppierungen gibt es nicht.

Bild 1: Produktionszahlen in Pivot nach Jahr und ISO-Kalenderjahr und -woche auswerten

Wer die Daten für die Pivot-Auswertung mit Power Query vorbereitet, freut sich über die vielfältigen Möglichkeiten und die große Zeitersparnis gegenüber aufwendigen Copy&Paste- und Berechnungsaktionen in Excel.

Allerdings steht Power Query in Bezug auf Datumsfunktionen Excel noch ein wenig nach. In Excel steht für die Berechnung der ISO-Kalenderwoche seit Version 2010 die Funktion KALENDERWOCHE mit dem Parameter 21 und seit Version 2013 die Funktion ISOKALENDERWOCHE zur Verfügung.

Power Query verfügt bisher über keine solche Funktion. Aber auch dafür gibt eine Lösung.

In Power Query zunächst das Jahr aus einer Datumsspalte ermitteln

Power Query verfügt über die Möglichkeit, das Jahr und die Woche des Jahres aus einer Datumsspalte zu ermitteln (Bild 2), nicht aber das ISO-Kalenderjahr und die ISO-Kalenderwoche.

Bild 2: In Power Query stehen die Optionen Jahr und Woche des Jahres zur Verfügung, nicht aber das ISO-Kalenderjahr und die ISO-Kalenderwoche

Das Jahr aus dem Fertigungsdatum auszulesen, lässt sich in Power Query wie folgt lösen (Bild 3):

1. Per Rechtsklick in der Spalte Fertigungsdatum den Befehl Spalte duplizieren wählen [1]
2. In der neu entstandenen duplizierten Spalte den Befehl
   Transformieren > Datum > Jahr > Jahr aufrufen [2]
3. Anschließend die Spalte in Jahr umbenennen

Bild 3: Aus der Spalte Fertigungsdatum wird das Jahr auslesen

In Power Query ISO-Kalenderjahr und  ISO-Kalenderwoche ermitteln

Eine Möglichkeit zum Ermitteln des ISO-Kalenderjahres und der ISO-Kalenderwoche stellt Power Query nicht zur Verfügung. Aber mit ein paar Zeilen zusätzlichem M-Code lässt sich dieses Problem schnell lösen.

Bild 4: Den vorhandenen M-Code um die Berechnung des ISO-Kalenderjahres und der ISO-Kalenderwoche erweitern

Dazu sind folgende Schritte auszuführen (Bild 4):

1. Über Ansicht > Erweiterter Editor das Editor-Fenster öffnen [1][2]
2. Den folgenden M-Code in den vorhandenen M-Code – beispielsweise vor der Anweisung in  –  einfügen [3]:

ISO_Jahr = Table.AddColumn(NAME_VORHERIGER_SCHRITT,"ISO_Jahr", each Date.Year(Date.AddDays([Fertigungsdatum],3-Date.DayOfWeek([Fertigungsdatum],1))), type number),

Hilfsdatum = Table.AddColumn(ISO_Jahr , "Hilfsdatum", each #date([ISO_Jahr],1,3)),

ISO_KW = Table.AddColumn(Hilfsdatum ,"ISO_KW", each Number.IntegerDivide(Duration.Days( [Fertigungsdatum]-[Hilfsdatum])+Date.DayOfWeek([Hilfsdatum],0)+6,7), type number ),

ISO_Jahr_KW = Table.AddColumn(ISO_KW, "ISO_Jahr_KW", each Text.Combine({Text.From([ISO_Jahr], "de-DE"), Text.From(Number.ToText([ISO_KW], "00"), "de-DE")}, "/"), type text),

HilfsdatumEntfernen = Table.RemoveColumns(ISO_Jahr_KW,{"Hilfsdatum"})

1. Zu beachten hierbei sind die im M-Code kursiv geschriebenen  Stellen:

Im Schritt ISO_Jahr muss das Argument NAME_VORHERIGER_SCHRITT durch den Namen des vorherigen Schrittes im M-Code ausgetauscht werden (Bild 5: gelbe Markierung).

Der Name Fertigungsdatum muss durch den Namen der auszuwertenden Datumsspalte ersetzt werden (Bild 5: grüne Markierung).

Die Zeile, die dem Schritt HilfsdatumEntfernen folgt, muss auf diesen Schritt verweisen (Bild 5: blaue Markierung).

1. Anschließend den Erweiterten Editor über die Schaltfläche Fertig verlassen.

So sieht das Ergebnis nach Einfügen und Anpassen des M-Codes aus:

Bild 5: Vollständiger M-Code mit integrierter Berechnung von Jahr, ISO-Kalenderjahr und -woche

Nun können die Daten als Verbindung geladen werden. Beim Einfügen der Pivot-Tabelle wird dann diese neu erstellte Verbindung ausgewählt und das Erstellen der Pivot-Auswertung nach Jahr, ISO-Kalenderjahr und -woche kann beginnen (Bild 6).

Bild 6: In Power Query werden alle benötigten Gruppierungsebenen und sonstigen Datenaufbereitungen für Pivot geschaffen