Convert isoYearIsoWeek (YYYYWW) into a date

[YaleSOM]

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

[Anonymous]

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!

[MarcelBeug · Accepted solution]

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