Aggregate or Remove Rows based on Employee Numbers

[Anonymous]

I have a table with employee information including a start and end date. However, the table has multiple rows for employees that have left. For example, one row would just have the hire date and the second row would have a begining and end date. I want to remove the extra rows that every employee has one row which has the begining and end date. Is there a way to aggregatye the rows using unique employee IDs or to delete any duplicate rows that don't have a termination date? Here is what my current data looks like:

 

Here the first row for the employee doesn't show a temination date but the second row did. I would like this to just be the bottom highlighted row.

Thank you for your help.

[jgeddes · Accepted solution]

You can do this in Power Query by counting the occurrences of the employee number and then filtering out any employee numbers that have two occurrences but no Last Work Date.

the code would look like

// Add column that counts occurences of employee number and assigns a "discard" value to employee numbers with 2 occurences and null LastWork Date
#"Added Custom" = Table.AddColumn(#"YourTablePreviousStep", "Count", each if List.Count(Table.SelectRows(#"Changed Type", (A) => [EA_Emp_N] = A[EA_Emp_N])[EA_Emp_N]) = 2 and [EE_LastWorkDayDt] = null then "Discard" else "Keep"),
// Filters Table to Keep disired rows
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Count] = "Keep")),
// Deletes the filter column that was added
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})

[Anonymous]

Thank you!