compare different values in a table

[Valeria]

Hello!

I need help finding a solution. I have a table containing a list of employees named "Employee List" with two columns: "month" and "name."

I need to determine, for each month:

• The number of employees who left and joined.
• The list of names for those who left and joined.

I initially separated them into two different tables and then merged them. However, this approach is not the most straightforward to update. I want to keep everything in one table to retain historical data.

Thanks fo anyone will read this! 😊

Valeria

[AlienSx]

Hello, @Valeria group data by month to get a table with 2 columns: month and list of employees. Then create a list of rows with (Table.ToRecords or Table.ToRows - upon your liking). Then run List.Generate to calculate what you want using List.Difference(current, previous) to get those who joined and List.Difference(current, next) to get those who left.   

[Valeria]

Hello @AlienSx, thanks for responding. 

I think I don't need to group. Below example of the data. 

 

can you explain better how I use table.toRecord/ToRows and List. Generate? I am quite new to PowerBi.

Thanks a lot

Valeria

[AlienSx]

Hello, @Valeria you did not show the result you expected to see in the end so I created columns with ee names as lists (joined and left).

let
    Source = your_table,
    g = Table.Group(Source, "Month", {{"e", each _[Name]}}), 
    rows = List.Buffer(Table.ToRecords(g)),
    gen = List.Generate(
        () => 
            [i = 0, 
            r = rows{0} & 
                [joined = {}, 
                n_joined = 0, 
                left = List.Difference(rows{0}[e], try rows{1}[e] otherwise {}), 
                n_left = List.Count(left)]],
        (x) => rows{x[i]}? <> null,
        (x) => 
            [i = x[i] + 1, 
            r = rows{i} & 
                [joined = List.Difference(rows{i}[e], rows{i - 1}[e]),
                n_joined = List.Count(joined),
                left = try List.Difference(rows{i}[e], rows{i + 1}[e]) otherwise {},
                n_left = List.Count(left)]],
        (x) => x[r]
    ),
    z = Table.FromRecords(gen)
in
    z

[Martin_D]

Assuming the following definitions:

• A joined employee is an employee that occurs in a monthly employee snapshot and does not occur in any earlier monthly employee snapshot.
• A left employee is an employee that does not exisit in a monthly employee snapshot but did exist in the previous month's monthly employee snapshot.

The following approach would not account for leaves and rejoins, i.e. only the earliest join and the latest leave per employee is listed:

• Join date for each employee is the table, grouped by the employee column, with the min month aggregaction operation in the second column https://learn.microsoft.com/en-us/power-query/group-by . Sort by month and you have the joins for each month.
• Number of joins per month is the above table, grouped by month, with aggregation operation count employees.
• Leave date for each employee is the table, grouped by the employee column, with the max month aggregaction operation in the second column. Then delete all rows for the latest month because these employees are still in the organization. Sort by month and you have the leaves for each month.
• Number of leaves per month is the above table, grouped by month, with aggregation operation count employees.

Do you need to consider leaves and rejoins?

[Valeria]

Hello Martin, I don't have any dates in my table, just name and month. I need to know the list of names left and the ones joined.