Forum Discussion

maijanen's avatar
maijanen
Frequent Visitor
6 years ago
Solved

Need help converting YEAR/WEEK_NUMBER (text format) into date format

I have data that has year and week number like 2020/02. Power BI detects it as text but not as date. How could I convert this into another column and so that it could detect it as a week number?   ...
Need Help
maijanen's avatar
maijanen
Frequent Visitor
6 years ago

Answer to Anonymous, ImkeF and dax at the same:

 

Thanks for your suggestions! I actually tried Anonymous 's idea already previosly but I would need that as a one column indicating the date. And yes it could be basically the first day of the week and I then I could just hide the day and month and show the week number maybe.. 

dax's avatar
dax
Community Support
6 years ago

Hi maijanen , 

If you want to get the first day of week, you could create a calendar table, then merge this with your table, you could try below M code

calendar table

let
    Source = List.Dates(#date(2020,1,1), 365 ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "y-w c", each Number.ToText(Date.Year(    [Column1]) )&"/"& Number.ToText(Date.WeekOfYear([Column1]))),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"y-w c"}, {{"mind", each List.Min([Column1]), type date}})
in
    #"Grouped Rows"

 merge table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3UorVgTINkdmmCLaRgVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"y-w" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"y-w", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"y-w"}, Query1, {"y-w c"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"mind"}, {"Query1.mind"})
in
    #"Expanded Query1"

Best Regards,
Zoe Zhi

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