Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Number of Course Attempts Based on Two Columns

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:

 

StudentIdCourseCourseResult
1BUS201FAIL
2BUS202FAIL
3BUS201PASS
1BUS201FAIL
2BUS202PASS
1BUS201PASS

 

The result should look like this:

 

StudentIdCourseCourseResultAttemptsResult
1BUS201FAIL10
2BUS202FAIL10
3BUS201PASS11
1BUS201FAIL20
2BUS202PASS21
1BUS201PASS31

 

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"