Power Query transformation of unrecognizable date/time format in column with various input

Hi,

I am working on transforming imported data in Power Query and have a column in the table which contains various alphanumeric results. Some of these results are date/time info in the following text format: hh:mm:ss (dd-MMM-yyyy), e.g. 07:10:00 (01-JUL-2019).

I am looking for a way to transform these date/time values into a format which is recognized as date/time by Excel, preferably dd.mm.yyyy hh:mm:ss, e.g 01.07.2019 07:10:00, without affecting the other non-date/time data in the column.

Any suggestions?

Best regards,
Marbleking

Hi,

Please see attached example file.

Regards,
Marbleking

Try this:=IFERROR(DATEVALUE(MID(Table1_2[@Result],11,11))+TIMEVALUE(LEFT(Table1_2[@Result],9)),Table1_2[@Result])

Thanks a lot for these solutions. I'll be working on integrating them into the rest of my data. Thanks again.