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:

startTimeendTimeeventTypeMachine name
2024.05.17 11:422024.05.21 18:23ProductionA
2024.05.21 18:352024.05.21 18:45ProductionA
2024.05.21 18:452024.05.21 18:48ErrorModeA
2024.05.21 18:482024.05.21 19:12StandByA
2024.05.23 10:122024.05.23 10:15ErrorModeB
2024.05.23 10:152024.05.23 11:24ProductionB
2024.05.23 11:242024.05.23 11:26ErrorModeB
2024.05.23 11:262024.05.23 11:27StandByB
2024.05.23 12:262024.05.23 12:28StandByB
2024.05.23 12:282024.05.23 20:39ProductionB

 

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:

startTimeendTimeeventTypeMachine name
2024.05.17 11:422024.05.21 18:23ProductionA
2024.05.21 18:232024.05.21 18:35TurnedOffA
2024.05.21 18:352024.05.21 18:45ProductionA
2024.05.21 18:452024.05.21 18:48ErrorModeA
2024.05.21 18:482024.05.21 19:12StandByA
2024.05.23 10:122024.05.23 10:15ErrorModeB
2024.05.23 10:152024.05.23 11:24ProductionB
2024.05.23 11:242024.05.23 11:26ErrorModeB
2024.05.23 11:262024.05.23 12:26TurnedOffB
2024.05.23 12:262024.05.23 12:28StandByB
2024.05.23 12:282024.05.23 20:39ProductionB

 

Is it possible? And if yes, how?

 

Thank you.

Need Help
  • jgeddes's avatar
    jgeddes
    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.

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    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.

    • Adam15's avatar
      Adam15
      Frequent Visitor
      2 years ago

      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.