Power Query Group by

[ClaudiaSa25]

Dear All,

I am exporting some data from a Group Mail Box with power query. I would like to match the Attribute.InternetMessageID with AttributeInreply to in order to obtain for example when a mail was received and when we replied to it. I tried with Group by in power query but this doesn't seem right. 

Is there any way to achieve this? 

Many thanks

[#2]

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 Admin