Forum Discussion
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
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
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.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!
