Year&Week

[catzw6699]

 Hi !
Simple question I guess but cannot solve it. I have a column with Year & Week in text format e.g. 202408 (Year 2024 and Weekno 08). How do I convert this into a datecolumn?

[v-junyant-msft · Accepted solution]

Hi @catzw6699 ,

The date data type in Power BI does not support year + week for now.
And in addition to using Power Query, you can also use DAX:
Use these DAXs to create calculated columns:

Year = LEFT([YearWeek], 4)

WeekNumber = RIGHT([YearWeek], 2)

DateColumn = DATE([Year], 1, 1) + ([WeekNumber] - 1) * 7

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chakravarthy]

Hi @catzw6699 - I have split the year and week column. I have changed the Year column to Date and Week Column to Text in Power Query.. Below is the code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjGwUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Text.Start([Column1],4)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Year", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Week", each Text.End([Column1],Text.Length([Column1])-4)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Week", type text}})
in
#"Changed Type2"