Change a function to reduce the lines of code

[Mic1979]

Dear all,

I have the following custom function:

(

tbl, 

Column1, 

Column2 , 

Column1_cond as list , 

Column2_cond as list, 

ColumnToChange1, 

ColumnToChange2, 

ColumnToChange3, 

ColumnToChange4, 

ColumnToChange5, 

ColumnToChange6, 

ColumnToChange7, 

ColumnToChange8, 

ColumnToChange9,

ColumnToChange10,

ColumnToChange11,

ColumnToChange12,

ColumnToChange14,

ColumnToChange15 ) =>

let

    New_Table = Table.FromRecords(Table.TransformRows(tbl, each

                if  List.Contains(Column1_cond, Record.Field(_, Column1)) and List.Contains(Column2_cond, Record.Field(_, Column2))

                then Record.TransformFields(_, {

                     {ColumnToChange1, (x)=> "A"} ,          

                     {ColumnToChange2, (x)=> "B"},

                     {ColumnToChange3, (x)=> "C"},

                     {ColumnToChange4, (x)=> "E"},

                     {ColumnToChange5, (x)=> "F"},

                     {ColumnToChange6, (x)=> "G"},

                     {ColumnToChange7, (x)=> "H"},

                     {ColumnToChange8, (x)=> "I"},

                     .......,

                     .......,

                     {ColumnToChange15, (x)=> "Z"}

                     })

                else _ ), Value.Type(Table.FirstN(tbl, 0)) )

in

    New_Table

Columns from ColumnToChange1 to ColumnToChange15 are all the columns of the table, excludingColumn1 and Column2.

Could you suggest an alternative way to write this code, instead of listing all the ColumnToChange?

Thanks for your support.

[wdx223_Daniel · Accepted solution]

(

tbl, 

Column1, 

Column2 , 

Column1_cond as list , 

Column2_cond as list, 

ColumnsToChange as Lists) =>

let

    New_Table = Table.FromRecords(Table.TransformRows(tbl, each

                if  List.Contains(Column1_cond, Record.Field(_, Column1)) and List.Contains(Column2_cond, Record.Field(_, Column2))

                then Record.TransformFields(_, List.Transform(ColumnsToChange,each {_{0},(x)=>_{1}}))

                else _ ), Value.Type(Table.FirstN(tbl, 0)) )

in

    New_Table

the format of ColumnsToChange should be:

{

                     {ColumnToChange1,  "A"} ,          

                     {ColumnToChange2, "B"},

                     {ColumnToChange3,"C"},

                     {ColumnToChange4, "E"},

                     {ColumnToChange5, "F"},

                     {ColumnToChange6,  "G"},

                     {ColumnToChange7, "H"},

                     {ColumnToChange8,  "I"},

                     .......,

                     .......,

                     {ColumnToChange15,  "Z"}

                     }

[Mic1979]

Hello,

many thanks for your help. It works. I will accept this as solution.

[Mic1979]

Hello @wdx223_Daniel 

Many thanks 😊

I will try it.

[lbendlin]

use Table.RenameColumns or Table.TransformColumnNames