Transforms multiple columns (defined in <code>FieldList</code>) of a <code>SourceTable</code> using the specified <code>TransformationFunction</code>. Optional parameters allow to pass addional function arguments and define a new type.

Table.TransformMultipleColumns.pq

let func =   
 // fnTransformMultipleColumns
let
    fnTransformMultipleColumns = (
        SourceTable as table, 
        FieldList, 
        TransformationFunction, 
        optional FunctionArgumentsList, 
        optional newType
    ) =>
        let
            FunctionArguments = if FunctionArgumentsList = null then {} else FunctionArgumentsList, 
            Source = Table.AddColumn(
                SourceTable, 
                "**tempColumn**", 
                each Record.FromList(
                    List.Transform(
                        FieldList, 
                        (l) =>
                            Function.Invoke(
                                TransformationFunction, 
                                {Record.Field(_, l)} & {_} & FunctionArguments
                            )
                    ), 
                    FieldList
                )
            ), 
            RemoveOldColumns = Table.RemoveColumns(Source, FieldList), 
            ExpandNewColumns = Table.ExpandRecordColumn(
                RemoveOldColumns, 
                "**tempColumn**", 
                FieldList
            ), 
            RestoreType = Value.ReplaceType(
                Table.ReorderColumns(ExpandNewColumns, Table.ColumnNames(SourceTable)), 
                Value.Type(SourceTable)
            ), 
            ApplyNewTypes = Table.TransformColumnTypes(
                ExpandNewColumns, 
                List.Transform(FieldList, each {_, newType})
            ), 
            Result = 
                if Value.Type(newType) = type type then
                    ApplyNewTypes
                else if newType = null or newType = true then
                    RestoreType
                else
                    ExpandNewColumns
        in
            Result
in
    fnTransformMultipleColumns ,
documentation = [
Documentation.Name =  " Table_TransformMultipleColumns ",
Documentation.Description = " Transforms multiple columns of a <code>SourceTable</code> using the specified <code>TransformationFunction</code>. ",
Documentation.LongDescription = " Transforms multiple columns (defined in <code>FieldList</code>) of a <code>SourceTable</code> using the specified <code>TransformationFunction</code>. Optional parameters allow to pass addional function arguments and define a new type. ",
Documentation.Category = " Table ",
Documentation.Source = " www.TheBIcountant.com ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description =  "  ",
Code = " let
    SourceTable = #table( type table [Product = Text.Type, Rate = Number.Type, Sales = Int64.Type, CoS = Int64.Type], 
 
//  Product| Rate| Sales| CoS| 
{//--------|-----|------|----|  
{       ""A"",  1.2,   100,  40 }, 
{       ""B"",  0.9,   200,  70 } } ), 

    FieldList = {""Sales"", ""CoS""}, 
    TransformationFunction = (currentItem, _) => currentItem * _[Rate],
    FunctionCall = Table_TransformMultipleColumns(SourceTable, FieldList, TransformationFunction)
in
    FunctionCall ",
Result = " #table( type table [Product = Text.Type, Rate = Number.Type, Sales = Int64.Type, CoS = Int64.Type], 
 
//  Product| Rate| Sales| CoS| 
{//--------|-----|------|----|  
{       ""A"",  1.2,   120,  48 }, 
{       ""B"",  0.9,   180,  63 } } )  
  "]}]
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Hi Imke,
While it is excellent code at all, the TransformationFunction definition looks too confusing. Why not define TransformationFunction as a function of 3 arguments:

1. Value: value as any to be transformed,
2. A table row: row as record
3. Additional arguments as list: optional args as nullable list

Then any TransformationFunction passed to Table_TransformMultipleColumns shall always be the 3-arguments function (anyway, we need to write this function manually, so we always could refer to them as arg{0}, arg{1} etc).
Or, maybe additional arguments could be a record. Then it is much easier to refer to them in the custom TransformationFunction.

What do you think?

Hi Maxim,
thanks for the feedback, but I don't understand, as my transformation function requires just those 3 elements as you have described.
Where is the difference? Maybe you could provide a sample?
Thanks a lot!

@ImkeF sorry, I was unclear.
First, I meant that it should be directly mentioned that:

• TransformationFunction has to have two mandatory arguments: value to be changed and row as record
• It could accept additional arguments, which should be provided as list in FunctionArgumentsList

And my suggestion was that, for example, in your case I need to define TransformationFunction = (value, row, a, b, c, d, e)=>, and when invoked by Table_TransformMultipleColumns it will look like this:
Table_TransformMultipleColumns(SourceTable, FieldList, TransformationFunction, {a,b,c,d,e})

For some reasons I thought that if TransformationFunction will be defined as the function of exactly 3 arguments (value as any, row as record, optional arguments as list), it made its definition more strict and clear. But it isn't )))