Convert Text String to Date

[dls0406]

I have a text string containing either 7 or 8 (MDDYYYY or MMDDYYYY) digits which I want to convert to a date value. I can use the Text.PadStart to convert all of the text string to MMDDYYYY, but am still having a lot of trouble using the Date.FromText function to get this in date value. I am VERY NEW to power query so please describe in baby steps.

Here is my setup now, where [Original date] column contains text strings of 7 or 8 character. With the following custom column formula, I am getting an error in each cell
= Date.FromText( Text.PadStart([Original Date],8,"0"), "MMDDYYYY")

Am I missing something from a syntax point of view? I have seen instructions where I have to break the text string into 3 columns and then recombine, but I'd rather get it all done in one step and I can't imagine that is impossible.

 

[sandy666]

did you try Date - Parse ?

 

[dls0406]

did you try Date - Parse ?

yes. When I do that it also results in an error

 

[sandy666]

post a link to shared file (not a picture) with sample of the data

 

[dls0406]

I cannot post the data as it is confidential - not mine to share. However I would assume there must be an easy formula that someone with powerquery expertise can share, to convert an 8-digit text string into a date. The text string, for example, is 03232005 and I want powerquery to recognise it as a date (March 23, 2005, but any format is fine)

 

[sandy666]

I don't want confidential data but representative generic sample (more then one record)

 

[dls0406]

Okay....I cannot give data file, but this is a sample of what the data looks like that I would like to convert to something that is recognized as a date by powerquery.

Original Date (Formatted as Text "ABC")

3232005

6122018

12282009

10012006

 

[sandy666]

maybe add prefix 0, cut string to 8 characters from right then Text.Insert(Text.Insert([Original Date],2,"/"),5,"/") and Parse

 

[dls0406]

Yes that works without even the parse step! Thank you for your help!

 

[sandy666]

I can't check because I am not using us style date format
Have a nice day