Create a manager's flag in M-Query

[Solution_b]

Create a PBI M-query for a 'Yes' or 'No' flags for Is_employee_a manager?

I have a table called Employee with column Employee_id, Manager_employee_id and Period.

The logic is to identify and flag ("Yes" or "No") based on Employee_Id if the Employee_Id is in the Manager_employee_Id list in the same period?

Pay attention to Employee_Id G123 who was not a manager in previous month (Period 30/11/2023) but has become a manager in the period 31/12/2023. This should flag "No" for 30/11/2023 but "Yes" in 31/12/2023. The same applies to B123 who was a manager in 30/11/2023 but no longer a manager in 31/12/2023. It should flag "Yes" for 30/11/2023 but "No" for 31/12/2023.

see below table

Employee_Id	Manager_employee_Id	Period
A123	C123	30/11/2023
B123	E123	30/11/2023
C123	B123	30/11/2023
D123	E123	30/11/2023
E123		30/11/2023
F123	C123	30/11/2023
G123		30/11/2023
A123	C123	31/12/2023
B123	E123	31/12/2023
C123	E123	31/12/2023
D123	E123	31/12/2023
E123	G123	31/12/2023
F123	C123	31/12/2023
G123		31/12/2023

From the above table to this table below

Employee_Id	Manager_employee_Id	Period	Flag
A123	C123	30/11/2023	No
B123	E123	30/11/2023	Yes
C123	B123	30/11/2023	Yes
D123	E123	30/11/2023	No
E123		30/11/2023	Yes
F123	C123	30/11/2023	No
G123		30/11/2023	No
A123	C123	31/12/2023	No
B123	E123	31/12/2023	No
C123	E123	31/12/2023	Yes
D123	E123	31/12/2023	No
E123	G123	31/12/2023	Yes
F123	C123	31/12/2023	No
G123		31/12/2023	Yes

Please note: I don't want a calculated column for this.

[jgeddes · Accepted solution]

You could try something like...

Table.AddColumn(PREVIOUSSTEP, "Flag", each if List.Contains(List.RemoveItems(List.Distinct(Table.SelectRows(PREVIOUSSTEP, (x)=> x[Period] = [Period])[Manager_employee_Id]), {" "}), [Employee_Id]) then "Yes" else "No")

[Solution_b]

Thank you.
That solved it.