Forum Discussion

Adam15's avatar
Adam15
Frequent Visitor
2 years ago
Solved

Insert row if a a time range is missing

Hey guys,   I have a table like this: startTime endTime eventType Machine name 2024.05.17 11:42 2024.05.21 18:23 Production A 2024.05.21 18:35 2024.05.21 18:45 Production A 20...
  • jgeddes's avatar
    2 years ago

    Here is a possible solution. Paste this into the advanced editor of a blank query and you can work through the steps.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc8xC8IwEAXgvxIyl5K7JPaazYKjIDiGDmIcXBoIdfDfG7EIvUTqmMf7uDzvJSo0rbItdALAGZTNN0IQQA51jk4phsd1vscpP/ZybDxraVtAY/+BpgIpR4eUYjrGcPvliLnewfvz5/kyheFZKi1AfSossuzaUHOWOXBo+LwSLi0W7TYPLiUWdat5pcJS5Yi2Fa0VKqf7yrjxBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startTime = _t, endTime = _t, eventType = _t, #"Machine name" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"startTime", type datetime}, {"endTime", type datetime}, {"eventType", type text}, {"Machine name", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "nextStart", each try if #"Added Index"[Machine name]{[Index]+1} = [Machine name] and #"Added Index"[startTime]{[Index]+1} <> [endTime] then #"Added Index"[startTime]{[Index]+1} else null otherwise null, type datetime),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([nextStart] <> null)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"startTime", "Index"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"endTime", "startTime"}, {"nextStart", "endTime"}}),
        #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",each [eventType],"TurnedOff",Replacer.ReplaceText,{"eventType"}),
        Custom1 = Table.Combine({#"Replaced Value",#"Changed Type"}),
        Custom2 = Table.Sort(Custom1, {{"Machine name", Order.Ascending}, {"startTime", Order.Ascending}}),
        #"Reordered Columns" = Table.ReorderColumns(Custom2,{"startTime", "endTime", "eventType", "Machine name"})
    in
        #"Reordered Columns"

    Hope this gets you pointed in the right direction.