Calculate how long it took to reply to email on my side

[Tinu]

I need to calculate how much time passed between an email received and sending out an answer.

- This is based on the exchange online standard connector for the mail table

- each email conversation has a unique index "ConversationIndexGUID" (this is previously calcluated as = Table.AddColumn(#"Changed Type", "ConversationIndexGUID", each Text.Middle([Attributes.ConversationIndex],6,16)))

- each conversation can consist of 1 to many back and forth emails. 

- Sent email is defined as AllRows.FolderPath = "\Gesendete Elemente\"

- Received email is defined as AllRows.FolderPath = "\Posteingang\"

- The column AllRows.DateTimeSent is used to calculate the time

I am struggeling to calculate for each "Sent Email" what is the time difference to the nearest, earlier "Received Email" (if existing) of the same conversation ID.

 Not sure if DAX / M-Query is better. Final email box will be about 500k messages strong.

Your help is greatly appreciated. Thanks!

Here a few lines as CSV / Table. (not sure how I can share csv here)

AllRows.Folder Path	AllRows.DateTimeSent	ConversationIndexGUID
\Posteingang\	05.12.2022 22:09	3mRZb5LVrksEu4AK
\Posteingang\	05.12.2022 15:14	R6GinqFR1MTTqUu6
\Gesendete Elemente\	05.12.2022 15:11	R6GinqFR1MTTqUu6
\Posteingang\	05.12.2022 15:01	R6GinqFR1MTTqUu6
\Posteingang\	05.12.2022 10:14	oJNDNtUKvq4kGsdH
\Posteingang\	05.12.2022 08:55	7yTKYcNW//C0GAY5
\Posteingang\	05.12.2022 03:24	C76rsaFyw0VEuDmm
\Gesendete Elemente\	04.12.2022 21:21	kkcSZJi3i9TmS7b0
\Posteingang\	04.12.2022 21:19	kkcSZJi3i9TmS7b0
\Posteingang\	04.12.2022 20:58	rEZPakDzuwDUydqi
\Posteingang\	04.12.2022 20:58	rEvGLwKxox2UO3Vy
\Posteingang\	04.12.2022 07:39	sovAe/8YCOtEuzlw
\Posteingang\	04.12.2022 03:30	ht1Gm/SGuUVki3HT
\Posteingang\	04.12.2022 02:45	IjRfhZa5J9ck6tyT
\Gesendete Elemente\	03.12.2022 16:40	/AFWM5lJ6MTQa6yc
\Gesendete Elemente\	02.12.2022 16:46	SZkXIk9J4xM0WXpK
\Posteingang\	02.12.2022 16:44	SZkXIk9J4xM0WXpK
\Posteingang\	02.12.2022 16:16	EH/oCwDE7JdEGXi4
\Gesendete Elemente\	02.12.2022 16:16	EH/oCwDE7JdEGXi4
\Posteingang\	02.12.2022 16:16	EG3JVhQVNMvkWh8v
\Gesendete Elemente\	02.12.2022 16:16	EG3JVhQVNMvkWh8v
\Gesendete Elemente\	02.12.2022 15:57	vHo5izMBDdR5+DWt
\Gesendete Elemente\	02.12.2022 15:51	vu7Vby3CawRG+q+k
\Gesendete Elemente\	02.12.2022 15:41	kkcSZJi3i9TmS7b0
\Gesendete Elemente\	02.12.2022 15:34	B0hLT2Dcy6QhCUce
\Gesendete Elemente\	02.12.2022 15:33	R6GinqFR1MTTqUu6
\Posteingang\	02.12.2022 15:11	vHo5izMBDdR5+DWt
\Posteingang\	02.12.2022 15:03	j/KdJe8bZR3Uu6en
\Posteingang\	02.12.2022 14:43	g722+fE/fTTrSkVe
\Gesendete Elemente\	02.12.2022 13:51	vHo5izMBDdR5+DWt
\Gesendete Elemente\	02.12.2022 13:47	rn0JumZmXrQIWnSx

[Syndicate_Admin]

Where do you get outlook csv from?

[jcastr02]

Hello @Syndicate_Admin 

1. Get Data>> More>>Online Service>>Microsoft Exchange Online.
2. Type the mailbox address: A@microsoft.com.
3. Select the Microsoft account type.
4. Sign in the A@microsoft.com and enter password.
5. Then you could see load the data.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

How long did it take to reply Calculated Column =
VAR _email = Data[ConversationIndexGUID]
VAR _senttime = Data[AllRows.DateTimeSent]
VAR _receivedemail =
    MAXX (
        FILTER (
            Data,
            Data[ConversationIndexGUID] = _email
                && Data[AllRows.Folder Path] = "\Posteingang\"
                && Data[AllRows.DateTimeSent] < _senttime
        ),
        Data[AllRows.DateTimeSent]
    )
RETURN
    IF (
        Data[AllRows.Folder Path] = "\Gesendete Elemente\",
        DATEDIFF ( _receivedemail, _senttime, MINUTE )
    )

[jcastr02]

@Tinu @Jihwan_Kim  any thoughts?

I tried to expand Attributes to get ConversationIndex, but when I do it shows a "Binary"

[Tinu]

For this case you want another create another column: Table.AddColumn(#"Changed Type", "ConversationIndexGUID", each Text.Middle([Attributes.ConversationIndex],6,16))) You can also check out tinu.pbix how it works.

[24601]

Apologies for the reply a long time since it was posted but how did you decide on the characters between 6-16 being your ID for all the emails in that thread?

I'm trying to find rules to state what I have a strong feeling about. 

[jcastr02]

Thanks @Tinu , how do you get the 'Attribute.ConversationIndex' column to come up vs. it coming up as Binary?

[Tinu]

Hi

I had a check with my data and it looks very good. Will check a bit deeper, but I would say this is so far the best solution to measure reply-times in outlook with Power BI! Awesome, thanks!