Office-Blog

Daten in Power Query für Pivot-Auswertungen mit Jahr, ISO-Jahr und -Kalenderwoche vorbereiten

10.04.2018   Hildegard Hügemann

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.

Pivot-Auswertung mit ISO-Kalenderjahr und -woche

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.

Aus einer Datumsspalte das Jahr auslesen

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
Jahr aus Datumsspalte auslesen

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.

Berechnung des ISO-Kalenderjahres und der ISO-Kalenderwoche

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:

Vollständiger M-Code

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).

Die Daten für Pivot in Power Query aufbereiten

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

 

 

Über den Autor

Hildegard Hügemann

- Dipl.-Informatikerin
- Zertifizierte Office-Trainerin mit den Schwerpunkten Excel, Access, Word
- Entwicklerin von Excel-Tools zur Projekt-, Produktions- und Verkaufssteuerung
- Coach bei der Datenaufbereitung mit Access, Power Query und Power Pivot
- Entwicklerin von Access-Datenbanken für Mittelständler und Behörden
- Bloggerin zu Excel und Access auf www.huegemann-informatik.de
- Autorin für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

Das könnte Sie auch interessieren

1 Kommentar bisher

Frank Arendt-TheilenPosted on  5:20 pm - Apr 11, 2018

Hallo Hildegard,
das Power Query noch nicht die ISO-Kalenderwoche kennt, hat mich ebenfalls gestört. Deshalb vielen Dank für Deinen Beitrag zu diesem Thema.
Anmerkungen:
Aus einem Datum lässt sich das Jahr in einer neuen Spalte direkt erstellen > Spalte hinzufügen > Datum > Jahr > Jahr. Diese Spalte wird auch gleich richtig benannt.

Das Einfügen der vier Mashup-Codezeilen und die Code-Anpassungen sind recht aufwendig. Daher die Überlegung die Berechnung der Kalenderwoche in einer eigenen Power Query-Funktion auszulagern. Diese Funktion kann komplett ohne Änderungen oder Anpassungen benutzt werden. Vorausgesetzt wird dann nur noch eine Spalte mit gültigen Datumswerten. Sie kann als Textdatei in einem beliebigen Ordner abgespeichert und immer wieder eingesetzt werden.

1. Erstelle eine neue leere Abfrage und kopiere den folgenden M-Code hinein:

////// Funktionsbeginn
//Formel in Excel: =KÜRZEN((Datum-WOCHENTAG(Datum;2)+11-(„1/“&JAHR(Datum+4-WOCHENTAG(Datum;2))))/7)
let
fxKalenderwoche = (Datum as date) =>
let
Wochentag = Date.DayOfWeek(Datum) + 1,
Teil1 = Number.From(Datum) – Wochentag + 11,
Teil2 = Number.From(#date(Date.Year(Date.From(Number.From(Datum) + 4 – Wochentag)),1,1)),
Teil3 = (Teil1 – Teil2) / 7,
Kuerzen = Teil3 – Number.Mod(Teil3, 1)
in
Kuerzen
in
fxKalenderwoche
////// Funktionsende

2. Benenne die Abfrage um in, z.B.: fxKalenderwoche
3. Bei der Abfrage, in der die Kalenderwoche enthalten sein soll Spalte hinzufügen > Benutzerdefinierte Spalte > Spaltenname: KW > Formel: fxKalenderwoche([DATUMSSPALTE]) > Ok.

Für das ISO-Jahr ließe sich die folgende Funktion nutzen:

//////////////////////////////////////////////////////////////////////////////////////////////
// Die Funktion fxISOJahr ermittelt zu einem gegebenen Datum die dazugehörige ISO-Jahreszahl,
// z.B. gehört der 31.12.2018 in das ISO-Jahr 2019.
// Die Funktion erwartet einen gültigen Datumswert.

(Datum as date) => Date.Year(Date.AddDays(Datum, 3 – Date.DayOfWeek(Datum, 1)))

////// Funktionsende

Mit freundlichem Gruß
Frank Arendt-Theilen

Schreibe eine Antwort