Forum Discussion
Duplicate Employee ID and Employee Names in data set - How can I clean this data?
Hi PowerQueryFTW, powerquerybabe , powerquerytony PowerQueryQstn powerquerytony1
I am hoping someone can advise me on the correct way to deal with this data problem I have.
Employee Table - 1000 rows in total.
Employee ID - 911 distinct , 828 unique - 172 employee IDs are included in the data more than once, 83 of which are distinct.
Employee Name - 911 distinct, 982 unique. 18 records contain duplicate names - 9 of which are distinct
What is the best way to approach this issue and please provide step by step guide notes if possible.
Thanks
Hi thanks for reaching out i suppose you wnt to clean the data and load so for that
- Load Data: Import the Employee Table into Power Query Editor.
- Remove Exact Duplicates: Select Employee ID and Employee Name columns, and remove exact duplicates.
- Group by Columns: Group by Employee ID and Employee Name to review and reconcile duplicate entries.
- Standardize Data: Clean inconsistencies (e.g., trim spaces, standardize cases) for Employee Name.
Finally load the cleaned the data into Power BI and model it and start building the report
if the suggestion helped please accept this as the solution and give a kudos
Hi AJ2025
I would sujjest that remove duplicates based on the entire table ( all columns ) ,
after that if there are duplicates in the employee id column and employee name for the same id is different then you should check from the source and respective ( data engineers )
or at last ideal pratice would be remove duplicates from the customer id column ( unique = distinct ) as it is the primary key column of the table ( and all values must be unique ) for that.
Regards
Govind Sapkade ( Data Analyst , Power BI PL 300 Certified , MS Fabric Enthusiast )
Linkdin : www.linkedin.com/in/govind-sapkade-845104225
Youtube : http://www.youtube.com/@govind_dataanalyst
8 Replies
- Akash_Varuna
Super User
Hi thanks for reaching out i suppose you wnt to clean the data and load so for that
- Load Data: Import the Employee Table into Power Query Editor.
- Remove Exact Duplicates: Select Employee ID and Employee Name columns, and remove exact duplicates.
- Group by Columns: Group by Employee ID and Employee Name to review and reconcile duplicate entries.
- Standardize Data: Clean inconsistencies (e.g., trim spaces, standardize cases) for Employee Name.
Finally load the cleaned the data into Power BI and model it and start building the report
if the suggestion helped please accept this as the solution and give a kudos
HI All
Thank yu for all of your suggestions. I have not had chance to try any of these out yet. I will let you know when I do 😉
- ajaybabuinturi
Hi AJ2025 ,
Can you provide the sample data so that I will get you step by step cleansing process.
Thanks - govind_021
Hi AJ2025
I would sujjest that remove duplicates based on the entire table ( all columns ) ,
after that if there are duplicates in the employee id column and employee name for the same id is different then you should check from the source and respective ( data engineers )
or at last ideal pratice would be remove duplicates from the customer id column ( unique = distinct ) as it is the primary key column of the table ( and all values must be unique ) for that.
Regards
Govind Sapkade ( Data Analyst , Power BI PL 300 Certified , MS Fabric Enthusiast )
Linkdin : www.linkedin.com/in/govind-sapkade-845104225
Youtube : http://www.youtube.com/@govind_dataanalyst - Chewdata
Responsive Resident
hey!
I would suggest that you ask your data engineer / the source' product owner what kind of table this is.Looking at your screenshot and speaking from experience It might be that it is nog purely a employeetable, but for example a employee + function table. For example: The employees in your duplicate screenshots have different functions. Employees that through time have held multiple functions within the company. depending on how this is registered within the source application they might appear multiple times with different ID's.
if this is the case then it is really your business question that determines how to clean your data. Do you want to keep the first or the last record. Or maybe there is a currentFunction field on which you can filter the data.
v-saisrao-msftCommunity Support
Hi AJ2025,
Thank you reaching out to the Microsoft Forum community.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
- v-saisrao-msft
Hi AJ2025,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you. - v-saisrao-msft
Hi AJ2025
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
