Excel BI Challenge Workout 197

Workouts Excel BI Challenge
21

LinkedIn Post by:
Brian Julius

My hashtag#powerquery

solution 
let
Source = Table.SelectRows( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], each [Matches] >= 10),
AddAvgPts = Table.AddColumn(Source, "AvgPts", each [Total Points]/[Matches]),
Group = Table.Group(AddAvgPts, {"Group"}, {{"All", each _, type table [Group=text, Player=text, Matches=number, Total Points=number, AvgPts=number]}, {"MaxAvgPts", each List.Max([AvgPts]), type number}}),
Expand = Table.ExpandTableColumn(Group, "All", {"Player", "AvgPts"}, {"Player", "AvgPts"}),
FilterNClean = Table.SelectColumns( Table.SelectRows(Expand, each [AvgPts] = [MaxAvgPts]), {"Group", "Player"})
in
FilterNClean
22

LinkedIn Post by:
Daniel G.
 

=LET(s,SORT(FILTER(HSTACK(A2:D13,D2:D13/C2:C13),C2:C13>9),{1,5},{1,-1}),g,TAKE(s,,1),u,UNIQUE(g),
VSTACK(A1:B1,HSTACK(u,XLOOKUP(u,g,INDEX(s,,2)))))
23

LinkedIn Post by:
Bo Rydobon

M-Code 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Group = Table.Group(Source, "Group", {"Player", each Table.Sort(Table.AddColumn(_,"P", each if [Matches]>9 then -[Total Points]/[Matches] else null),"P")[Player]{0} })
in
 Group
24

LinkedIn post by:
JvdV

I suppose one would leave out a group if no player has played over 9 matches:

=LET(a,C2:C13,b,SORT(FILTER(HSTACK(A2:B13,D2:D13/a),a>9),3,-1),c,UNIQUE(TAKE(b,,1)),HSTACK(c,VLOOKUP(c,b,2,0)))

25

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

Try #2

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

26

LinkedIn Post by:
Pieter de B.

=LET(a,A2:D13,b,FILTER(a,INDEX(a,,3)>9),c,SORTBY(TAKE(b,,2),INDEX(b,,4)/INDEX(b,,3),-1),g,TAKE(c,,1),CHOOSEROWS(c,XMATCH(UNIQUE(g),g)))

27

LinkedIn Post by:
John Jairo V.

Hi to all!

:white_check_mark:

One option could be:

=LET(g,A2:A13,m,C2:C13,a,D2:D13/m,FILTER(A2:B13,a=MAP(g,LAMBDA(x,MAX(a*(m>9)*(g=x))))))

Blessings!

28

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

Try #3

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

29

LinkedIn Post by:
Surendra Reddy

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

30

LinkedIn Post by:
John Jairo V.

Hi to all!
:white_check_mark:

Another option could be:

=LET(u,UNIQUE(A2:A13),m,C2:C13,HSTACK(u,VLOOKUP(u,SORTBY(A2:B13,-(m>9)*D2:D13/m),2,)))

Blessings!

31

LinkedIn Post by:
Surendra Reddy

=LET(a,A2:A13,b,C2:C13,d,D2:D13,e,UNIQUE(a),HSTACK(e,XLOOKUP(MAP(e,LAMBDA(x,MAX(FILTER(d/b,(b>=10)*(a=x))))),d/b,B2:B13)))

32

LinkedIn Post by:
Kris Jaganah

=LET(a,A2:A13,b,B2:B13,c,C2:C13,d,D2:D13/c,e,UNIQUE(a),HSTACK(e,XLOOKUP(MAP(e,LAMBDA(x,MAX((a=x)*d*(c>9)))),d,b)))

33

LinkedIn Post by:
Rick Rothstein

=LET(a,A2:A13,c,C2:C13,d,D2:D13,u,UNIQUE(a),HSTACK(u,XLOOKUP(MAP(u,LAMBDA(u,MAX(FILTER(d/c,(a=u)*(c>9))))),d/c,B2:B13)))

34

LinkedIn Post by:
Bo Rydobon

=LET(g,A2:A13,u,UNIQUE(g),m,C2:C13,HSTACK(u,MAP(u,LAMBDA(a,XLOOKUP(99,D2:D13/m/(m>9)/(g=a),B2:B13,,-1)))))

35

LinkedIn Post by:
Tolga Demirci

=LET(q;D2:D13/C2:C13;w;UNIQUE(A2:A13);HSTACK(w;XLOOKUP(MAP(w;LAMBDA(a;LARGE(IFERROR(IF(C2:C13>=10;MAP(A2:A13;q;LAMBDA(x;y;XLOOKUP(a;x;y)));"");"");1)));q;B2:B13)))

36

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

=LET(m, C2:C13, t, T2:T13, g, A2:A13, c, t/IF(m<10, 100,m), HSTACK(UNIQUE(g), MAP(UNIQUE(g), LAMBDA(x, INDEX(SORT(FILTER(HSTACK(A2:B13,c), g=x),3,-1),1,2)))))

37

LinkedIn post by:
Hussain Ali Nasser
 

=LET(
_table,A2:D13,
_averages,D2:D13/C2:C13,
_averagetable,HSTACK(_table,_averages),
_filteredtable,FILTER(_averagetable,CHOOSECOLS(_averagetable,3)>10),
_sortedtable,SORT(_filteredtable,{1,5},{1,-1}),
_groups,UNIQUE(A2:A13),
_lookup,XLOOKUP(_groups,TAKE(_sortedtable,,1),INDEX(_sortedtable,,2)),
HSTACK(_groups,_lookup)
)
38

LinkedIn Post by:
Miguel Angel Franco García

Envio mi soluccion

=LET(a;UNICOS(A2:A13);b;FILTRAR(B2:D13;(C2:C13>=10)*(A2:A13=INDICE(a;1)));c;FILTRAR(B2:D13;(C2:C13>=10)*(A2:A13=INDICE(a;2)));d;FILTRAR(B2:D13;(C2:C13>=10)*(A2:A13=INDICE(a;3)));e;APILARH(a;APILARV(BUSCARX(PROMEDIO(INDICE(b;;3));INDICE(b;;3);INDICE(b;;1);;1);BUSCARX(PROMEDIO(INDICE(c;;3));INDICE(c;;3);INDICE(c;;1);;1);BUSCARX(PROMEDIO(INDICE(d;;3));INDICE(d;;3);INDICE(d;;1);;1)));e)

39

Power Query

let
	Source  = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
	Average = Table.AddColumn ( Source, "Avg", each [Total Points] / [Matches] ),
	Return  = Table.Group (
		Average,
		"Group",
		{
			"Player",
			each Table.Max ( Table.Sort ( _, { "Avg", 1 } ), { ( f ) => f[Matches] >= 10, "Avg" } )[
				Player
			]
		}
	)
in
	Return

Qualifying Player.xlsx (24.0 KB)