Forum Discussion
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...
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.
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
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"},