Results 1 to 3 of 3

Thread: How do I Create a list of column values?

  1. #1
    Neophyte hamble's Avatar
    Join Date
    Jun 2021
    Posts
    2
    Articles
    0
    Excel Version
    O365

    Question How do I Create a list of column values?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    122
    Articles
    0
    Excel Version
    Excel 365
    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"

  3. #3
    Neophyte hamble's Avatar
    Join Date
    Jun 2021
    Posts
    2
    Articles
    0
    Excel Version
    O365
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •