Good day!
I need to do a simple addition of values in a row, where the column name matches a particular pattern.
I have successfully created a list of column names based on a pattern, so for example I could turn this:
into this:
That is, instead of listing out all the fields, I have created a list of all those that start with the correct prefix. As there will be dozens of them, this is preferred!
However, when I want to sum the column values, the list needs to be:
i.e. they are wrapped in [#...]
How do I create a dynamic list like that? i.e. to convert this:
to use a dynamic list of columns with the prefix, instead of listing them out.
I need to do a simple addition of values in a row, where the column name matches a particular pattern.
I have successfully created a list of column names based on a pattern, so for example I could turn this:
Code:
= Table.ReplaceValue(#"LastTable",null,0,Replacer.ReplaceValue,{"Scores-NewRepeat.SEOns", "Scores-NewRepeat.SEPos", "Scores-NewRepeat.SEAbc"})
into this:
Code:
= Table.ReplaceValue(#"LastTable",null,0,Replacer.ReplaceValue, List.Select(Table.ColumnNames(#"LastTable"), each Text.StartsWith(_, "Scores-NewRepeat.SE") ))
That is, instead of listing out all the fields, I have created a list of all those that start with the correct prefix. As there will be dozens of them, this is preferred!
However, when I want to sum the column values, the list needs to be:
Code:
{[#"Scores-NewRepeat.SEOns"], [#"Scores-NewRepeat.SEPos"], [#"Scores-NewRepeat.SEAbc"]}
How do I create a dynamic list like that? i.e. to convert this:
Code:
= Table.AddColumn(#"LastTable", "Addition", each List.Sum({[#"Scores-NewRepeat.SEOns"], [#"Scores-NewRepeat.SEPos"], [#"Scores-NewRepeat.SEAbc"]}), type number)
to use a dynamic list of columns with the prefix, instead of listing them out.