Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Text won't turn to Date

Hi, 

 

I have tried converting my text to date by changing the Data Type formatting in the Column tools but just receive an error. See pic below. 

I also tried to create a column with the following code and received an error.

Date = FORMAT(LEFT(BOOJ30MF[MMDD],2) & RIGHT(BOOJ30MF[MMDD],2) & LEFT(BOOJ30MF[BOOJRSDT],4),"MM/DD/YYYY")

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I figured it out. I had to create an If statement to get rid of the 0s. Then I created another column with the following equation: 

     

    Column 2 = Date(LEFT(BOOJ30MF[Requested date],4),MID(BOOJ30MF[Requested date],5,2) ,RIGHT(BOOJ30MF[Requested date],2))
     

20 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous 

     

    That type of information will not give you a date format you need to do the following:

     

    Date = LEFT(BOOJ30MF[MMDD],2) &"/&RIGHT(BOOJ30MF[MMDD],2) & "/" & LEFT(BOOJ30MF[BOOJRSDT],4)

     

    And then format the column as a date column.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      @ MFelix - I am getting the error "Cannot convert value "0/0/0" of type Text to type Date

      • Anand24's avatar
        Anand24
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous ,

         

        Power BI doesn't recognise MMDDYYYY directly as a Date format. That's why you are not able to directly convert into date field.

        Same is the issue when you use FORMAT function

         

        Create a calculated column by splitting and joining existing column to have data in the format of a date(MM/DD/YYYY or DD/MM/YYYY or any other Date format).

        Probably this new column will be of text data type but in Power BI recognizable date format, hence you will just need to convert this date data type.

         

        Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

         
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

    Use

    FORMAT(table[col],"MMDDYYYY")

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      @ pranit828 - This still gives me an error 😕

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        HI Anonymous .

         

        Try this formula:

         

        Date = Date(LEFT(BOOJ30MF[BOOJRSDT],4),LEFT(BOOJ30MF[MMDD],2) ,RIGHT(BOOJ30MF[MMDD],2))

         

  • mhossain's avatar
    mhossain
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Anonymous 

     

    If you want to take DAX approach: You need to use Date function and mentione year, month day.

    DATE(

    RIGHT([DateText],4),

    LEFT([BOOJ30MF[Date],2),

    MID([DateTest],3,2))

     

    You can do in powerquery too, using Date.FromText