Check if value in column A exists in column B for a specific id

[Twizxup]

Hi All,

This is probably way easier than I think but I'm struggling to create a calculation that, for a given ID, checks if the value in column A also exists in column B.

For example:

Employee ID 123 returns TRUE because the Actual Code exists in the Forecasted Code column for that same ID

Employee ID 345 returns FALSE because the Actual Code does not exist in the Forecasted Code column for that same ID

Employee ID 567 returns FALSE because the Actual Code does not exist in the Forecasted Code column for that same ID

Employee ID 789 returns TRUE because the Actual Code exists in the Forecasted Code column for that same ID

Employee ID	Actual Code	Forecasted Code	FLAG
123	LOANMAN	LOANMAN	TRUE
345	CLDARCH	MFDPAQT	FALSE
567	CLDARCH	CLDMFAM	FALSE
567	CLDARCH	MFDPAMT	FALSE
567	CLDARCH	MFDPAQT	FALSE
567	CLDARCH	MFDPLRM	FALSE
567	CLDARCH	MFDPMFP	FALSE
789	DEVEXPR	CLDCORP	TRUE
789	DEVEXPR	CLDFINT	TRUE
789	DEVEXPR	DEVEXPR	TRUE
789	DEVEXPR	P2PCONT	TRUE

Any help would be greatly appreciated! Thank you in advance.

[vanessafvg]

see example attached

[vanessafvg]

where are you creating this if statment and how

using power query 

just create a conditional column if  a = b then 1 else 0

[Twizxup]

I can't figure out how to create it in PowerQuery (althugh that'd be ideal for my purpose) so I'm assuming it'd have to be a measure.

Unfortunately that doesn't work because I don't need the values to equal each other at the row-level, but rather for the value in "Actual Code" column to be present anywhere (for the specified Employee ID) in the "Forecasted Code" column. Employee ID 789 is a good example.

I don't think this is possible in Power Query

[vanessafvg · Accepted solution]

thats also ok, you can do that first, and return 0 or 1, and then group by the employee, max(flag), and if it has a  value, then max will surface it on the group by

[Twizxup]

This works great, thank you! The only thing I changed about your suggestion is that I concatenated the Employee ID and the Actual Code, and then I grouped by that instead.

It appears to work exactly as I had hoped -- thank you!