Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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.
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
Thank you very much. It may goes wrong when 'null' in the transferred column in table_A, the message as following:
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"},
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
69 | |
24 | |
18 | |
13 |