Forum Discussion
YaleSOM
9 years agoFrequent Visitor
Convert isoYearIsoWeek (YYYYWW) into a date
I'm looking to convert isoYearIsoWeek into a readable date format in PowerBI. When I try to change the data type from text to date, I get an error. Can anyone provide some help on how to convert ...
- 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
MarcelBeug
9 years agoCommunity Champion
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