Removing Text from a date column

Hey Nickpin,

    This might be a bit of a pain, but you might have to do a few delimiter splits and a custom column or two to get your data just right from the power query side. This is the play data I'm starting with

So what I'm going to do is actually split this by the common delimiter for the date which is "/"

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Dates", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Dates.1", "Dates.2", "Dates.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Dates.1", type text}, {"Dates.2", Int64.Type}, {"Dates.3", Int64.Type}})
in
    #"Changed Type1"

That will give us this

Next we are going to create a custom column that pulls the numerical value out of the first field and add it with the other fields

= Table.AddColumn(#"Split Column by Delimiter", "Date", each Text.Select([Dates.1], {"0".."9"}) 
&
"/"
&
[Dates.2]
&
"/"
&
[Dates.3])

Which will give us this result

Go ahead and remove your old columns and change the data type to date and you should be set. 

Here are the steps from the advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kkszUvOSE3RNdQ30jcyUIrVQYgpIIkhMYPz00oUSooS84ozSzLz8+CqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dates = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Dates", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Dates.1", "Dates.2", "Dates.3"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Date", each Text.Select([Dates.1], {"0".."9"}) 
&
"/"
&
[Dates.2]
&
"/"
&
[Dates.3]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Dates.1", "Dates.2", "Dates.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
    #"Changed Type1"