Power Query Check if a value exists in another table list

Hi tomperro, 

You can follow the belwo steps

1. Load both tables into Power Query

2. Merge Queries

In EmployeeTable:

• Merge with EmployeeDetailsTable using both EmployeeName and EmployeeID (to ensure uniqueness).
• Use Left Outer Join.
• Expand only QualificationCode.

3. Add Custom Columns for Qualification Flags

Now in the expanded version of EmployeeTable, add custom columns like this:

Qualification1 (987 r 654)= if [QualificationCode] = 987 or [QualificationCode] = 654 then 1 else 0
Qualification2 (444)= if [QualificationCode] = 444 then 1 else 0

4. Transform the Qualification columns data type to Whole number.

5. Group to Aggregate Flags per Employee

Now, group by EmployeeID and EmployeeName, and use Max aggregation on Qualification1 and Qualification2.

I am attaching a .pbix file for your reference.
https://drive.google.com/file/d/1RdCxhpSFEHK3GyvrdLly4K4UcRA9BTUI/view 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.