Conditional index based on dataset in one column

Hi MonkeySam 

try this custom function (create a new blank query, open the advanced editor and replace everything there with this code)

(tbl as table, col as text, optional prefix as text, optional newCol as text) as table =>
let
    p = prefix ?? "ABC",
    n = newCol ?? "Index",
    l = Table.Column(tbl, col),
    i = List.Accumulate(l, {}, (s, x) => s & {if Text.StartsWith(x, p) then 1 else List.Last(s) + 1})
in
    Table.FromColumns(Table.ToColumns(tbl) & {i}, Table.ColumnNames(tbl) & {n})

You can invoke it on your table by filling this out
Selecte the Query, enter the columnName "Data", your prefix and desired name for the conditional index

I hope this is helpful