How do I Create a list of column values?

H

hamble

New member
Joined
Jun 9, 2021
Messages
2
Reaction score
0
Points
0
Excel Version(s)
O365
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:

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"]}
i.e. they are wrapped in [#...]

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.
 
Code: 
let
    LastTable,
    LST = List.Buffer(List.Select(Table.ColumnNames(LastTable), each Text.StartsWith(_, "Scores-NewRepeat.SE"))),
    #"Replaced Value" = Table.ReplaceValue(LastTable,null,0,Replacer.ReplaceValue, LST),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Addition", (x) => List.Sum(List.Transform(LST, each Record.Field(x,_))))
in
    #"Added Custom"
 
Dziekuje Bill!

So you are using the same list construct here for both versions. But when I have the UI generate me code with a fixed list, one construct creates {"Column1", "Column2"} while the other produces {[#"Column1"], [#"Column2"]}.

Are you able to explain what the difference is with your LST version and how it satisfies both?
 
You must log in or register to reply here.
Back
Top