Table Transformations for Dynamic Columns

HI Anonymous

If I understand the problem correctly

Suppose in your above sample, you want to transform the Columns starting with V

for example multiply all values by 10 or divide them by 10 you can use something like this

let
Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Value 1", "Value 2", "Column 3"}),
ColumnsToTransform= List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "V")),
ActionToPerform=List.Repeat({each _ *10},List.Count(ColumnsToTransform)),
Transformed=Table.TransformColumns(Source,List.Zip({ColumnsToTransform,ActionToPerform}))
in
 Transformed

Please try this function:

(InputTable as table, ListOfColNames, TransformFunction as function) =>
let
    TransformFunctionList = List.Transform(ListOfColNames, (x) => {x, TransformFunction}),
    Transform = Table.TransformColumns(InputTable, TransformFunctionList)
in
    Transform

Name it "fnTransformMany" and call it like so:

let
    Source = Table.FromRows({{"New York", "23", "51", "732"}, {"Chicago", "25", "421", "23"}, {"Los Angeles", "632", "22", "423"}}, {"City", "Value 1", "Value 2", "Column 3"}),
    ColumnsToRemove = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "V")),
    Result = fnTransformMany(Source, ColumnsToRemove, each Text.Start(_, 2))
in
    Result