Increment index column based on another specific value

[ahutchins]

I have a dataset that includes an IncidentNumber, Responder, and ActionCode. Each responder is supposed to go through four ActionCodes, which I'll call Action 1-4, but some actions are skipped. Each Responder could repeat Actions 1-4 in a given IncidentNumber. See sample below.

What I would like to do is to add a column that I'll call InstanceNum that increments whenever Action 1 comes up. So far, I have successfully grouped by Incident and Responder using the All Rows operation. I can add an index column, but I can't figure out how to increment the number when Action 1 comes up.

Any help is appreciated.

Sample

Incident	Responder	Action	InstanceNum (what I want to add)
1	A	2	1
1	A	3	1
1	A	4	1
1	A	1	2
1	A	2	2
1	A	3	2
1	B	1	1
1	B	2	1
1	B	3	1

[Vijay_A_Verma]

Use this Excel file having the Query and output

https://1drv.ms/x/s!Akd5y6ruJhvhuTOMPxSd1vSL2xyh?e=pSZpcs 

[ahutchins]

Thank you so much! As it turns out, my version of Office (16) is too old. Fortunately, I'm a beta tester for 365 and it works great on that machine.

[Vijay_A_Verma · Accepted solution]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiOlWB0EzxiFZ4LCM0Th4dOHS84JxRQnFJVOOFQaoZhphGKmEYrLkFTGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Incident = _t, Responder = _t, Action = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Incident", "Responder"}, {{"Temp", each _, type table [Incident=number, Responder=text, Action=number]}}),
    //Function Start
    fxProcessNext=(Tbl)=>
        let
            #"Added Index" = Table.AddIndexColumn(Tbl, "Index", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if Tbl[Action]{[Index]-1}>[Action] then "Y" else "N" otherwise "Y"),
            #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Count(List.Select(List.FirstN(#"Added Custom"[Custom],[Index]+1), each _ = "Y"))),
            #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom.1", "InstanceNum"}})
        in
            #"Renamed Columns",
    //Function End
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcessNext", each fxProcessNext([Temp])),
    #"Expanded fxProcessNext" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcessNext", {"Action", "InstanceNum"}, {"Action", "InstanceNum"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcessNext",{"Temp"})
in
    #"Removed Columns"

[ahutchins]

Thank you for your help. When I try the blank query I get this error:

"Expression.Error: 5 arguments were passed to function which expects between 2 and 4.

Details:

  Pattern=

  Arguments=List"