Hello, I'm trying to come up with the M code to find if the value from one field is also in another field (equivalent of a VLOOKUP in Excel) within the same table. The table has two columns (A and B) and I want to create a third one (flag) that shows 'Yes' if the value in Column B is also in Column A.
| Column A | Column B | flag |
| A01 | B01 | No |
| A02 | B01 | No |
| A03 | A01 | Yes |
| A04 | B01 | No |
| A05 | A02 | Yes |
I added a Custom Column in Power Query with the following M code:
flag =
if List.Contains( [Column A] , [Column B] ) then "Yes" else "No"
The 'flag' field creates but a bunch of 'Errors' show, and when I click on any error they show the following message:
Expression.Error: We cannot convert the value "A03" to type List.
Details:
Value=A03
Type=[Type]
Do I need to use a function to format either [Column A] or [ Column B] ? Or should I use a function different from List.Contains ?
Also, I cannot create this 'flag' in the front end with DAX (which would be much easier) because this 'flag' is already used as a key for a join within the Table M code.
Any suggestion will be greatly appreciated, thanks!
