Removing Text from a date column

[Anonymous]

I have a row that is meant to store the date when an item was launched. It is currently stored as a mix of just the date in format "1/10/19" or in the format "Launched-1/10/19" I want to remove everything but the date itself so the column can be changed into date format. I have tried using Text.Remove in the formula for a new column but I get an error that Text.Remove is not a function. Any help would be appreciated.

Thanks!

[mahoneypat · Accepted solution]

Try adding a custom column in the query editor with this formula.  It will only keep the numbers and / from your text.

= Text.Select([DateTextColumn], {"0".."9", "/"})

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[az38]

Hi @Anonymous 

Execute Split Column by Delimiter "-" in Power Query Editor mode (Transform ribbon)

[Anonymous]

I apologize I should have been more specific in my question, some are formatted "Launched-1/2/20" some are "Launched 1/2/20" some are "soft transition 1/2/20" and some are just "1/2/20". How would I get all of the dates in one column using split by delimiter.

[westwrightj]

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"