Forum Discussion
help with week 53 for power query Date table
Hello,
I have the following M Code to create a Column "WeekOfYear". which so far works fine but is not integrating the calendar week 53 of 2020.
Can someone help to adjust this so it includes week 53 from 28.12.2020 to 03.01.2020 and then startst with week one starting from the 04th of january.
I
Table.AddColumn(
#"Geänderter Typ",
"WeekOfYear",
each
let
week = Text.From(Date.WeekOfYear([Date], Day.Monday)),
newYear = if Date.Year(Date.EndOfWeek([Date], Day.Monday)) <> Date.Year([Date]) then true else false,
addedLeadingZeroWeek = if Text.Length(week) = 1 then "0" & week else week
in
if newYear
then "WK " & "01/" & Text.From(Number.Mod([Year], 2000) + 1)
else
"WK " &
addedLeadingZeroWeek &
"/" & Text.From(Number.Mod([Year], 2000))
),
regards,
Dats
I found a fairly simple solution. I am conditionally replacing week 01/20 for every row who's StartOfWeek] is 28/12/20.
= Table.ReplaceValue(#"Reordered Columns",each [WeekOfYear],each if Date.Day([StartOfWeek])=28 and Date.Month([StartOfWeek])=12 and Date.Year([StartOfWeek])=2020 then "WK 53/20" else [WeekOfYear],Replacer.ReplaceText,{"WeekOfYear"})
and it works.. 😊
3 Replies
- themistoklis
Community Champion
Anonymous
If it helps you check the following link and see how it can be done using DAX and not Power Query
https://community.powerbi.com/t5/Desktop/Looking-for-real-year-week/m-p/1591530#M646306
thanks for the link. I actually know how to do it with dax. Or at least would have an idea....But as the report is quit sophisticated in terms of what depends on columns created in the date table I thought it must be less error prone and faster to do it by "just" adjusting the existing M code that actually creates the column in the first place.
I tried Creating a new variable e.g. check if current row value ist 28.12.2020 then wright week 53 but it doesn't seem to work.
Do you themistoklis or someone else have an idea of how to adjust the power query code?
I found a fairly simple solution. I am conditionally replacing week 01/20 for every row who's StartOfWeek] is 28/12/20.
= Table.ReplaceValue(#"Reordered Columns",each [WeekOfYear],each if Date.Day([StartOfWeek])=28 and Date.Month([StartOfWeek])=12 and Date.Year([StartOfWeek])=2020 then "WK 53/20" else [WeekOfYear],Replacer.ReplaceText,{"WeekOfYear"})
and it works.. 😊
