Forum Discussion

YaleSOM's avatar
YaleSOM
Frequent Visitor
9 years ago
Solved

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 ...
  • MarcelBeug's avatar
    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