Loop function based on keyword

Hi PythonMaster11 ,

Try pasting this into a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZA9C8IwFEX/imTukJe0Nc0mTg5CQcShdAiaaECbEuPgvzf247W42PWcB/feV1Xk5G3Qq70K2lt1X+0a4/xDBesakhBgkuYyy0mdVKT17gwDXEsqEDKEQBHyAQpJoYPH9qLmSaV72lmMkNDf/SsEfB4DEwSEOUXKeEdV48JN+zJKVCld0CveZWxRL16Ml98KQvQ0peP+qw5bZ4zWk5j+mhdIhx1vHTa/paNlfUQnDuFlDBouFsyJd1mcXX8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [robotAction = _t, startTime = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"robotAction", type text}, {"startTime", type time}}),
    addRandomBatchID = Table.AddColumn(chgTypes, "randomBatchID", each if [robotAction] = "Write Material Information" then List.Random(1) else {null}),
    expandRandomBatchID = Table.TransformColumns(addRandomBatchID, {"randomBatchID", each if _ = null then null else Text.Combine(List.Transform(_, Text.From)), type text}),
    replaceBlanks = Table.ReplaceValue(expandRandomBatchID,"",null,Replacer.ReplaceValue,{"randomBatchID"}),
    fillDownBatchID = Table.FillDown(replaceBlanks,{"randomBatchID"}),
    groupBatchID = Table.Group(fillDownBatchID, {"randomBatchID"}, {{"data", each _, type table [robotAction=nullable text, startTime=nullable time, randomBatchID=nullable text]}, {"minStart", each List.Min([startTime]), type nullable time}, {"maxStart", each List.Max([startTime]), type nullable time}}),
    expandDataColumn = Table.ExpandTableColumn(groupBatchID, "data", {"robotAction", "startTime"}, {"robotAction", "startTime"})
in
    expandDataColumn

It's a bit messy, and does rely on your data being in the correct order, but will hopefully inspire a cleaner solution if required.

Summary:

1) addRandomBatchID = adds a 1 item list of random numbers next to each process start (to identify a process batch)

2) expandRandomBatchID = expand list created in 1)

3) replaceBlanks = switch blanks for nulls - was lazy here!

4) fillDownBatchID = fill down our random number to the rest of each batch - this is why it's important original data is in the correct order beforehand

5) groupBatchID = group on our new batch ID, creatin All Rows, MIN, MAX, aggregate columns

6) expandDataColumn = expand the nested tables in [data] to get our original info back

Based on your actual data, you would probably shortcut this and just add a SUM of [Start to End] at the Group By stage along with an All Rows to get the original data back.

This gives the following output:

Pete