Conditional index based on dataset in one column

[MonkeySam]

Hi Experts,
I have referenced this previous solution (https://community.fabric.microsoft.com/t5/Desktop/Conditional-Index/td-p/2840701) but the grouping option looks like not fit my challenge.

My dataset loaded in one column only. I'd like to based on that ABC reference to create the incremental index until the next ABC reference. Each ABC reference is unique.

Can someone please give a thought? Thanks.

[MonkeySam]

@m_dekorte ,
Your founction works perfectly. Thank you very much!!

[m_dekorte · Accepted solution]

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

[MonkeySam]

@m_dekorte 
Thanks for the prompt response.
I tried the codes but might miss somewhere. I tried to add a column with "Invoke Custom Function" and referenced to your custom function. "tbl" colum is greyed out and stuck here.

Would you mind to clarify further? Thanks.

[m_dekorte]

Right. Yes, this funciton works on the table as a whole so select the function query and fill out the fields, for tbl you can select your Table1 query
Let me know if that worked for you, thanks!