Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Change 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.
 
 
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    1 year ago
    (
    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"}
                         }

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    use Table.RenameColumns or Table.TransformColumnNames

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    1 year ago
    (
    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's avatar
      Mic1979
      Post Partisan
      1 year ago

      Hello,

       

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