Join and union two large datasets with both similar and different column headers

[mlim0806]

Hi Community, 

Apologies for the large made-up dataset, I tried this workflow with a much smaller sample size and it seemed to work, but when I applied to my real dataset with over 40k rows, it didn't. 

I have two data inputs, one is an employee roster with a list of unique individuals and basic employee info, the other is a list of employee dream jobs and may include duplicate employee ID's since an individual can submit multiple dream jobs. I want the final output to include all employees and columns from the roster, a column of Y/N whether the employee "has a dream job", and if they do, each job will be listed on its own row with the columns populated from the employee roster. What it's doing now, is listing all rows of jobs with blanks cells because that data is not populating from the roster, and adding a separate row from the roster. This then assigns both a Y and N value for an employee and an employee must either be a yes or no, can't be both.

Attached is the workflow, and a sample excel of the outputted data on one tab and the desired output on the other. Any help or guidance is much appreciated!

[Qiu]

@mlim0806 
We can apply the Summarize tool for the list of employee goals by counting how many goals each individual has set.

[mlim0806]

thank you @Qiu in addition to seeing percentage of total headcount, final output is to include all columns from both reports, and all rows from the goals report with the data populated in from the employee roster so that I'm able to slice by other fields. For those individuals that have multiple goals, I would like to see a row for each one individually. 

This output will be used in Power Bi to create tables and charts. 

[mlim0806 · Accepted solution]

I was able to correct this issue by just joining by one field, the employee ID.