Forum Discussion

ddorhout's avatar
ddorhout
Frequent Visitor
2 years ago
Solved

YYWW text column to date in Power Query by adding custom column

I am struggling with transforming my YYWW to a date column. I want to have this done in PowerQuery already.

Note, I do not have dates before 2000. So 19xx are not present.

 

I have two snippets created until now. Unfortunately I can not make it to transform it in one Custom Column Formula.

 

# extracting the Year

Text.Combine({"20", Text.Start(Text.From([#"Go-live"], "en-US"), 2)})

 

# extracting the Week

Text.Middle(Text.From([#"Go-live"]),2)

 

How do I create a custom column including the transformation to date?

 

Need Help
Show and Tell
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi ddorhout - I have created some sample data column wth Go-live with YYWW format as below
    2301

    2215

    2132

    2038

    1947

     

    I am using en-us regional format, you can create a custom column , 

    please check the below add custom column code if it works.

     

    Advanced query editor code FYR:

    let

    Source = Table.FromRecords({
    [#"Go-live" = "2301"],
    [#"Go-live" = "2215"],
    [#"Go-live" = "2132"],
    [#"Go-live" = "2038"],
    [#"Go-live" = "1947"]
    }),

    // add column
    AddedCustom = Table.AddColumn(Source, "Date", each
    let
    YYWW = Text.From([#"Go-live"]),
    YearText = Text.Start(YYWW, 2),
    WeekText = Text.Middle(YYWW, 2),
    Year = Number.FromText("20" & YearText),
    Week = Number.FromText(WeekText),
    FirstDayOfYear = #date(Year, 1, 1),
    ResultDate = Date.AddWeeks(FirstDayOfYear, Week - 1)
    in
    ResultDate, type date
    )
    in
    AddedCustom

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi ddorhout - I have created some sample data column wth Go-live with YYWW format as below
    2301

    2215

    2132

    2038

    1947

     

    I am using en-us regional format, you can create a custom column , 

    please check the below add custom column code if it works.

     

    Advanced query editor code FYR:

    let

    Source = Table.FromRecords({
    [#"Go-live" = "2301"],
    [#"Go-live" = "2215"],
    [#"Go-live" = "2132"],
    [#"Go-live" = "2038"],
    [#"Go-live" = "1947"]
    }),

    // add column
    AddedCustom = Table.AddColumn(Source, "Date", each
    let
    YYWW = Text.From([#"Go-live"]),
    YearText = Text.Start(YYWW, 2),
    WeekText = Text.Middle(YYWW, 2),
    Year = Number.FromText("20" & YearText),
    Week = Number.FromText(WeekText),
    FirstDayOfYear = #date(Year, 1, 1),
    ResultDate = Date.AddWeeks(FirstDayOfYear, Week - 1)
    in
    ResultDate, type date
    )
    in
    AddedCustom

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

     

    • ddorhout's avatar
      ddorhout
      Frequent Visitor
      2 years ago

      Looks good!

       

      I just had to add one more step in order to have it at the start at the first day of the week:

       

      let
YYWW = Text.From([#"Go-live"]),
YearText = Text.Start(YYWW, 2),
WeekText = Text.Middle(YYWW, 2),
Year = Number.FromText("20" & YearText),
Week = Number.FromText(WeekText),
FirstDayOfYear = #date(Year, 1, 1),
ResultDate = Date.AddWeeks(FirstDayOfYear, Week-1),
StartOfWeek = Date.StartOfWeek(ResultDate)
in
StartOfWeek