Extract multiple dates from a single free text value

[Esef]

Hi All,

I am looking for a more elegent way to extract dates, times and notes from a free text field. Essentially it is a ticket work notes history concated into a single value at the header level for a ticket.

This is an example of what a single value looks like.

*****Example Record*****

2022-03-16 11:04:51 - Joe Smith (Work notes)
Reached out to the user to clarify which document he was looking for and he informed us that he was able to receive the documents. Closing this out.

2022-02-20 11:04:51 - Joe Smith (Work notes)
Time to first contact: 04:24:02

***********

Currently, I do not have access to the source system tables and am making due with data dumps.

Ideally, I would love to parse out dates, times and notes to their own fields but will settle for just the dates.

Also, I could go with parsing out to the same row (makes down stream work easier) but if its easier parsing out to new rows would be ok.

The single work notes field can store a little as no work notes or multiple. I am not sure what the max number of notes in a single field.

My current approach is to split colunms to rows using a space delimiter. From their I convert the colunm to date. Anything that is not a date gets errors and I remove the errors. This leaves me with a ticket number and worknotedate table which I join to the original table. 

Any input would be appreciated. 

[Vijay_A_Verma]

For date, use following formula (logic is same as yours but wrapped in a formula). You will get a list. You can make a decision to extract to rows or in a column separated by say comma (The formula can be wrapped in Text.Combine as well)

= List.RemoveNulls(List.Transform(Text.Split([Data]," "),(i)=>try Date.From(i) otherwise null))

 For time, same line of logic

= List.RemoveNulls(List.Transform(Text.Split([Data]," "),(i)=>try Time.From(i) otherwise null))

For Note, what is the expected output from your given sample?