Forum Discussion

Kumar11109's avatar
Kumar11109
Helper IV
9 years ago

Time Difference Between two resources

Hi All, 

 

I am facing another problem with my data set. I am attaching an image for a sample of my dataset. 

 

The data set contains the time spent by children in a session on different resources. I need to find the time difference between the end time of a resource id and the start time of the next resource for the same session. 

I just need to find out how much time do students spend while moving from one resource to another. 

 

Its urgent so please help me as soon as you can. 

 

 

Need Help

15 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Kumar11109

     

    I entered the following data to create your table

     

    Session ID	Resource ID	Start Time	End Time
1	11	18:15:30	18:17:35
1	12	18:19:17	18:21:21
1	13	18:23:04	18:25:07
1	12	18:26:51	18:28:53
2	11	18:30:38	18:32:39
2	13	18:34:25	18:36:25
2	12	18:38:12	18:40:11
3	11	18:41:59	18:43:57
3	12	18:45:46	18:47:43
3	13	18:49:33	18:51:29
4	15	18:53:20	18:55:15
4	11	18:57:05	18:59:01
4	15	19:00:54	19:02:47
4	12	19:04:41	19:06:33

    and then applied these steps in the Query Editor

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBbDoQgDEX3wrcfpQ+QuxXj/rcxFa+DmUmIOamH9tLjKLVspc7PjhowIXZYlHOjoKyO/HGj1jxLMFYN4sSA9L8O2hCcpjvCpqCvDCawnaiwsQSOMM/WxHbhV+AIy5xEF9Q7pL1GeEUMoiH6Ep5rAW/Ens4SmMEHjBi5hzvkfDeThUG5yYjc6xKYITrkcQek/nTIkiCcqBljCcqq50OI7Ypznh8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Session ID" = _t, #"Resource ID" = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Session ID", Int64.Type}, {"Resource ID", Int64.Type}, {"Start Time", type time}, {"End Time", type time}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Session ID", Order.Ascending}, {"Resource ID", Order.Ascending}, {"End Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Session ID", "Resource ID", "Start Time"}, {"NewColumn.Session ID", "NewColumn.Resource ID", "NewColumn.Start Time"}),
    #"Added Custom" = Table.AddColumn(#"Expanded NewColumn1", "Custom", each if ([Session ID] = [NewColumn.Session ID] and [Resource ID] = [NewColumn.Resource ID])
then [NewColumn.Start Time] - [End Time]
else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "NewColumn.Session ID", "NewColumn.Resource ID", "NewColumn.Start Time"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Time Spent"}})
in
    #"Renamed Columns"

    Which resulted in the following table

     

     

    • Kumar11109's avatar
      Kumar11109
      Helper IV
      9 years ago

      Phil_SeamarkI don't think so this is what I want. If you look at the column "Time Spent" and the difference between end time for resource 11 and start time for resource 12 is 00:1:53 seconds and not 5 mins and 30 seconds. 
      This is the problem, I hope I am a bit more clear now. 
      Thank You for your quick reply BTW. 
      And it would be really appreciated if we can do this without going into the query, if possible and by just adding a column. 

       

      Thank You, 

      Kumar Ashwarya 

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Kumar11109

         

        Hi Kumar Ashwarya,

         

        According to your conditions, the records in this table should have an order. So firstly, we add an index column. Then we can use this formula in calculated column to get the time spent.

        TimeSpent =
VAR NextStartTime =
    CALCULATE (
        MIN ( Table1[Start Time] ),
        FILTER (
            Table1,
            Table1[Index]
                >= EARLIER ( Table1[Index] ) + 1
                && Table1[Session ID] = EARLIER ( Table1[Session ID] )
        )
    )
RETURN
IF ( ISBLANK ( NextStartTime ), BLANK (), NextStartTime - Table1[End Time] )

         Ps: 1. Column NextStartTime is just to show the process, you don’t need to add it.

               2. Pay attention to the data type.

         

         

    • Kumar11109's avatar
      Kumar11109
      Helper IV
      9 years ago
      Hi Dale, I got sick so didn't have time to calculate this. Will use your idea and get back to you if I have any more questions.