PQ - How to extract first and last time from text in single cell

[sandy666]

How to extract first and last time from text in single cell


ChatConversationMinMax

03/11/2020 10:45:20 PM - CHATBOT
Hi there! My name is CHATBOT. I'm a bot, here to help you.
Can I ask for your name?
03/11/2020 10:45:24 PM - USER
Hi, can you please help me?
03/11/2020 10:45:26 PM - CHATBOT
Please type out your full first and last name without the use of initials.
Can I ask for your name?
03/11/2020 10:45:40 PM - USER
John Doe
03/11/2020 10:45:41 PM - CHATBOT
Thanks, John Doe!
Can I also get your email in case we get disconnected?
03/11/2020 10:46:24 PM - USER
test@test.com
03/11/2020 10:46:26 PM - CHATBOT
Wonderful! Let's get started.
Do you want to schedule time with someone who can tell you more about our services?
Yes
No
03/11/2020 10:46:35 PM - USER
Yes
03/11/2020 10:46:37 PM - CHATBOT
Excellent, let's schedule a meeting!
I just need to collect a little information about your company first.
What's the name of your company?
03/11/2020 10:46:45 PM - USER
ABC
03/11/2020 10:46:46 PM - CHATBOT
Got it!
How many employees do you have at your company?
03/11/2020 10:46:56 PM - USER
100+
03/11/2020 10:46:58 PM - CHATBOT
Thanks for sharing!
What aspects of our services prompted your interest in us?
22:45:20
22:46:58


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SplitLF = Table.ExpandListColumn(Table.TransformColumns(Sour ce, {{"ChatConversation", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ChatConversation"),
ETBD = Table.TransformColumns(SplitLF, {{"ChatConversation", each Text.BeforeDelimiter(_, " -"), type text}}),
Time = Table.AddColumn(ETBD, "Time", each try Time.From(DateTimeZone.From([ChatConversation])) otherwise null),
Filter = Table.SelectRows(Time, each ([Time] <> null)),
Timestamp = Table.AddColumn(Filter, "Timestamp", each "Timestamp"),
Group = Table.Group(Timestamp, {"Timestamp"}, {{"Min", each List.Min([Time]), type time}, {"Max", each List.Max([Time]), type time}}),
RC = Table.RemoveColumns(Group,{"Timestamp"})
in
RC