Calculating average chat reply time: Table, grouping and subgrouping based on sequence in chat

[Anonymous]

Hi, community!

I have a few thousand CSV files containing chat conversations. My objective is to calculate the average time between answers from different users (User = the customer, Account = our staff).

Here is what the raw data looks like, for one csv file: 

My question is: how could I group the messages that are sent in a "sequence" (meaning sent by the same sender (user or account)) together? 

(My intent is that the sequences would need to have some kind of running number within a conversation, where the chats of a same sequence would have the same running number)

My next step would be to create a table that would group first by conversation, then by sender type, and then by "sequence". I would retrieve the min and max timestamp for each of these sequences. Then calculate the time difference between the timestamp of the last message of the previous sequence with the first timestamp of the first message of the next sequence. 

I'd be happy to get your input on whether the thinking process is correct and some leads for me to explore grouping messages in sequence (if that's the correct way to go).

Also, I'd like to give a big thanks to all the members here. All the information shared has been so useful to me in the past few months when I was trying to figure out ways of using power BI.

Thanks for the time and potential leads to resolve this issue.

Cheers

[Anonymous]

Excellent! Thanks. I actually used an index. It worked wonders. Thanks for your kind help!

[amitchandak]

@Anonymous , You need to use logic similar to Continuous streak

Continuous streak: https://youtu.be/GdMcwvdwr0o

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1290346