Variable Column list in List.Product & Lict.Nonnullcount

[Nicaustral]

Hi, I am trying to transform : 
#"Inserted Multiplication" = Table.AddColumn(#"Renamed Columns", "Multiplication", each List.Product({[Column1], [Column2], [Column3], [Column4], [Column5], [ColumnBASE]}), Currency.Type),
and
#"Inserted Count" = Table.AddColumn(#"Added Index", "Count", each List.NonNullCount({[Column1], [Column2], [Column3], [Column4], [Column5]}), Int64.Type),

so that it can accomodate a variable list of columns (it needs to use all the columns with a name starting with "Column")

I am replacing  {[Column1], [Column2], [Column3], [Column4], [Column5]} by 
List.Select(Table.ColumnNames(#"Added Index"), each Text.Contains(_,"Column"))
but the count returns the same value for each row (which is incorrect) and the list.Product returns an error.
Seems to be a type error ( list is not considered as a list of columns?) 

Can this be solved ?  thank you 

[mahoneypat · Accepted solution]

It might be easier to unpivot your data to do this; however, here is an example of how to do this with some example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNlGK1YlG4ukomSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index"{[Index]}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Record.ToList(Record.SelectFields([Custom], List.Select(Table.ColumnNames(#"Added Index"), each Text.Contains(_, "Column"))))))
in
    #"Added Custom1"

Regards,

Pat

[Nicaustral]

HI Pat,

It works ! Legend !