Extract DateTime from Text

[joshua1990]

Hi experts!

I have a column that contains the DateTime as a Text, includig the fileformat and the filename:

Column

Sales 13-05-2021 12-00-00

Sales 27-05-2021 18-55-00

Now I would like to extract the DateTime from this column.

How is this possible without 20 extra steps?

[ImkeF · Accepted solution]

Hi @joshua1990 ,
you can add a column with the following formula that will return the datetime-value from it:

DateTime.From(
    let
        splitColumn       = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Column]), 
        splitsplitColumn2 = Splitter.SplitTextByDelimiter("-", QuoteStyle.None)(splitColumn{2}?)
    in
        Text.Combine(
            {
                Text.Middle([Column], 12, 4), 
                Text.Middle([Column], 8, 4), 
                Text.Middle([Column], 6, 2), 
                " ", 
                Text.Combine(splitsplitColumn2, ":")
            }
        )
)