Hello @Anonymous
check out this solution. Applies Table.Group and analysis the create grouped table. 2 indicators are caclulated. Duration between Received and Min Sent mail. About your second request I'm not sure. Currently is shows true if in a conversation was find a Sent Item-mail. Here the M-code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZHRCoIwFIZfZXgd6aYWdBUEQVeBXWoXw451dHPi1Ojt2yooUqKiYFffgf3f+U8cO0mygbIhqwZkkjgjJ4LZQslKcDQ0UzXRUHeYghlR6tKpyzzmEerNwmAYcS4LbPUhz6G2Txhmf+TlaS45inGqpEWtbpSE+s62I2sTAaSAHewIGiVycXolNLmlh3ehJzQo1EsfkLwKfVUP7ddDf1LPVzasb8P+dqx1lhkD0qDEcq/fvpIo8pzrvR2yIAg/PsxSCaGObfV2oNm50AjDC4eB7z/kbc8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Folder Path" = _t, Subject = _t, #"date time sent " = _t, #"date time received " = _t, ID = _t, #"Sender Address" = _t, #"Receiver Address" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Folder Path", type text}, {"Subject", type text}, {"date time sent ", type datetime}, {"date time received ", type datetime}, {"ID", type text}, {"Sender Address", type text}, {"Receiver Address", type text}}, "en-US"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AllRows", each _, type table [Folder Path=text, Subject=text, #"date time sent "=datetime, #"date time received "=datetime, ID=text, Sender Address=text, Receiver Address=text]}}),
CreateResult = Table.AddColumn
(
#"Grouped Rows",
"Result",
(row)=>
let
HasSent = if List.Count(List.Select(row[AllRows][Folder Path], each _ = "\Sent Item\"))>0 then true else false,
HasReceived = if List.Count(List.Select(row[AllRows][Folder Path], each _ = "\Reeceived item \"))>0 then true else false,
DurationIfHasSentAndHasReceived = if HasSent = true and HasReceived = true then
Duration.TotalMinutes(List.Min(Table.SelectRows(row[AllRows], each [Folder Path]= "\Sent Item\")[#"date time sent "]) -
Table.SelectRows(row[AllRows], each [Folder Path]= "\Reeceived item \")[#"date time sent "]{0})
else
null
in
[HasSent = HasSent, DurationReceivedAndFirstSentMail =DurationIfHasSentAndHasReceived ]
),
#"Expanded Result" = Table.ExpandRecordColumn(CreateResult, "Result", {"HasSent", "DurationReceivedAndFirstSentMail"}, {"HasSent", "DurationReceivedAndFirstSentMail"})
in
#"Expanded Result"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
