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?

 

Thanks for your help!

 

Need Help

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      Brunoandrade
      New Member
      3 years ago

      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's avatar
      maijanen
      Frequent Visitor
      6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi maijanen

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

    Regards,
    JB
  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    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?

     

  • dax's avatar
    dax
    Community Support
    6 years ago

    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'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.. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        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