Forum Discussion
Create a manager's flag in M-Query
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.
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")
2 Replies
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")Thank you.
That solved it.
