Wer im Internet nach einer Berechnung der ISO-Kalenderwoche in Power Query sucht, findet häufig eine Benutzerdefinierte Funktion, die diesen Job erledigt. Doch leider unterbricht die Benutzerdefinierte Funktion das Query Folding bei SQL-Datenquellen, womit meistens der Ladeprozess verlangsamt wird. In diesen Beitrag stelle ich eine Lösung bereit, die auch das Query Folding für SQL-Datenquellen unterstützt.
Die entsprechende Berechnung basiert auf dem Blogbeitrag „A Simple Formula to Calculate the ISO Week Number“ von Jeff Moden, die ich auf SQL Server Query Folding fähige Transformationen übersetzt habe.
Um die Anwendungsschritte nicht unnötig aufzublasen, habe ich alle Transformationen in einen Anwendungsschritt zusammengefasst.
Um den Anwendungsschritt in die eigene Abfrage zu übernehmen, müssen die Zeilen 5 – 23 kopiert und über den erweiterten Editor in die eigene Abfrage eingefügt werden. In Zeile 11 muss der Anwendungsschrittname „dbo_FactSales“ und der Datumspaltenname „Datekey“ auf den entsprechenden Anwendungsschrittnamen und Datumspaltennamen der Abfrage angepasst werden. Dabei muss die Datumspalte vom Datentyp Datum oder Datum/Uhrzeit sein.
let
Quelle = Sql.Databases(".", [CreateNavigationProperties = false]),
ContosoRetailDW_SQLBI = Quelle{[Name = "ContosoRetailDW_SQLBI"]}[Data],
dbo_FactSales = ContosoRetailDW_SQLBI{[Schema = "dbo", Item = "FactSales"]}[Data],
IsoWeek = Table.TransformColumns(
Table.TransformColumns(
Table.TransformColumnTypes(
Table.TransformColumns(
Table.TransformColumns(
Table.TransformColumnTypes(
Table.DuplicateColumn(dbo_FactSales, "DateKey", "IsoWeek"),
{{"IsoWeek", Int64.Type}}
),
{{"IsoWeek", each _ + 53688, type number}}
),
{{"IsoWeek", each _ - Number.Mod(_, 7) - 53685, type number}}
),
{{"IsoWeek", type date}}
),
{{"IsoWeek", Date.DayOfYear, Int64.Type}}
),
{{"IsoWeek", each Number.RoundDown(_ + 6 / 7), Int64.Type}}
)
in
IsoWeek
Performancevergleich
Bei einem ersten Performancevergleich der Berechnung der ISO-Kalenderwoche mittels einer Power Query Funktion (Rot) im Vergleich zu der Query Folding Lösung (Grün), war die Query Folding Lösung bei ca. 3,4 Millionen Zeilen (FactSales der ContosoRetailDW) ca. 1 Minute schneller.
Quellen:
https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
https://whatthefact.bi/de/power-bi/power-query-m/iso-8601-kalenderwoche-in-power-query-berechnen/
