Data clean up and merge duplicate data

[dd88]

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

[ddpl]

@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]

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

[dd88 · Accepted solution]

@ddpl  I found the solution here 

Fill up/ down with conditions in Power Query
https://www.youtube.com/watch?v=QV98Jndj5Fo

Thanks again