Forum Discussion
Adam15
2 years agoFrequent Visitor
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...
- 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.
jgeddes
2 years agoSuper User
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.
Adam15
2 years agoFrequent Visitor
Thank you, it's perfect. I had no idea about the try [Index]+1 function. I'm sure I can use it in the future for other projects aswell.