Forum Discussion
Reference column from another table for a List
Dear All,
I have the following table:
Table1
| CODE | VALUE |
| 724 | 1.000 |
323 | 500 |
null | 200 |
| 455 | 300 |
| null | 1.200 |
| 444 | 600 |
| 256 | 700 |
What I want is:
in column CODE, replace "null" with a list of all the distinct codes existent in column CODE except "null".
MSo instead of nulls I get a list of the following elements {724, 323, 455, 444, 256}
I achieve this throught the following step:
= Table.TransformColumns( #"previous step", {{"CODE", (x)=> if x = null then List.Select(List.Distinct( #"previous step"[CODE]), each _<> null) else {x}}})
Now, I have another table with a list of codes:
Table2
| CODE | VALUE |
| 123 | 100 |
829 | 100 |
| 047 | 150 |
| 299 | 300 |
My question is: How do I reference CODE column in Table 2 in the previous step INSTEAD of CODE column in Table1?
Thanks for your time
gianmarco
1 Reply
- lbendlinSuper User
You use any of the merge operations - like Table.AddColumn, joins, or index lookups.
What is your expected outcome based on the sample data?