Forum Discussion
Need help converting YEAR/WEEK_NUMBER (text format) into date format
Thank you all for your help. I'm quite new to Power BI and I think I wasn't able to describe my problem properly and that's my fault. I got a link from a friend of mine to this blog post that helped in my problem: https://eriksvensen.wordpress.com/2019/11/26/powerquery-calculate-the-iso-date-from-year-and-a-week-number/
Hi maijanen ,
Did you want to get the first day of each week or other type? As I know, currently desktop could recognize date formats like "2020/10/12" or "01-jan-2020"... They are all date type, it might not recognize date type like yyyy/week, so I suggest you could submit this in power-bi-ideas
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.
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..
Hi maijanen,
If you need a week number column only you can delete the year column after split. Or if you need to have it in the date format you can use #date (2020,1,1) + #duration (weeknumber, 0, 0, 0) after extracting the week number.
It is possible to do it all as column.transform rather and adding and then deleting columns, but it is more cosmetics - the algorithm stays the same.
Kind regards,
JBThank you all for your help. I'm quite new to Power BI and I think I wasn't able to describe my problem properly and that's my fault. I got a link from a friend of mine to this blog post that helped in my problem: https://eriksvensen.wordpress.com/2019/11/26/powerquery-calculate-the-iso-date-from-year-and-a-week-number/
This is awesome!
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
