Power Query Group by

Matching Attribute.InternetMessageID with AttributeInreplyTo in Power Query to track email reply details is definitely achievable.

Here are a few approaches you can try:

1. Advanced Custom Column:

Create a custom column using the following formula: ReplyInfo = List.Zip({[MessageID], [#Received]}, List.Select([InReplyTo], x => Text.Split(x, ";")[0]))

This formula creates a list of pairs for each row, where the first element is the MessageID and the second is the Received time extracted from the InReplyTo address (assuming it contains the original message's timestamp).

2. Inner Join:

Create a duplicate of your main table. In the duplicate table, rename the Attribute.InternetMessageID column to ReplyToID. Perform an inner join between the original and duplicate tables on the Attribute.InReplyTo and ReplyToID columns, respectively.

This will create a new table with rows for both the original and reply emails, allowing you to compare timestamps.

3. Group by with Aggregation: Group your data by Attribute.InternetMessageID. Within the group, use a minimum aggregation on the Received column to get the original email arrival time. Calculate the maximum aggregation on the Received column to get the reply time (assuming replies arrive after the original message). This approach provides aggregated statistics for each message thread.

Thanks,

MS http://mahaofficer.in