Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thanks for your help!
Solved! Go to Solution.
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-...
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 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.
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-...
This is awesome!
Hi @maijanen ,
what exactly do you want? A field simply returning the week number (20)? Then @Anonymous solution is fine.
Or do you want to generate a date field of a (or multiple) days from the 20th week?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF can probably help with a Power Query solution.
In DAX it would be below. What day of the week do your weeks start on?
Column =
VAR __Year = VALUE(LEFT([YearWeek],4))
VAR __Week = VALUE(RIGHT([YearWeek],2)) //Using VALUE only for its intended purpose of converting a text value to a number
VAR __Calendar = ADDCOLUMNS(CALENDAR(DATE(__Year,1,1),DATE(__Year,12,31)),"Week",WEEKNUM([Date]))
RETURN
MINX(FILTER(__Calendar,[Week]=__Week),[Date])
Hi Greg,
I've been searching for this for quite some time. Used your formula but with Weeknum([Date],21) and got isoWeek.
Thanks a lot!
Thanks Greg for your answer, I'll wait also for the power query solution since that would be ideal. My weeks start on Monday.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.