List.Contains to Lookup for a value within the same table

[ragnezza]

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!

[ragnezza]

that's amazing, it's working.

Thanks!

[Anonymous · Accepted solution]

Write it like

if List.Contains(List.Buffer([PreviousStepName[Column B]), [Column A]) then "Yes" else "No"

--Nate