Training Data: Assigned Courses vs Course Completions

[Tolzie]

Hi there,

I'm new to both Power Query & Power BI, and could really use some help.

My objective is to create a table in which there are three columns: Column A is the assigned courses; Column B shows the employees who have been assigned a training (this is determined by certain criteria listed below); Column C shows the completion date for each row of data— in other words, "Completed" if completed, or "Null" if no completion data exists.

My data sets include an Employee Roster, Attendance Data, and Assignment Criteria. See examples below:

Employee Roster 

Employee ID	Employee Name	Employee Status
54321	Ruiz, Lucy	Manager
13468	Lewis, Jaime	Manager

Attendance Data

Employee ID	Course Name	Date of Completion
54321	Safety Course	3/14/22
54321	Manager Course	2/22/22
13468	Manager Course	2/22/22

Assignment Criteria

Course Name	Employee Status
Safety Course	Manager
Manager Course	Manager

I have created a Query which lists every person who matches the Assignment Criteria above.

Now what I am looking for is a table that looks like this:

Assigned Courses (taken from the Assignment Criteria table)	Employee ID (taken from the Assignment Criteria table)	Date of Completion (from Attendance Data table)
Safety Course	54321	3/14/22
Safety Course	13468	null
Manager Course	54321	2/22/22
Manager Course	13468	2/22/22

I feel like I'm so close, yet so far! Please help.

[Tolzie]

@Anonymous Amazing! Thank you very much for sending the file. At first I didn't pick up a couple of steps you have performed in Power Query, but once I did- it worked!

Thank you.

[Anonymous · Accepted solution]

Hi @Tolzie 

Table Assignment Criteria join Employee Roster on Employee Status, then join Attendance Data on the first two columns, file attached for your reference