Reply
Frequent Visitor
Posts: 6
Registered: ‎03-15-2017
Accepted Solution

Calculated column grabbing date from string of text

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.

 

Complications

 - 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


Accepted Solutions
Frequent Visitor
Posts: 6
Registered: ‎03-15-2017

Re: Calculated column grabbing date from string of text

In case anyone stumbles on this in the future - 

 

I ended up having to edit the query as it pulled the data in to remove any entires in the column that did not have "/" - then I was able to grab the right digits and convert the column to date.

View solution in original post


All Replies
Super Contributor
Posts: 671
Registered: ‎07-22-2015

Re: Calculated column grabbing date from string of text

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.

 

Highlighted
Frequent Visitor
Posts: 6
Registered: ‎03-15-2017

Re: Calculated column grabbing date from string of text

I think this would work but my feed is a DirectQuery which doesn't permit ISERROR functions.

Frequent Visitor
Posts: 6
Registered: ‎03-15-2017

Re: Calculated column grabbing date from string of text

In case anyone stumbles on this in the future - 

 

I ended up having to edit the query as it pulled the data in to remove any entires in the column that did not have "/" - then I was able to grab the right digits and convert the column to date.