Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago
Solved

Extract DateTime from Text

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?

Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    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, ":")
            }
        )
)

     

1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    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, ":")
            }
        )
)