Forum Discussion

Paridhisharma's avatar
Paridhisharma
Frequent Visitor
8 years ago
Solved

columns with mixed data types

HI BI Folks,   I have a column of data type TEXT and it has both Dates and Texts:   Due Date 10/31/2017 09/13/2017 TBD 10/20/2017 12/01/2017 TBD   I want to create a new column for Due Da...
Need Help
  • fhill's avatar
    fhill
    8 years ago

    Create a 'Check Column' with 1900/01/01 for TBD.  Once created, now you can format this column as DATE format.

     

    CheckColumn = IF(Table1[Due Date] = "TBD",DATE(1900,01,01),DATEVALUE(Table1[Due Date]))

     

    Use this 2nd column to do the date checks and include logic to pull out the 1900/01/01.  ** Assuming you don't have any dates before 1899.

     

    Text = IF(Table1[CheckColumn] > TODAY(), "In Progress", IF(Table1[CheckColumn] = Date(1900,01,01),"TBD","Complete"))

     

  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    Paridhisharma

    You can also replace the "TBD" with a specific date, say "2099-01-01", then change the column type to Date in Query Editor.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
8 years ago

Paridhisharma

You can also replace the "TBD" with a specific date, say "2099-01-01", then change the column type to Date in Query Editor.

  • Paridhisharma's avatar
    Paridhisharma
    Frequent Visitor
    8 years ago

    HI Eric,

     

    Thankyou for your response. 

     

    But what if we have more text values coming in that column apart from TBD? Like Pending etc. 

     

    ex:

     

    Due Date

    1/1/2017

    TBD

    1/2/2017

    Pending

    1/3/2017

     

    As i am not sure of the different values we are going to get in that column, i want all the text values to be replaced by date 01/01/2099. is that possible?