Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

Identify cycles in records

Hello everyone, along with greeting you I wanted to see if anyone within the group could give me a help to be able to solve this. This is an extract from an event log from a source-to-destination poi...
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5BCoAwDAS/IjkLzaYNhjxCBMFL6f+/YQ6lgvbgJRsWdphaSUxgoJW2zHFPLow9nuPiEgEkScIiC9gZrviU2dWorR2VB6oTBnGyepfqyg9Kf1rpzMocYdVu", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [event_id = _t, machine_id = _t, point_to_id = _t, point_from_id = _t, mission_start_time = _t, mission_end_time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"mission_start_time", type datetime}, {"mission_end_time", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Cycle Time", each try
if [point_to_id]=#"Added Index"{[Index]-1}[point_from_id]
and [point_from_id]=#"Added Index"{[Index]-1}[point_to_id]
and [mission_start_time]=#"Added Index"{[Index]-1}[mission_end_time]
then [mission_end_time]-#"Added Index"{[Index]-1}[mission_start_time] else null otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Cycle Time", type duration}})
in
    #"Changed Type1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".