Forum Discussion
Increment index column based on another specific value
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 |
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"
4 Replies
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"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"
Use this Excel file having the Query and output
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.
