Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
1 year ago
Solved

TIme Interval Calculation

Hi - I am looking to determine the time interview between records per LocationCode and Date   Example Data:   LocationCode Date-Time-Stamp 1234-24 4/24/2025 10:00 am 1234-24 4/24/2025...
  • ronrsnfld's avatar
    1 year ago

    Here's one way:

      (Paste the code below into the Advanced Editor and explore the Applied Steps to understand.

       Then adapt to your actual data)

    • Group by LocationCode
    • Sort the subgroups (might not be necessary)
    • Add an Index column to each subgroup to enable 
      • Subtract each time stamp from the next one, UNLESS it is the first (Index = 0)
    • Expand the subtable
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNtE1MlHSUTLRNwIiAyNTBUMDKwMDhcRcpVgdHAoMMRSYQhSYQhWA5AtwyxsjyZuYmpljdUEiXnkTUwU8CowwLEB1gAW6+ajSllDpWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LocationCode = _t, #"Date-Time-Stamp" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"LocationCode", type text}, {"Date-Time-Stamp", type datetime}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"LocationCode"}, {
            {"Intervals", (t)=>
                [a=Table.Sort(t, each [#"Date-Time-Stamp"]), //Sort may not be necessary
                 b=Table.AddIndexColumn(a,"Index",0,1,Int64.Type),
                 c=Table.AddColumn(b,"TimeInterval-Minutes", each 
                        if [Index] = 0 then null else Duration.TotalMinutes([#"Date-Time-Stamp"] - b[#"Date-Time-Stamp"]{[Index]-1})  )
                ][c], 
            type table [LocationCode=nullable text, #"Date-Time-Stamp"=nullable datetime, #"TimeInterval-Minutes"=Int64.Type]}}),
        
        #"Expanded Intervals" = Table.ExpandTableColumn(#"Grouped Rows", "Intervals",
                                    {"Date-Time-Stamp", "TimeInterval-Minutes"})
    in
        #"Expanded Intervals"

     

    Source

     

    Results