Count Number of Course Attempts Based on Two Columns

[Anonymous]

Hello All,

I am a bit new to Power BI and Power Query so I apologize in advance if this should not be posted here. I tried searching the forum, but was not able to find the answer I was looking for.

In Power Query, I am trying to count the number of course attempts a student has made based on two columns: StudentId and Course. The table looks like this:

StudentId	Course	CourseResult
1	BUS201	FAIL
2	BUS202	FAIL
3	BUS201	PASS
1	BUS201	FAIL
2	BUS202	PASS
1	BUS201	PASS

The result should look like this:

StudentId	Course	CourseResult	Attempts	Result
1	BUS201	FAIL	1	0
2	BUS202	FAIL	1	0
3	BUS201	PASS	1	1
1	BUS201	FAIL	2	0
2	BUS202	PASS	2	1
1	BUS201	PASS	3	1

I have created the result column with conditional formatting. However, I am unsure how I can tackle the ATTEMPTS column in Power Query.

Any help would be greatly appreciated! Thank you in advance.

[Vera_33 · Accepted solution]

Hi @Anonymous 

One way to do, GROUPBY and add Index, paste in Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIKDTYyADHcHD19lGJ1opWMYKJGyKLGyGoDHIODwaJEmIBdLUQ0FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StudentId = _t, Course = _t, CourseResult = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StudentId", Int64.Type}, {"Course", type text}, {"CourseResult", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Result", each if [CourseResult] = "FAIL" then 0 else 1),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"StudentId", "Course"}, {{"allrow", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrow],"Attempts",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"StudentId", "Course", "CourseResult", "Result", "Attempts"}, {"StudentId", "Course", "CourseResult", "Result", "Attempts"})
in
    #"Expanded Custom"