More efficient way to keep only numbers in multiple columns

what you ask can be done, even if  "simple" is a very relative concept. I had already replied that if you could post even a fictitious example of your data and explain well what you need, someone will give you some answers. In the meantime you have to be satisfied with attempts and hypotheses (since you have not yet explained which situation you start from and where you want to arrive). I am attaching an example of what you can do with the Table.TransformColumns function that you wanted to use (but the same thing can be achieved with the Table.transformRows function)

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FYsxCsAwDAP/4rXJYDlL3xKylFKo7SSl9P/UAQ3idKqVGBIZx2CB9JH6yBAUSvR+QeDmrpZXfeY9N0DM1KilSud1gk3BUsRNM2MZocY7/L2bChDDQq39", BinaryEncoding.Base64), Compression.Deflate)), 
    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [c1 = _t, c2 = _t]),
    #"Duplicata colonna" = Table.DuplicateColumn(Origine, "c1", "c1 - Copia"),
    #"Rinominate colonne" = Table.RenameColumns(#"Duplicata colonna",{{"c1 - Copia", "c3"}}),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Rinominate colonne", "c1", "c1 - Copia"),
    #"Rinominate colonne1" = Table.RenameColumns(#"Duplicata colonna1",{{"c1 - Copia", "c4"}}),
   // #"Convertita in maiuscolo ogni parola" = Table.TransformColumns(#"Rinominate colonne1",{{"c3", each Text.SplitAny(_,  Text.Combine({"a".."z",","}))},{"c4", each Text.SplitAny(_,  Text.Combine({"a".."z",","}))}})
    #"Somma tutti i numeri" = Table.TransformColumns(#"Rinominate colonne1", 
    List.Transform({"c3","c4"}, each {_, each List.Sum(List.Transform(Text.SplitAny(_,  Text.Combine({"a".."z",","})),Number.From))}))

in
    #"Somma tutti i numeri"

if you have different group of column to transform in different way:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FYsxCsAwDAP/4rXJYDlL3xKylFKo7SSl9P/UAQ3idKqVGBIZx2CB9JH6yBAUSvR+QeDmrpZXfeY9N0DM1KilSud1gk3BUsRNM2MZocY7/L2bChDDQq39", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [c1 = _t, c2 = _t]),
    #"Duplicata colonna" = Table.DuplicateColumn(Origine, "c1", "c3"),
    #"Duplicata colonna2" = Table.DuplicateColumn(#"Duplicata colonna", "c1", "c5"),
    #"Duplicata colonna3" = Table.DuplicateColumn(#"Duplicata colonna2", "c2", "c4"),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Duplicata colonna3", "c2", "c6"),
     transform = Table.TransformColumns(#"Duplicata colonna1", List.Transform({"c1","c3","c4"}, each {_, each List.Sum(List.Transform(Text.SplitAny(_,  Text.Combine({"a".."z",","})),Number.From))}) & 
     List.Transform({"c2","c5","c6"}, each {_, each List.Product(List.Transform(Text.SplitAny(_,  Text.Combine({"a".."z",","})),Number.From))})),
    #"Riordinate colonne" = Table.ReorderColumns(transform,{"c1", "c2", "c3", "c4", "c5", "c6"})
in
    #"Riordinate colonne"

here I made the example with two groups {c1, c3, c4} and {c2, c5, c6} but in general these lists of names can be dynamically constructed by applying selection criteria to the list of the names of all the columns:

AllColNames = Table.ColumnNames (yourTab)

group1 = List.Select (AllColNames, (c) => SatisfyGroup1Criteria (c))

group2 = List.Select (AllColNames, (c) => SatisfyGroup2Criteria (c))

Hello k1s1 

as I wrote in my post with my approach it's not needed to define the transformation of every column, but can do within one code as I'm referecing in my post as well. The idea is to get through every row... transform every single row to a table, transform the value-column and transform it back to a row... and then transform it to a table. The nice here is that you don't need to define every single column and therefore is dynamic.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy