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))