Forum Discussion
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 the isoYearIsoWeek date format into a year and week? Basically, there are two values - the first is the year followed by the week number (YYYYWW)
Here is what I have:
isoYearIsoWeek
201641
201642
201643
201644
Thank you,
Brian
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
2 Replies
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 RemovedColumnsHi, 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
AddedEndOfISOWeekI 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!
