Loop function based on keyword

[PythonMaster11]

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.

[Anonymous]

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

[BA_Pete]

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

[PythonMaster11]

Unfortunately, there are not. Besides a Date field, all I have are the columns you see in the picture. 

[BA_Pete · Accepted solution]

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

[PythonMaster11]

Hey Pete, I'm very new and don't know how to paste the formula into a blank query (from Other Sources, right?). I assumed I would merge the two queries after this? Once I got that figured out I was going to create a column that calculated the difference between max and min to display the duration of each job. Thanks for your help thus far.

[BA_Pete]

Hi @PythonMaster11 ,

In Power Query go to New Source, select Blank Query, then with that query selected, go to Home tab > Advanced Editor then paste my code completely over the default code in there.

Once you hit enter, it will generate the table I've created with all the steps that you can click through to see what is happening at each step.

Pete

[ronrsnfld]

Does "write" always follow "update"?  Or might there be extra lines in between?

[PythonMaster11]

Yes. Write and Update are unique keywords to mark the start and end of any given job.