Get just one date from multiple rows with the same date

[Anonymous]

Hi all,

I have a table like the one below, where I have hireDate and terminationDate broken up by individuals, identifiable by their empID. The problem is, a person could have worked in multiple departments as denoted in the departmentCommonName column, in which case they will have multiple rows. This will create multiple rows with the same hireDate and terminationDate for the same person. I am using these dates to calculate attrition, so I want to add in another column which will pull out only one of these pairs of dates for the employee, that way when I count for attrition I only count the person once. So in the data below, we can see that employee 206 has 5 sets of hireDate and terminationDate which are the same, but I want a column which will show this pair only once for employee 206, and then the same for all of the other employees. Any suggestions?

Thank you!

[Anonymous · Accepted solution]

I've found a way to solve it for my specific case, matching up the term date with the last day with client date (since they should only match when the employee has been termed anyway). Thanks for the replies all!

[parry2k]

@Anonymous may be easier is that in query editor, make copy of your table, remove all unwanted columns, keep emp name, hire and terminate date and remove duplicate rows. At the end  you will have unique row for each employee and then it will be much easier to work from there.

[Anonymous]

@parry2k Thanks for your reply! This sounds good, but the problem is that I will need attrition to eventually be broken up by departmentCommonName and empLocation, but if I remove those columns in another table I won't know which people were in which group when I try to add them up. Any suggestions?

[parry2k]

@Anonymous well if you don't have termination date by department, you cannot achieve that anyhow so not sure how you are planning to do it?

[Anonymous]

@parry2k I do have termination date by department actually, it is shown as the lastDayWithClient column. Sorry for not explaining it thoroughly! What is most important here for me right now is actually empLocation, where I need to be able to count and break out attrition by empLocation which has multiple categories. This is why I wanted to get a column which shows the hire and termination date only once per person so that once I broke it out by location, it would not overcount attrition.

[v-diye-msft]

Hi @Anonymous 

You might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.