Convert text field that may have a date to a date field

[Anonymous]

I have data that is text that I want to convert to a date field. The text usually has a date that can be written in different formats. E.g., 3/4/20 could be written as 3/4, 03/04, 03/4/2020, etc. There is sometimes random text before and after the text. I want to convert this text field so that it is displayed just as a date.

The below screen shot shows some examples. The custom column (last column) is the final 'correct' value...but I manually typed these as an example.  The columns that are yellow are columns I added.

I think the final step is to evaluate all three yellow columns for dates and pick the first column that is a "valid" date.

Any ideas?

[v-yiruan-msft]

Hi @Anonymous ,

If your problem has been resolved, could you please mark it as Answered? So it will help others if they face the similar problem with you. Thank you.

Best Regards

Rena

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create one measure as below:

1. Get the number of start position of text “/”using SEARCH function
2. Get the date using MID function

Date = var firstSlashL= search("/",FIRSTNONBLANK('ConvertText'[Clean Date],0)) 
var isNumberP1=MID(FIRSTNONBLANK('ConvertText'[Clean Date],0),firstSlashL-1,1)
var secondSlashL=IFERROR(SEARCH("@",SUBSTITUTE(FIRSTNONBLANK('ConvertText'[Clean Date],0),"/","@",2),1),0)
var fdate=if(ISNUMBER(IFERROR(value(isNumberP1),isNumberP1)),MID(FIRSTNONBLANK('ConvertText'[Clean Date],0),firstSlashL-1,5),MID(FIRSTNONBLANK('ConvertText'[Clean Date],0),secondSlashL-1,5)) 
return trim(fdate)

The above formula is written based on the sample data you provided , and may not be fully applicable to your scenario. Later you may need to adjust this according to your situation.

Best Regards

Rena

[Greg_Deckler]

Well, use SEARCH to search for "/". Subtract 2 from that value and grab to the LEN of the string. Then you should be able to convert it to a Date.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490