Forum Discussion
Conditional index based on dataset in one column
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.
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
4 Replies
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 helpfulRight. 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!
