Forum Discussion

dd88's avatar
dd88
Icon for Post Patron rankPost Patron
3 years ago
Solved

Data clean up and merge duplicate data

PowerBi desktop app.

 

I have employee data from 2 different systems.  System 1 has EmployeeID and System 2 has Payroll EmployeeID.  However the Employee IDs are different in both systems, setup in separate legacy systems.

 

The goal is gap analysis, and a data cleanup to match and connect the data between the different systems.  Usually a unique employeeID would be the ‘key’ used across both systems.  However at this stage, with this dataset I am not sure what the key will be.

 

In PowerBI, an excel speadsheet is the data source, tab 1 contains System 1 data and tab 2 contains system 2 data.

 

I have Append the tables, and  an example of the result data is

 

 

I read about Remove Duplicate Values

 

https://learn.microsoft.com/en-us/power-query/working-with-duplicates

 

I dont think I want to remove duplicate values Full name, as then System 2 EmployeeID data will be deleted.

 

I think what I want to achieve is to merge the data, and then remove duplicates,  and the resulting data example is

 

 

How can I achieve this? And what is the best way to achieve this

 

TIA

 

 

 

Need Help

3 Replies

  • ddpl's avatar
    ddpl
    Icon for Solution Sage rankSolution Sage
    3 years ago

    dd88  try this

     

    1.Append the both the table.

    2.Asscending the the column first name or any one

    3.Then select the column system1 empl id column and Fill down the data

    4.Now select system2 payroll empl id column and Fill up the data

    5.Finally remove duplicate.

    • dd88's avatar
      dd88
      Icon for Post Patron rankPost Patron
      3 years ago

      Thanks ddpl   thats a neat feature.

       

      After I performed the Fill, I verified the data.

       

      What has occurred, is the data does not align and match correctly with the name record.

       

      For an employee, there maybe no data (blank) at all for system 1 or system 2.

       

      The fill results are:

      for example, system 2 the 1st record is null, and has filled the data incorrectly. System 1 the last record, the next employee does not have data, the result it has filled with the previous employee data.

       

       

      any ideas?

      TIA