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

     

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