Forum Discussion
jerryr125
1 year agoHelper IV
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...
- 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
v-csrikanth
1 year agoCommunity Support
Hi jerryr125
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!
Best Regards,
Community Support Team _ C Srikanth.