03-15-2017 01:33 PM
I have a column with a text string as a value. At the end of the text string there is a date (MM/DD/YYYY)....sometimes. Each cell in the column is formatted like:
"String of text DD/MM/YYYY" or just simply "String of text"
End goal: Use a "New Measure" to create a "Calculated Column" that will return the date string if there is a date and nothing if there is no date because I need to format the column as a date to get my visuals and slicers to render properly.
- I need to use a measure because it is a direct query data type
- Not every string in the original column has a date string at the end
What I've tried:
Using the RIGHT function to grab the last 10 characters in the string. While this will get the right information, I can't format the column as a date or everything breaks (Error code says "Conversion failed when converting date and/or time from character string).
**I know this issue is because some of the fields don't have the date string at the end so Power BI won't convert the values to date if there are text characters. If there is a way to tell it to nullify any non-date values, this would potentially work?
I also tried to use an IF statement but I can't figure out what I would use to make it work.
Thank you for any guidance
03-15-2017 02:06 PM
You can use IsError function:
myDate = if(IsError(myTable[Text]), BLANK(), DATEVALUE(myTable[Text]))
myTable[Text] in this case will right function to grab 10 characters.