Forum Discussion

Supercat's avatar
Supercat
Frequent Visitor
2 years ago
Solved

Replace Value in several columns

Hello everyone,

     I have been considering this question for a long time. I have a table with several columns where value(integer actually) stores. Also I have another table with column name, integer and the meaning standing for. I have to combine these two tables or actually replace the value from integer to its real meaning. Can anyone help me with this?

 

 

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    as I wrote above - be careful. If your data has errors (you either don't have such columns or some values in tbl_A are not listed in tbl_B). We can fix that:

    #problem with column names

     

    List.Intersect({Table.ColumnNames(tbl_A) , List.Distinct(tbl_B[Column1])})

     

    #problem with missing values (nothing to replace with):

     

    b = try List.Select(a, (x) => x{0} = value){0}{1} otherwise null][b],

     

     see if it works.

6 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hello, Supercat i did not catch possible errors so be careful

    let
    tbl_A = your_a_table, 
    tbl_B = your_another_table,
    columns = {"Unit", "CapexCount"},
    g = Table.Group(tbl_B, "Column1", {"repl", each List.Zip({[selectvalue], [selectname]})}),
    rec = Record.FromList(g[repl], g[Column1]),
    f = (value, col_name) =>
        [a = Record.FieldOrDefault(rec, col_name, {}),
        b = List.Select(a, (x) => x{0} = value){0}{1}][b],
    tx = List.Transform(columns, each {_, (x) => f(x, _)}),
    z = Table.TransformColumns(tbl_A, tx)
in
    z

     

    • Supercat's avatar
      Supercat
      Frequent Visitor
      2 years ago

      Yes, it worked. But if I need to transfer all the column in table_A, I need to type all the column into the code. Can I just take all the item in table_B 'column 1' to replace the following row? Thank you very much.

      columns = {"Unit", "CapexCount"},
    • Supercat's avatar
      Supercat
      Frequent Visitor
      2 years ago

      Thank you very much. It may goes wrong when 'null' in the transferred column in table_A, the message as following:

       

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        as I wrote above - be careful. If your data has errors (you either don't have such columns or some values in tbl_A are not listed in tbl_B). We can fix that:

        #problem with column names

         

        List.Intersect({Table.ColumnNames(tbl_A) , List.Distinct(tbl_B[Column1])})

         

        #problem with missing values (nothing to replace with):

         

        b = try List.Select(a, (x) => x{0} = value){0}{1} otherwise null][b],

         

         see if it works.

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi,

    Another solution with List.ReplaceMatchingItems

     

    let
    Transform = List.Transform(
          Table.ColumnNames(tbl_A),
          (x)=> let Selection = Table.SelectRows(tbl_B, each [Column1]=x) 
                in List.Zip({Selection[selectvalue], Selection[selectname]})),
    ReplaceMatchingItems = Table.FromColumns(
           List.Transform( 
              List.Zip({Table.ToColumns(tbl_A), Transform}),
              each List.ReplaceMatchingItems(_{0}, _{1})),
           Table.ColumnNames(tbl_A))
    in
    ReplaceMatchingItems

     Stéphane