Cyclic Reference Error When Checking Whether Row Value in Column B Exists in Column A in Power Query

[d_pbi_2]

Hello, I have a table with an _id column and a parent_capability_id column both with values of type text. 

I am trying to create a custom column that returns 1 if the value in the parent_capability_id column exists in the entire _id column. My goal is to be able to flag (and then remove) any rows where the parent_capability_id does not exist as an _id in the _id column.

To do this, I am using the function:

if List.Contains([_id], [parent_capability_id]) then 1 else null

This produces the error:

Expression.Error: We cannot convert the value "B8769B37-9617-48D5-A..." to type List.
Details:
Value=B8769B37-9617-48D5-A060-4A139B077ABD
Type=[Type]

I have tried to create a list query from the _id column, and then use this in place of the _id column in that same function, but it gives me a cyclical reference error.

I am able to achieve my goal in DAX using the CONTAINS function, but I need to do this in Power Query / m to filter the dataset at that stage.

Any help would be much appreciated.

[amitchandak · Accepted solution]

@d_pbi_2 , Use the last step table name, say it #"change type"

if List.Contains(#"change type"[_id], [parent_capability_id]) then 1 else null

List.Contains and List.ContainsAll: https://youtu.be/oiLTUC1RCbU

[d_pbi_2]

That did the trick! Thank you @amitchandak!