Duration of Sent and Received RRS feed

  • Question

  • I would like to find the duration of emails from the sent time and receive time. I have pulled a power query from Exchange on a specific (completed emails). Using the Conversation Index attribute I would like to find how long from open to close did a conversation take computing the time using the sent and received columns. Also the thread may have 3 emails or 10 so the formula would need to know which email is the start and which one is the end email in the conversation to make the computation. Thank you.
    • Edited by D-a-n_L Monday, September 23, 2019 7:22 PM
    Monday, September 23, 2019 4:12 PM


All replies

  • Hey!

    I think that you have the right idea usin the Conversation Index.

    Have you tried using the Group By feature using the value from the Conversation Index? You can then do a Max and Min with that Group By Functionality to get the values that you're looking for like the earliest sent datetime, or he latest sent time, or the latest received time. It is completely up to you at that point, but the way to go would be with Group By.

    This article might be able to help you with that Group By functionality:

    Tuesday, September 24, 2019 12:21 PM
  • I think I am grasping the grouping feature but cant get past getting the receive date and sent date for each thread to determine total time to receive and send the email.

    • Edited by D-a-n_L Tuesday, September 24, 2019 11:17 PM insert pic
    Tuesday, September 24, 2019 11:14 PM
  • You could, for example, grab the earliest (min) of both columns and do the same but for a max operation. That will give you something among the lines of:

    Conversation - MinReceived - MaxReceived - MinSent - MaxSent

    and once that table is ready you can just do the math that you desire against those dates.

    Thursday, September 26, 2019 4:40 PM
  • As you can see from the screenshot I grouped by ConversationIndex. Rows 1&2 are one thread and 3&4 another. I would like to calculate each threads duration. The other concern I have is when I do this full scale some of the threads will be two rows and another 3 or more.

    1. Calculate thread duration

    2. Build query so I can throw it at a new set of threads and it will do the calulations automatically

    Thanks for all your help!

    Tuesday, October 1, 2019 6:36 PM
  • Hey!

    The grouping doesn't seem right.

    The goal should be to do something like this:


    where the group by field is the conversationIndex, and then you'd create new columns to get the Min and Max of the DateTimeReceived and you'd then just do simple math to get the duration from those two datetime values.

    Tuesday, October 1, 2019 8:42 PM
  • This is the grouping as you suggested.

    This is the result.

    I don't see how I can get the time difference between rows 1&2 then skip to the next rows 3&4 with separate results? The time calculations are by column not row.

    • Edited by D-a-n_L Tuesday, October 1, 2019 9:00 PM
    Tuesday, October 1, 2019 8:58 PM
  • Maybe I'm not understanding correctly your requirements, but I don't really see how you ended up with DateTimeReceived if you used the ConversationIndex (which should be a value shared across multiple rows) as the Group By column.

    The ConversationIndex should be some sort of text or unique identifier of the whole conversation.

    Wednesday, October 2, 2019 8:29 PM
  • My mistake. I re-did the grouping as follows:

    The results show three different threads. If you look at the last two rows which is one thread the received and sent times are on different rows. The times in each row are the same so how would I calculate the times circled. The times are the same if you look at each row. The difference is seen looking at them in the columns but how do you calculate that?

    Wednesday, October 2, 2019 9:59 PM
  • what would be your definition of a unique thread and how could you create a unique ID to target that thread? 

    By looking at your image, it appears that you need to create that new column that would act as the unique ID across the thread so your group by can work effectively

    Thursday, October 3, 2019 3:24 AM
  • Your a champ for sticking this out with me.

    What I want to accomplish is to query an outlook folder for all threads and calculate the time it took from start to finish for each thread. Suggestion?

    Thursday, October 3, 2019 6:17 PM
  • Your a champ for sticking this out with me.

    What I want to accomplish is to query an outlook folder for all threads and calculate the time it took from start to finish for each thread. Suggestion?

    I think that you're pretty close. It's just a matter of better understanding what you need and what fields you might want to use.

    I highly recommend that you read more about the Exchange fields being returned by the Connector. Here's an interesting article:

    Perhaps what you need is the Conversation Topic field instead of the Conversationindex.

    Thursday, October 3, 2019 11:02 PM
  • Excel 365 Pro Plus with Power Query.
    Ditto Miguel Escobar.
    With unique ID per thread.

    Friday, October 4, 2019 12:24 AM
  • How did you do to create table one and table two?

    Add index column > Group by conversation index ???

    • Edited by D-a-n_L Friday, October 4, 2019 9:51 PM
    Friday, October 4, 2019 9:37 PM
  • Table1 was created by Adobe Acrobat from your posted image.
    Hard. Prefer file.
    Table2 was created by Power Query from Table1.
    Invoke PQ for details.

    Friday, October 4, 2019 10:11 PM
  • Would you elaborate how you got table two using what PQ commands and steps.
    Monday, October 7, 2019 3:26 PM
  • Added a redundant image of the M code of Table2 query to my file.
    To elaborate on this would take a book like this:
    "M is for (Data) Monkey"
    Miguel Escobar is the author of the serious part of this book.

    Monday, October 7, 2019 5:20 PM
  • Ouch
    Monday, October 7, 2019 10:12 PM
  • Thank you for the help Miguel!
    Tuesday, October 8, 2019 3:43 PM