Forum Discussion
List.Contains() is slow 2.0
Instead of using Table.Replace, try using Table.AddColumn to define the same thing as a custom column (and then delete the original [Epic Link] column and rename the new custom column to [Epic Link]).
Both of those seem intuitive to me. Adding a column is often faster than modifying an existing one.
For your second point, buffered_table[issue key] is already in memory as part of buffered_table, so buffering it into memory separately as a list probably(?) doesn't help that much. Since you are using buffered_list in List.Contains for each row, you want it to be as fast as possible and using a distinct version is more efficient. Having the distinct version already computed and explicitly buffered in that form is even better.
Thanks for the help AlexisOlson and Anonymous , the hints combined got the job done:
// this is for performance boosting the following list.contains function call
buffered_table = Table.Buffer(renamed),
buffered_list = List.Buffer(List.Distinct(buffered_table[issue key])),
result_a = Table.ReplaceValue(buffered_table,
each
[Epic Link],
each
if [Epic Link]<>null
then
if List.Contains(buffered_list,[Epic Link])
then [Epic Link]
else ""
else null,
Replacer.ReplaceText,{"Epic Link"}
),
result_b = Table.AddColumn(buffered_table,
"Epic Link (unlinked)",
each if [Epic Link]<>null
then
if List.Contains(buffered_list, [Epic Link])
then [Epic Link]
else ""
else null)
Result_b is about 75 times faster than result_a, but I'm puzzled that:
- adding a column and putting (new) values in there is faster than replacing a value in another column
- List.Buffer(List.Distinct(buffered_table[issue key])) should be faster than List.Distinct(List.Buffer(buffered_table[issue key]))
Super UserBoth of those seem intuitive to me. Adding a column is often faster than modifying an existing one.
For your second point, buffered_table[issue key] is already in memory as part of buffered_table, so buffering it into memory separately as a list probably(?) doesn't help that much. Since you are using buffered_list in List.Contains for each row, you want it to be as fast as possible and using a distinct version is more efficient. Having the distinct version already computed and explicitly buffered in that form is even better.
Got it solved as described above. The column was distinct already.