Remove timestamp in chat transcript

Hi Anonymous ,
Based on your description, it seems you've made good progress with extracting certain elements using `Text.BetweenDelimiters`. To address the issue of multiple timestamps caused by the customer pressing enter multiple times, we can follow a few steps to clean up the data.
1.If the messages are split across multiple rows, you may want to combine them into a single text block for each conversation. You can use the `Group By` feature in Power Query to group messages by conversation ID (or a similar identifier), and then use the `Text.Combine` function to concatenate the messages into a single text field.
2.To remove timestamps, you can use the `Text.Select` function to keep only the characters that are not part of the timestamp pattern. For example, if your timestamps are always in the format `[HH:MM AM/PM]`, you can create a custom column that excludes these patterns. Alternatively, if the timestamp format is not consistent, you might need to use a combination of `Text.Start`, `Text.End`, `Text.Middle`, `Text.Length`, and `Text.PositionOf` functions to dynamically locate and remove the timestamps.
Here is the sample M code you can try 

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    GroupedMessages = Table.Group(Source, {"ConversationID"}, {"CombinedMessage", each Text.Combine([MessageColumn], " "), type text}),
    RemovedTimestamps = Table.TransformColumns(GroupedMessages, {"CombinedMessage", each Text.Select(_, {"a".."z", "A".."Z", "0".."9", " ", ".", ",", "!", "?"}), type text}),
    CleanedText = Table.TransformColumns(RemovedTimestamps, {"CombinedMessage", each Text.Trim(Text.Clean(_)), type text})
in
    CleanedText

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly