This post is also available in:
English
Bereits letzte Woche haben wir uns mit der Thematik der korrekten Berechnung der ISO Kalenderwoche in DAX beschäftigt. Da die Frage aufkam, wie man dies in Power Query lösen könnte, werde ich diese Woche darauf eingehen. Für die Problemstellung der Kalenderwochen möchte ich auf den Post von letzter Woche verweisen.
Berechnung der US-Kalenderwoche mit Date.WeekOfYear Funktion
Wie auch in DAX gibt es auch in Power Query bereits eine Funktion zur Berechnung der Kalenderwoche. Diese lautet Date.WeekOfYear und gibt das US-Format mit Wochenstart am Sonntag zurück. Mit optionalem Parameter wird die Kalenderwoche mit Wochenstart an einem Montag zurückgegeben. Die Syntax lautet folgendermaßen:
Date.WeekOfYear(#date(2021, 01, 01), Day.Monday)Berechnung mit Power Query Funktion
Leider gibt es im Power Query keinen versteckten Parameter wie im DAX, daher müssen wir uns die Kalenderwoche berechnen. Allerdings hat Robert K. Bell bereits die größte Vorarbeit geleistet. Basierend auf seinem Code habe ich die Funktion etwas für unsere Zwecke abgeändert. Den folgenden Code müsst ihr einfach im Power Query in eine neue Query einfügen:
/*
based on https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date
M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's
`weeknum(x, 21)` doesn't give the correct ISO Week-Year.
homepage: https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca
*/
let
getISO8601Week = (someDate as date) =>
let
getDayOfWeek = (d as date) =>
let
result = 1 + Date.DayOfWeek(d, Day.Monday)
in
result,
getNaiveWeek = (inDate as date) =>
let
// monday = 1, sunday = 7
weekday = getDayOfWeek(inDate),
weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
ordinal = Date.DayOfYear(inDate),
naiveWeek = Number.RoundDown(
(ordinal - weekday + 10) / 7
)
in
naiveWeek,
thisYear = Date.Year(someDate),
priorYear = thisYear - 1,
nwn = getNaiveWeek(someDate),
lastWeekOfPriorYear =
getNaiveWeek(#date(priorYear, 12, 28)),
// http://stackoverflow.com/a/34092382/2014893
lastWeekOfThisYear =
getNaiveWeek(#date(thisYear, 12, 28)),
weekYear =
if
nwn < 1
then
priorYear
else
if
nwn > lastWeekOfThisYear
then
thisYear + 1
else
thisYear,
weekNumber =
if
nwn < 1
then
lastWeekOfPriorYear
else
if
nwn > lastWeekOfThisYear
then
1
else
nwn,
week_dateString =
Text.PadStart(
Text.From(
Number.RoundDown(weekNumber)
),
2,
"0"
)
in
weekNumber
in
getISO8601WeekDanach müsst ihr die Funktion nur noch als neue Spalte mit dem Parameter Datum aufrufen und ihr habt die korrekte Berechnung der Kalenderwoche auch bereits im Power Query:
Die Beispiel-Datei kann hier heruntergeladen werden:
Hallo,
sofern ich das richtig verstehen sollen adurch
week_dateString =Text.PadStart(Text.From(Number.RoundDown(weekNumber)),2,"0")einstellige Kalenderwoche eine führende 0 erhalten und somit als 01 und nicht als 1 erscheinen.
Leider werden kalenerwochen kleiner 10 dennoch einstellig angezeigt.
Gruß Eisser
Hallo Eisser,
die zur Verfügung gestellte Funktion hat mehrere Möglichkeiten, wie du Werte zurücklieferst.
In dem oben gezeigten Beispiel wird die Wochennummer als Integer zurückgeliefert.
Das siehst du an der Zeile 81/82:
Wenn du die Wochennummer als String möchtest mit vorangestellter “0”, dann kannst du den Rückgabewert durch die Funktion “week_dateString” ersetzen. Heisst einfach die Zeile 82 anpassen in:
Das gibt dir dann die Woche als Text zurück mit vorangestellter “0”.
Falls du noch Fragen hast, dann lass es mich gerne wissen.
Viele Grüsse
Denis
Wird die Kalenderwoche z.B. in Deutschland und USA oder andere Länder korrekt angezeigt. Also auf das Gebietsschema angepasst?
Fall nein, wie könnte man das so ändern, dass dies möglich ist?
Liebe Grüße
Thomas
Hallo Thomas,
du kannst mit den oben genannten Funktionen definieren, wie die Kalenderwoche berechnet werden soll.
Aber welche Art der Berechnung du verwenden möchtest, muss weiterhin definiert werden.
Für eine automatisierte Berechnung müsstest du dir eine Lösung einfallen lassen, da gibt es leider nichts Vordefiniertes.
Man könnte beide Varianten vorberechnen und basierend auf dem eingeloggten User die Relationship ändern oder ähnliches. Aber das sind erstmal Gedanken, keine Ahnung wie die in der Praxis funktionieren.
Viele Grüsse
Denis