Grouping and counting number of Hogwarts Houses by Points-earning Students

[Jimbo1511]

Hi there,

Say I have the below list of Points awarded at Hogwarts in a given month, along with the Student who earned them. Gryffindor has 6 unique Students earing Points, Slytherin 3, Ravenclaw 2 and Hufflepuff 1.
Does anyone know how I can group and count the Houses by the number of unique Students earning Points that month?

Desired output table:

House size (Points-earning Students)	Count of Houses
1 student	1
2 students	1
3 or more students	2

Data set:

Date	House	Points	Student
6-Mar	Slytherin	60	Draco
7-Mar	Gryffindor	40	Lee
7-Mar	Slytherin	90	Vincent
9-Mar	Gryffindor	10	Harry
10-Mar	Hufflepuff	20	Cedric
11-Mar	Gryffindor	20	Ginny
12-Mar	Slytherin	70	Pansy
13-Mar	Gryffindor	10	Neville
13-Mar	Gryffindor	70	Hermione
13-Mar	Gryffindor	70	Ron
16-Mar	Ravenclaw	40	Luna
16-Mar	Ravenclaw	50	Padma
18-Mar	Gryffindor	70	Ron
19-Mar	Ravenclaw	40	Luna
19-Mar	Ravenclaw	50	Padma
21-Mar	Slytherin	90	Vincent
22-Mar	Ravenclaw	50	Padma
23-Mar	Slytherin	90	Vincent
24-Mar	Gryffindor	40	Lee
25-Mar	Hufflepuff	20	Cedric
26-Mar	Slytherin	70	Pansy
27-Mar	Gryffindor	10	Neville

[Jimbo1511]

Thanks so much @jennratten ! Makes good sense and gives me exactly what I need. Cheers

[jennratten]

You're welcome!

[jennratten · Accepted solution]

Hello - this will produce the requested result.  You can group by house to get a count of students, then assign those counts to house sizes and finally group again by house size to get a count of houses by house size.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdLPC4IwFAfw/2Vng1ymeS7QQ0UYdJEOYz5psJ6xfuF/36YrSmZ6GRt8+L7H3stzEk42TBGP7GV9O4ESqO/hVB8rxXhFjl5OIksSVZelwKIyj8CYNcCP+A6JDTgI5IC3BsWuGN+olClVN8afWpTey1LCRZ/6QQ1aQqEEb5XvimpUIhBtFHU0FRmzY3i1Ztbb0xYeQkr4w5qsFNRZVDjosgpb8v7vjD0AuWTPz1/ekfWTedt4cbZmMVwpHq7kIp1K1B8zW0pHJM1GJQWD20bnY5aEuja7M3/qXO3f+R9f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, House = _t, Points = _t, Student = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"House", type text}, {"Points", Int64.Type}, {"Student", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"House"}, {{"House Size", each let i = List.Count ( List.Distinct ( [Student] ) ) in if i = 1 then "1 Student" else if i = 2 then "2 Students" else "3 or More Students" , type text}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"House Size"}, {{"Count of Houses", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows1"