Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tolzie
Frequent Visitor

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 IDEmployee NameEmployee Status
54321Ruiz, LucyManager
13468Lewis, JaimeManager

 

Attendance Data

Employee IDCourse NameDate of Completion
54321Safety Course3/14/22
54321Manager Course2/22/22
13468Manager Course2/22/22

 

Assignment Criteria

Course NameEmployee Status
Safety CourseManager
Manager CourseManager

 

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 Course543213/14/22
Safety Course13468null
Manager Course543212/22/22
Manager Course134682/22/22

 

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

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

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

Vera_33_0-1647300381956.png

 

View solution in original post

2 REPLIES 2
Tolzie
Frequent Visitor

@Vera_33 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.

Vera_33
Resident Rockstar
Resident Rockstar

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

Vera_33_0-1647300381956.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors