Insert row if a a time range is missing

[Adam15]

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
2024.05.21 18:45	2024.05.21 18:48	ErrorMode	A
2024.05.21 18:48	2024.05.21 19:12	StandBy	A
2024.05.23 10:12	2024.05.23 10:15	ErrorMode	B
2024.05.23 10:15	2024.05.23 11:24	Production	B
2024.05.23 11:24	2024.05.23 11:26	ErrorMode	B
2024.05.23 11:26	2024.05.23 11:27	StandBy	B
2024.05.23 12:26	2024.05.23 12:28	StandBy	B
2024.05.23 12:28	2024.05.23 20:39	Production	B

As you can see, every machine can be in 3 different state (Error, StandBy, Production) and the table shows when the state started and ended.

Unfortunatelly I don't get any data when they are turned off. I'm using a stacked Gantt visualisation to show their state in the selected time frame, but if the machine was turned off all along in that timeframe the visualisation doesn't show it. So I want to insert a row when this happens where the state is TurnedOff. Something like this:

startTime	endTime	eventType	Machine name
2024.05.17 11:42	2024.05.21 18:23	Production	A
2024.05.21 18:23	2024.05.21 18:35	TurnedOff	A
2024.05.21 18:35	2024.05.21 18:45	Production	A
2024.05.21 18:45	2024.05.21 18:48	ErrorMode	A
2024.05.21 18:48	2024.05.21 19:12	StandBy	A
2024.05.23 10:12	2024.05.23 10:15	ErrorMode	B
2024.05.23 10:15	2024.05.23 11:24	Production	B
2024.05.23 11:24	2024.05.23 11:26	ErrorMode	B
2024.05.23 11:26	2024.05.23 12:26	TurnedOff	B
2024.05.23 12:26	2024.05.23 12:28	StandBy	B
2024.05.23 12:28	2024.05.23 20:39	Production	B

Is it possible? And if yes, how?

Thank you.

[jgeddes · Accepted solution]

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]

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.