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 cr...
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!!

     

     

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