Datetime an email was replied in Exchange

Question

Hello,&nbsp;We are exploring a shared mailbox in Outlook and trying to calculate how long it takes before an email gets replied to. I connected to outlook using Exchange connector in Power BI and been trying to find where the "reply to" information is. I expanded the Attributes column but it is not there.

danextian · Answer

Hi Anth79 ,It's been over a year since I worked on this one. I was able to find a workaround but not a fool-proof or a near fool-proof solution.&nbsp; I have also left the company I had this project for.
As far as I can remember, each email has a unique identification in the backend. Additional characters are appeneded to that unique ID whenever a reply is made to it. The same goes if the reply has another reply - additional chaeracters are appened to it. Please see below:

Unique ID
Timestamp

1234
23/07/2020 12:06

1234abc
23/07/2020 12:34

1234abc1234
23/07/2020 13:03

1234abc1234abc
23/07/2020 13:32

1234abc897
23/07/2020 14:01

1234abc897abc
23/07/2020 14:30

1356
23/07/2020 12:18

1356abc
23/07/2020 12:47

1356abc1234
23/07/2020 13:16

1356abc1234abc
23/07/2020 13:44

1356abc897
23/07/2020 14:13

1356abc897abc
23/07/2020 14:42

&nbsp;
The original email in the table above is 1234 and 1356 and all other emails with a unique ID that starts with these numbers are under their bucket. So how to know which ones in the mailbox are the original emails out of all the emails? The unique ID of the original email will have only a certain number of characters which is four in our illustration.&nbsp; 
&nbsp;
But like I've said, this solution is not fool-proof.&nbsp; Whenever a user changes the email subject, may because the email didn't have a subject or the subject was vague, it creates a separate converstaion. That is instead of appending additional characters to 1234abc897abc so the thread can contiue, the reply separates and creates its own original email - say 2345.Perhaps, there is a more fashionable and an easier way to do this.

v-lili6-msft · Answer

hi,&nbsp;danextianAll the&nbsp;information about&nbsp;"reply to" is in the&nbsp;Attributes column.&nbsp;https://community.powerbi.com/t5/Integrations-with-Files-and/PowerBI-Get-data-from-Exchange-Inbox-details-size-of-the-mail/td-p/258964https://community.powerbi.com/t5/Desktop/Mail-how-to-view-quot-from-quot-email-address-when-connected-to/td-p/349109https://community.powerbi.com/t5/Desktop/Outlook-how-to-link-Inbox-amp-Sent-Items/td-p/519110&nbsp;Best Regards,Lin

danextian · Answer

Hi Lin,&nbsp;Thank you for your response and my&nbsp; apologies for getting back late.&nbsp;I checked on the links you've posted but none of them works for my use case.&nbsp; I though I could use the ConversationTopic attribute to group the emails but realized that a topic can have the same name as other topics (if only the connector also exposes the conversation id).&nbsp; My goal now is to get the first email in the conversation from Inbox folder. I could probably make use of ConversationIndex attribute but it is in binary format. Would you know how to extract data from it?

anth79 · Answer

To make a unique ID, where i could still see topic, i concatentated Conversation ID (first 6 characters was enough) and Topic.

To report on the response time i did the following, based on the new conv identifier.
I ranked all emails, by received date and within the new conv identifier groupong and where the sender was not the shared inbox or staff members that have access to it.

I then ranked the sent dates where the opposite, based on sender, was true.

These were both done in separate tables, which i then merged via the conv identifier, to have a table containing only the first received and reply dates of a conversation.
Issue i have is our Tech dept turned off the ability to have Sent Items stored in the shared inbox, so we have to manually move them weekly. If we miss any, it cant then find the reply data for the above to work. As that property exists in the UI even when it's not in the shared inbox's sent items ("You replied to this email...") i thought it would be easy to extract but so far I have not identified it.
There's probably a far simpler solution, but I hope this helps 🙂

Forum Discussion

Datetime an email was replied in Exchange

4 Replies

Related content

spam replys

Exchange Rates - Historical

Converting Epoch Datetime into regular datetime format

Convert DateTime to Date

Exchange Connector Error