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, inte...
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.

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