# Excel BI Challenge Workout 197

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 197 —
(Solutions in any language are also welcome for Excel Challenges)

List the qualifying player from each group on the basis of

1. Highest average in that group
2. A player should have played a minimum of 10 matches

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

## LinkedIn Post by: Henriette Hamer

Maybe I’m getting the gist of the MAP-funtion?

`MAP(N3:N5;LAMBDA(x;FILTER(\$B\$2:\$B\$13;(\$A\$2:\$A\$13=x)*(\$K\$2:\$K\$13=MAX(FILTER(\$K\$2:\$K\$13;(\$A\$2:\$A\$13=x)*(\$L\$2:\$L\$13=TRUE)))))))`

``````=LET(
_tbl, SORT(HSTACK(A2:D13, D2:D13 / C2:C13), {1, 5}, {1, -1}),
Sortbl, FILTER(_tbl, CHOOSECOLS(_tbl, 3) >= 10),
DROP(
REDUCE(
"",
UNIQUE(TAKE(_tbl, , 1)),
LAMBDA(a, v,
VSTACK(
a,
LET(
_ftbl, FILTER(Sortbl, CHOOSECOLS(Sortbl, 1) = v),
FILTER(_ftbl, TAKE(_ftbl, , -1) = LARGE(TAKE(_ftbl, , -1), 1))
)
)
)
),
1,
-3
)
)
``````

``````let
Source = Table.SelectRows(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], each [Matches] > 9),
Sol = Table.Group(Source, {"Group"}, {{"Player", each
let
a = Table.AddColumn(_, "Tot", each [Total Points]/[Matches]),
b = Table.Max(a, "Tot")[Player]
in b}})
in
Sol
``````

Qualifying players w/ #powerquery. #bitanbit #powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = [Table.Group](http://table.group/)(Table.AddColumn(Table.SelectRows(Source, each [Matches] >= 10), "Avg", each [Total Points] / [Matches]), {"Group"}, {{"Player", each List.Last(Table.Sort(_, "Avg")[Player])}})
in
Solution
``````

This ugly formula is by no means Dynamic, and is hardcoded to there being only 3 groups: A, B, C. I blame Excel for having issues with MAXIFS and calculated arrays! ``````=LET(A, A2:D13,
B, INDEX(A,0,3),
C, INDEX(A,0,4)/B*(B>9),
D, INDEX(A,0,1),
E, C=MAX(IF(D="A",C,0)),
F, C=MAX(IF(D="B",C,0)),
G, C=MAX(IF(D="C",C,0)),
H, TAKE(A,,2),
I, VSTACK(FILTER(H,E),FILTER(H,F),FILTER(H,G)),
I)
``````

## LinkedIn Post by: محمد حلمي

`=LET(c,C2:C13,r,SORTBY(A2:D13,c>9,-1,D2:D13/c,-1),l,UNIQUE(A2:A13),HSTACK(l,XLOOKUP(l,TAKE(r,,1),INDEX(r,,2))))`

## LinkedIn Post by:Alejandra Horvath CPA, CGA

My PQ 😊
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Grouped = Table.Group(Source, {"Group"}, {{"Count", each
let
a = Table.AddColumn( Table.SelectRows( _, each [Matches] >9 ), "Average", each [Total Points]/ [Matches])
in
Table.Sort(a, {{"Average", Order.Descending}} ) [Player]{0} }})
in
Grouped
``````

`=LET(a,FILTER(A2:D13,C2:C13>=10),b,UNIQUE(A2:A13),d,INDEX(a,,4)/INDEX(a,,3),e,INDEX(a,,2),f,INDEX(a,,1),HSTACK(b,MAP(b,LAMBDA(x,FILTER(e,(d)=MAX(FILTER(d,(f=x))))))))`

Hi,
insert this formula in H3 and then drag down.

ENGLISH VERSION

`=LET(mean,\$D\$2:\$D\$13/\$C\$2:\$C\$13,x,MAX(IF(\$A\$2:\$A\$13=G3,IF(\$C\$2:\$C\$13>=10,mean))),INDEX(\$B\$2:\$B\$13,MATCH(G3&x,\$A\$2:\$A\$13&mean,0)))`

ITALIAN VERSION

`=LET(mean;\$D\$2:\$D\$13/\$C\$2:\$C\$13;x;MAX(SE(\$A\$2:\$A\$13=G3;SE(\$C\$2:\$C\$13>=10;mean)));INDICE(\$B\$2:\$B\$13;CONFRONTA(G3&x;\$A\$2:\$A\$13&mean;0)))`

``````let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Group"}, {{"Contagem", each
[
a = Table.SelectRows(_, each [Matches] > 9),
b = List.Max(Table.AddColumn(a, "media", each [Total Points]/[Matches])[media]),
c = Table.SelectRows(a, each ([Total Points]/[Matches]) = b )
][c]
}}),
res = Table.ExpandTableColumn(gp, "Contagem", {"Player"})
in
res
``````

## LinkedIn Post by: محمد حلمي

Try #1

`=LET(e,A2:A13,c,C2:C13,l,UNIQUE(e),HSTACK(l,MAP(l,LAMBDA(a,LET(v,FILTER(HSTACK(B2:D13,D2:D13/c),(e=a)*(c>10)),r,TAKE(v,,-1),XLOOKUP(MAX(r),r,TAKE(v,,1)))))))`

Power Query
``````let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = Table.AddColumn(Origen, "Averege", each [Total Points]/[Matches]),
b = Table.SelectRows(a, each [Matches] > 9),
c = Table.Group(b, {"Group"}, {{"Aux", each _, type table}}),
d = Table.AddColumn(c, "Player", each Record.Field(Table.Max([Aux], "Averege"), "Player")),
e = Table.RemoveColumns(d,{"Aux"})
in
e
``````

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FilteredRows = Table.SelectRows(Source, each [Matches] >= 10),
Result = Table.Group(
{"Group"},
{
{"Player", each let a = Table.Sort(_, {"Points per Match", Order.Descending}) in a{0}[Player]}
}
)
in
Result
``````

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FilteredRows = Table.SelectRows(Source, each [Matches] >= 10),
AddedCustom = Table.AddColumn(FilteredRows, "Points per Match", each [Total Points] / [Matches], type number),
SortedRows = Table.Buffer(Table.Sort(AddedCustom, {{"Group", Order.Ascending}, {"Points per Match", Order.Descending}})),
GroupedRows = Table.Group(SortedRows, {"Group"}, {{"MaxPlayer", each _{0}[Player]}})
in
GroupedRows
``````

`=LET(m,A2:D13,d,HSTACK(m,INDEX(m,,4)/INDEX(m,,3)),b,SORT(UNIQUE(INDEX(d,,1))),HSTACK(b,MAP(b,LAMBDA(a,@CHOOSECOLS(SORT(FILTER(d,(INDEX(d,,1)=a)*(INDEX(d,,3)>9),""),5,-1),2)))))`

`=LET(g,A2:A13,u,UNIQUE(g),m,C2:C13,HSTACK(u,XLOOKUP(u,g&m/D2:D13/(m>9),B2:B13,,1)))`

## LinkedIn Post by:Oscar Mendez Roca Farell

hi everyone, my proposal solution:

`=LET(_a, A2:A13,_c, C2:C13,_u, UNIQUE(_a),_m, (D2:D13/_c)*(_c>9)*(_a=TOROW(_u)), HSTACK(_u, TOCOL(REPT(B2:B13, 1/(_m=BYCOL(_m,LAMBDA(c,MAX(c))))), 3)))`

``````=LET(datas,FILTER(A2:D13,C2:C13>=10),scores,BYROW(datas,LAMBDA(a,CHOOSECOLS(a,4)/CHOOSECOLS(a,3))),groups,UNIQUE(CHOOSECOLS(datas,1)),
`=LET(a,A2:A13,b,C2:C13,c,D2:D13,d,UNIQUE(a),e,MAP(d,LAMBDA(x,MAX(FILTER(c/b,(a=x)*(b>=10))))),HSTACK(d,MAP(e,LAMBDA(y,FILTER(B2:B13,c/b=y)))))`