Trouble converting Date from Text

[jowildes]

Hello,

I'm hoping someone here has experienced this.  I have two columns that I have split, the original data is text, and the values are text representation of dates in the mmddyyyy format. Screenshot of the data is further below. I want to be able to convert the text value to date type.  When I select Date/Time, or Date from the Data Type drop down I get an error from Power Query.  

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
07182022 <-this is how my date is formatted

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
07182022

If I attempt to use the Date.FromText , I get an error that the format includes a TIME VALUE ...I don't really care about the time, I just need it to register as a date when in Excel.

I can get around this by loading the data and not formatting it as Date, but then using the Text-to-Column feature and changing the data to date format MDY in the Text-to-Column wizard.  it works flawlessly there, but I don't want to have the user go through this every time they refresh the data.  

Thoughts on how to do this?

[PhilipTreacy · Accepted solution]

Hi @jowildes 

You'll need to parse the text into parts so PQ knows what to do with it.  Without any delimiter between the mm dd and yyyy parts, it doesn't know what is what.

Add a Custom Column with this code

Date.From( Text.Middle([StartDate] , 0 , 2) & "-" & Text.Middle([StartDate] , 2 , 2) & "-" & Text.Middle([StartDate] , 4 , 4) , "en-US" )

Download example PBIX file

Regards

Phil