Forum Discussion
jerryr125
Helper IV
1 year agoTIme 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...
- 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
ronrsnfld
Super User
1 year agoHere'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