Forum Discussion
Loop function based on keyword
Hello all,
I'm trying to find the duration of each job my robot is doing. Process Operations vary from 3 - 50+ so I figured the best way to calculate the duration was to identify the keyword "Write Material Information" and assign the corresponding Start Time to variable t1. Then search for the vary next instance of "Update Material Position" and assign the corresponding Start Time/End time to variable t2 (FYI, Start Time & End Time are always the same value at the end of each job). After that, create a custom column to calculate the Job Duration via T=t2-t1. Then the function would repeat to find the next keyword "Write Material Information" and start all over (there are 20,000+ rows to go through). Here's a little picture to help. I'm new to VBA and Power Query so thanks in advance.
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 expandDataColumnIt'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
8 Replies
- BA_Pete
Super User
Hi PythonMaster11 ,
I'm sure I already know the answer to this, but just in case: Are there other columns that give a robot ID and an 'Action Batch' ID?
Pete
Unfortunately, there are not. Besides a Date field, all I have are the columns you see in the picture.
- BA_Pete
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 expandDataColumnIt'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
You could filter your rows to include only the Rows with Update and Write. Sort by StartTime. Then you could use Table.Split(PriorStepName, 2). This will give you a column of nested two row tables, and you can now add a column like:
= List.Transform(PriorStep, "Duration" each Table.AddColumn( _, [StartTime]{1} - [StartTime]{0}))
Now you can expand the list of tables, and go back to the step before we filter, and join on that step as the table name, your last step name as the right table, with Robot Action and Start Time as your join columns.
--Nate
--Nate
