Check off required criteria

[jimrward]

My quest for knowledge continues so here is one for the gurus

To simplify my problem I will try to describe it using a mini golf competition of 9 holes on a par 3 golf course each player is assigned a unique id, hole number, score, so 9 rows for each ID, hole(1-9), score
I can group by uniqueid and sum the score to get total

What I would also like to do is see if any player gets a 2 and 3 and 4 and 5 in their set of 9 holes as a bonus competition
they can get multiples of each required hole score but must tick the box for 2,3,4,5 at least once, so if they don’t get a 2 then it’s a fail
I have tried creating custom columns, IS2,IS3,IS4,IS5 posting 1 for yes in each column but can’t workout how to check for 1,1,1,1 from a grouping
I will post some data later from my pc when I get home

 

[jimrward]

After a sleep on it I think I have a solution albeit not very elegant
data set

Book1
	A	B	C
1	ID	Hole	Score
2	1	1	6
3	1	2	4
4	1	3	3
5	1	4	2
6	1	5	7
7	1	6	5
8	1	7	8
9	1	8	6
10	1	9	6
11	2	1	4
12	2	2	4
13	2	3	3
14	2	4	3
15	2	5	4
16	2	6	4
17	2	7	3
18	2	8	3
19	2	9	3
20	3	1	3
21	3	2	4
22	3	3	3
23	3	4	2
24	3	5	4
25	3	6	5
26	3	7	4
27	3	8	6
28	3	9	3
Sheet1

code, C1 to C4 conditions, on grouping TC1 to TC4 total condtions met for the round, last column concatenate TCn together and eliminate ones with a zero
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Hole", Int64.Type}, {"Score", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "C1", each if [Score]=2 then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "C2", each if [Score]=3 then 1 else 0),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "C3", each if [Score]=4 then 1 else 0),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "C4", each if [Score] =5 then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom3", {"ID"}, {{"Total", each List.Sum([Score]), type nullable number}, {"TC1", each List.Sum([C1]), type number}, {"TC2", each List.Sum([C2]), type number}, {"TC3", each List.Sum([C3]), type number}, {"TC4", each List.Sum([C4]), type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"TC1", type text}, {"TC2", type text}, {"TC3", type text}, {"TC4", type text}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type1", "AllTC", each [TC1]&[TC2]&[TC3]&[TC4]),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Jackpot", each if Text.Contains([AllTC],"0") then "" else "Jackpot")
in
#"Added Custom5"

End result

Book1
	A	B	C	D	E	F	G	H
1	ID	Total	TC1	TC2	TC3	TC4	AllTC	Jackpot
2	1	47	1	1	1	1	1111	Jackpot
3	2	31	0	5	4	0	0540
4	3	34	1	3	3	1	1331	Jackpot
Table1