Forum Discussion
Mic1979
Post Partisan
Post PartisanChange a function to reduce the lines of code
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.
- (tbl,Column1,Column2 ,Column1_cond as list ,Column2_cond as list,ColumnsToChange as Lists) =>letNew_Table = Table.FromRecords(Table.TransformRows(tbl, eachif 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)) )inNew_Tablethe format of ColumnsToChange should be:{{ColumnToChange1, "A"} ,{ColumnToChange2, "B"},{ColumnToChange3,"C"},{ColumnToChange4, "E"},{ColumnToChange5, "F"},{ColumnToChange6, "G"},{ColumnToChange7, "H"},{ColumnToChange8, "I"},.......,.......,{ColumnToChange15, "Z"}}
4 Replies
- lbendlin
Super User
use Table.RenameColumns or Table.TransformColumnNames
- wdx223_Daniel
Community Champion
(tbl,Column1,Column2 ,Column1_cond as list ,Column2_cond as list,ColumnsToChange as Lists) =>letNew_Table = Table.FromRecords(Table.TransformRows(tbl, eachif 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)) )inNew_Tablethe format of ColumnsToChange should be:{{ColumnToChange1, "A"} ,{ColumnToChange2, "B"},{ColumnToChange3,"C"},{ColumnToChange4, "E"},{ColumnToChange5, "F"},{ColumnToChange6, "G"},{ColumnToChange7, "H"},{ColumnToChange8, "I"},.......,.......,{ColumnToChange15, "Z"}}
