Forum Discussion

erezb's avatar
erezb
Frequent Visitor
9 years ago
Solved

Converting from Text to Date

Hi all,   How can I extract out from a date field the month and year arguments and create a new coloum with the format:   mm\yyyyy   I have an original date column ("minnipuk") holding the dat...
Need Help
  • parry2k's avatar
    parry2k
    9 years ago

    Do you have dedicated continuous calendar table in your data model which is best practice for time intelligence based reporting. If you dont have this table, you can create one, here is the link

     

    https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

     

    Although see below on how to extract Month and Year and you need to sort this column by other column to get month order correctly.

     

    Column 2 = FORMAT('Table'[Date], "MM/YY")
    Column = FORMAT('Table'[Date], "MMM/YY")
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    You say you also want a column to be "what I need is - 9-2015 or even better sep-2015."

     

    Add a column to your DATE table

     

    Month = FORMAT('Dates'[Date],"mmm-YYYY")

     

    To get this sorted correctly also add the following column

     

    MonthID = INT(format('Dates'[date],"YYYYMM")

    Once you have these two columns,  Set your Month column to be sorted by your MonthID column.

     

  • erezb's avatar
    erezb
    9 years ago

    Thanks, parry2k.

     

    I used your  second soultion and it works fine.

     

    I'm not quiet sure I have understood your first solution. I did try to  creatie this and got a message "The syntax for ';' is incorrect" I'm  not sure why it occurs.

     

    However, suppose I have managed to create the table and also have a 2'nd table with my impoted data that contains a date field,

    Do you mean there should be some kind of join between them by the date fields (formated : dd/mm/yyyy) and than get the corresponding date format I'm intersted in ("mm/yyyy") from the date table.

     

    Thanks again

     

dchapman's avatar
dchapman
New Member
8 years ago

I need to convert 0h 00m to hours.  I haven't been able to find a solution and saw this post about converting text to date.  The text is different in each row.  Any suggestions would be helpful.  

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi dchapman

 

This might work

 

    HOUR(
        TIMEVALUE(
            SUBSTITUTE(
               SUBSTITUTE("0h 00m","h",":") ,
                "m","")
            )   
        )
  • dchapman's avatar
    dchapman
    New Member
    8 years ago

    Hi Phil, 

     

    Thank you for the formula.  I tried adding a custom column using that formula but I may have done it incorrectly.  Where do I put in a reference to the original column data?  Or should I be doing something different? 

     

    This is what I have in my function bar: 

    = Table.AddColumn(#"Filtered Rows4", "Custom", each HOUR( TIMEVALUE( SUBSTITUTE( SUBSTITUTE("0h 00m","h",":") , "m","") ) ))

     

    I'm really new at using Power Query and not finding a lot of helpful information from Microsoft on how to use this product. 

     

    Thanks,

    Derik 

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      This code will work as a calculated column in Power BI Desktop (not Power Query)

       

       

      New Column = 
          HOUR(
        TIMEVALUE(
            SUBSTITUTE(
               SUBSTITUTE([your column here],"h",":") ,
                "m","")
            )   
        )