Forum Discussion
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 Date Status. If Due date is after today then status should be "In Progress", if Due Date is after 2 days then status should be "Due in 48 hours", if Due Date is TBD then Status should be "TBD"
For Example:
Due Date Due Date Status
10/31/2017 In Progress
09/13/2017 Complete
TBD TBD
10/20/2017 Due in 48 Hours
12/01/2017 In Progress
TBD TBD
The Due Date Column is of type TEXT and it is not getting converted to Date. Can i create a new column of type DATE where if the value of Due Date is Date, then I get the Date otherwise Blank for TBD Value.
Any help would be appreciated.
Regards,
Paridhi
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"))
You can also replace the "TBD" with a specific date, say "2099-01-01", then change the column type to Date in Query Editor.
3 Replies
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"))
You can also replace the "TBD" with a specific date, say "2099-01-01", then change the column type to Date in Query Editor.
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?
