Forum Discussion
Convert isoYearIsoWeek (YYYYWW) into a date
- 9 years ago
As a week has 7 days - and all ISO weeks have 7 days, also the weeks around New Year - the following query will take you step by step to the start and the end of the week.
This approach is based on the fact that January 4th is always in ISO week number 1.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Typed = Table.TransformColumnTypes(Source,{{"isoYearIsoWeek", Int64.Type}}), AddedJan_4th = Table.AddColumn(Typed, "Jan_4th", each #date(Number.IntegerDivide([isoYearIsoWeek],100),1,4), type date), AddedWeekday_Jan_4th = Table.AddColumn(AddedJan_4th, "Weekday_Jan_4th", each Date.DayOfWeek([Jan_4th],Day.Monday)), AddedStartOfWeek1 = Table.AddColumn(AddedWeekday_Jan_4th, "StartOfWeek1", each Date.AddDays([Jan_4th],-[Weekday_Jan_4th]), type date), AddedStartOfISOWeek = Table.AddColumn(AddedStartOfWeek1, "StartOfISOWeek", each Date.AddWeeks([StartOfWeek1],Number.Mod([isoYearIsoWeek],100)-1), type date), AddedEndOfISOWeek = Table.AddColumn(AddedStartOfISOWeek, "EndOfWeek", each Date.AddDays([StartOfISOWeek],6), type date), RemovedColumns = Table.RemoveColumns(AddedEndOfISOWeek,{"Jan_4th", "StartOfWeek1", "Weekday_Jan_4th"}) in RemovedColumns
Hi, I am am looking to convert the following WWYY format to a date. I stumbled upon this post and tried to utilize to code provided. But I am having trouble with the modification:
AddedJan_4th = Table.AddColumn(#"Renamed Columns2", "Jan_4th", each #date(Number.IntegerDivide([WeekYear],100),1,4), type date),
AddedWeekday_Jan_4th = Table.AddColumn(AddedJan_4th, "Weekday_Jan_4th", each Date.DayOfWeek([Jan_4th],Day.Monday)),
AddedStartOfWeek1 = Table.AddColumn(AddedWeekday_Jan_4th, "StartOfWeek1", each Date.AddDays([Jan_4th],-[Weekday_Jan_4th]), type date),
AddedStartOfISOWeek = Table.AddColumn(AddedStartOfWeek1, "StartOfISOWeek", each Date.AddWeeks([StartOfWeek1],Number.Mod([WeakYear],100)-1), type date),
AddedEndOfISOWeek = Table.AddColumn(AddedStartOfISOWeek, "EndOfWeek", each Date.AddDays([StartOfISOWeek],4), type date)
in
AddedEndOfISOWeek
I realized that the mod calclation drops the year value that I need when dividing by 100. Can you help with the modifcation of the code above? or with a different solution. Thanks in advance!