Power Query: Convert electives into a number of requirements completed

[colinhahn]

I am developing a report for a training program that has a series of electives.

The completion data from our learning management system has roughly the following format:

Person Name	Phase	Course ID	Completion Status
Jane Doe	1	101	Yes
John Smith	1	101	No
John Smith	3	301	Yes
Jane Doe	3	302	No
John Smith	3	303	Yes

I want to produce a pivot table with Person Name as the row, Phase 1 and Course ID as the columns (in that order), and a measure for Completion Status in the values area. I am currently using the measure formula =FIRSTNONBLANK(Table[Completion Status], ""), so it reports out the Yes or No as appropriate. (I am not worried about totals for this project).

The challenge is that there are some elective bundles. For instance, a user might need to complete any 3 of the 310-grouping of courses (311, 312, 313, 314, and 315).

I am trying to figure out how to transform my data so the elective bundles report out as how many courses completed, rather than which courses are completed, for the electives. For instance, the output I would like to see from the table above would be:

	Phase 1	Phase 3
Person	101	310 One of Three Completed	310 Two of Three Completed	310 Three of Three Completed
Jane Smith	Yes	Yes
John Doe	No	Yes	Yes

The semi-easy workaround is to create a measure that counts how many completions are done for that person from that set of courses. However, because it is a separate measure, all the non-electives would be listed first and then all the electives. In the actual user journey, the participant's order goes back and forth between non-electives and electives, so I'd like the report to follow the course numbering sequence.

To do that, I think I need to transform my data so that, for the first completion of person X in bundle Y, the course title is transformed from the actual course code to "310 One of 3 Completed", for the second completion of that person in that bundle, "310 Two of Three Completed", etc. I can't quite figure out how to do the logic to do that counting. There might be a way with index columns and self joins, or there may be a counting function that I can use with conditions.

Can anyone help me think through this problem?

 

[colinhahn]

In case anyone finds this thread in a search later on, I found a solution using the GROUP BY function in power query. I created a grouping code column, so all the electives within a single bundle had a shared grouping code. I then filtered the table to only show the rows that had a grouping code and were completed. Then, I grouped by person, phase, and grouping code, aggregating the count of how many modules were completed. That gave me a table that looked like:

Person Name | Phase | Grouping Code | Count

From there, I appended that table to the table that existed in the step immediately before creating the grouping code column. That gave me all the data I needed to work with, and I was able to clean up the resulting table to produce the report.