Replace Value in several columns

[Supercat]

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?

[slorin]

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

[AlienSx]

@Supercat sure. List.Distinct(tbl_B[Column1])

[Supercat]

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

[AlienSx · Accepted solution]

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.

[AlienSx]

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]

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"},