Forum Discussion
Return a value found in another table in any column
Dear community,
I'm having a hard time finding the right formula to do the following:
I have a Table 1 that contains references, one reference per row. I have a Table 2 that contains references spread accross multiple columns, so up to 12 references per rows.
I need to find a way to tell if the reference in Table 1, is found in Table 2 (any column).
I have tried look ups but I can only manage to look up one column, not the whole table 2.
I hope you can help me find a solution,
Thanks
Table 1 - this is the table I get from our database:
ID | Reference | Result |
1 | 556-0000025443L |
|
2 | 556-0000029463L |
|
3 | 556-0000025835L |
|
Table 2 - this is the table I get from our database
Reference 1 | Reference 2 | Reference 3 |
556-0000025443L | 556-0000026001L | 556-0000026671 |
556-0000026730L | 556-0000026732 |
|
556-0000025833L | 556-0000025835L |
|
Result for Table 1- this is the table I would like to get
ID | Reference | Result |
1 | 556-0000025443L | Found |
2 | 556-0000029463L |
|
3 | 556-0000025835L | Found |
pls try this
if your table2 is very large, then it would be better for you to write it like this
[ a =List.Buffer( List.Combine( Table.ToColumns(Table2))), b = if List.Contains(a,[#"Reference "]) then "Found" else ""][b]
3 Replies
- Ahmedx
Super User
pls try this
- Ahmedx
if your table2 is very large, then it would be better for you to write it like this
[ a =List.Buffer( List.Combine( Table.ToColumns(Table2))), b = if List.Contains(a,[#"Reference "]) then "Found" else ""][b]
- shafiz_p
Hi Clilly Connect both the table to power query. Keep table as it is. Create a new custom column in Table1 and write code as in the image below:
Checking each value of Table1 Reference with the Tab21 each 3 reference column, if matched, then it would be found else blank.
Hope this helps!!