Forum Discussion
Training Data: Assigned Courses vs Course Completions
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.
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
2 Replies
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
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.
