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

[maijanen]

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!

[dax]

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.

[maijanen]

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]

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.

[Anonymous]

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,
JB

[maijanen · Accepted 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-...

[Guido_Beulen]

This is awesome! 

[ImkeF]

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?

[Anonymous]

Hi @maijanen

Would this be Split column by delimiter ("/")? Then format to whole number...

Regards,
JB

[Greg_Deckler]

@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])

[Brunoandrade]

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!

[maijanen]

Thanks Greg for your answer, I'll wait also for the power query solution since that would be ideal. My weeks start on Monday.