need help in Lookup function

[Anonymous]

Hi All,

I have data like below table 

I want to create "Is Predicted" Filed based on the below condition by using M query 

• If "Actual value" is present in "Possible value" then yes else No

Sample data:

ID	Actual Value	Possible Value	Is Predicted
ID1	A	B	Yes
ID1	A	A	Yes
ID1	A	C	Yes
ID2	E	B	No
ID2	E	A	No
ID2	E	C	No
ID3	C	B	Yes
ID3	C	A	Yes
ID3	C	C	Yes

[v-cherch-msft]

Hi @Anonymous 

You may create a calculated column with DAX as below:

Column = 
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Possible Value] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    )
RETURN
    IF ( 'Table'[Actual Value] IN a, "Yes", "No" )

Regards,

[Anonymous]

Thanks, v-cherch-msft

Is it possible in M query editor? 

[Zubair_Muhammad · Accepted solution]

@Anonymous 

As a custom column, you can use

"Changed Type" is just the previous step's name. You have to change it according to your last step in the Query Editor

=let myID=[ID] in
let mylist=Table.SelectRows(#"Changed Type",each [ID]=myID)[Possible Value]
in
if List.Contains(mylist,[Actual Value]) then "Yes" else "No"