League Table

[jimrward]

Having a play with Power Tools to try and produce a league table of top scores
Simple data example
Golfer, Date, score
Looking for a league table max score to lowest score where golfer only appears once in the list

 

[alansidman]

Would you supply some sample data for us to work with. Post it using XL2BB. No pictures as they cannot be manipulated.

 

[jimrward]

apologies for the delay here been taking timeout
here is some sample data and the expected result in columns E and F, might be worth including an insight on an additional parameter say Junior/Senior and the possibility of grouping by year and quarter so i can understand the mechanics

Book1
	A	B	C	D	E	F
1	Date	Player	Stableford		Player	Score
2	05/06/2023	jim	24		dave	29
3	05/06/2023	fred	27		jim	28
4	05/06/2023	bob	21		fred	27
5	07/07/2023	dave	29		billy	24
6	07/07/2023	jim	25		bob	21
7	07/07/2023	billy	24
8	07/07/2023	jim	28
Sheet1

 

[lrobbo314]

How about this?

PQ
	A	B	C	D	E	F
1	Date	Player	Stableford		Player	Top Score
2	6/5/2023	jim	24		dave	29
3	6/5/2023	fred	27		jim	28
4	6/5/2023	bob	21		fred	27
5	7/7/2023	dave	29		billy	24
6	7/7/2023	jim	25		bob	21
7	7/7/2023	billy	24
8	7/7/2023	jim	28
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Player"}, {{"Top Score", each List.Max([Stableford]), type number}}),
    Sort = Table.Sort(Group,{{"Top Score", Order.Descending}})
in
    Sort

 

[jimrward]

many thanks for the above help, I would like to include an additional column, the date the score was achieved as follows

power query example.xlsx
	A	B	C	D	E	F	G
1	Date	Player	Stableford		Player	Score	Date Achieved
2	05/06/2022	jim	24		dave	29	07/07/2022
3	05/06/2022	fred	27		jim	28	08/07/2022
4	03/06/2022	bob	21		fred	27	05/06/2022
5	07/07/2022	dave	29		billy	24	07/07/2022
6	07/07/2022	jim	25		bob	21	03/06/2022
7	07/07/2022	billy	24
8	08/07/2022	jim	28
Sheet1

 

[jimrward]

I asked this question earlier in the year and now revisiting for reference and noted that there wasn’t a solution to the last entry above which I had piggybackEd on more in hope than anything else

how to include the date of the high score, when I group by player and grab the max the date column gets lost

i haven’t started a new thread so as not to replicate the pertinent information above

 

[smozgur]

One way I can think, looking up for the Top Score value in the filtered PlayerRows table (added in the Group step), sorting the matching rows by date (desc), so we get the latest max score of the player if there are more than one, then selecting the top row. Then convert the record list to table, etc..

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Player"}, {{"Top Score", each List.Max([Stableford]), type any}, {"PlayerRows", each _, type nullable table[Date = any, Player = any, Stableford = any]}}),
    Lookup = Table.TransformRows(Group, 
                        (r) => Table.First(
                                    Table.Sort(
                                        Table.SelectRows(r[PlayerRows], 
                                            (c) => c[Stableford] = r[Top Score]),
                                        {{"Date", Order.Descending}}
                                    )
                                )),
    ToTable = Table.FromList(Lookup, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Result = Table.Sort(Table.ExpandRecordColumn(ToTable, "Column1", {"Player", "Stableford", "Date"}, {"Player", "Top Score", "Date"}), {{"Top Score", Order.Descending}})
in
    Result

 

[JGordon11]

A similar approach:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Stableford", Int64.Type}}),
    Table_Sort = (tbl as table)=> Table.Sort(tbl,{{"Stableford", Order.Descending}, {"Date", Order.Descending}}),
    Group = Table.Group(ChangeType, {"Player"}, {{"Record", each Table_Sort(_){0}}}),
    Result = Table_Sort(Table.FromRecords(Group[Record]))
in
    Result

 

[jimrward]

@smozgur @JGordon11 many thanks for the excellent solutions both
Just so I can understand further the Table.Sort you both refer to is this entered freehand in the Advanced View editor, as when I try to sort anything I get sorted_rows rather than sorted_table in Excel PQ
Once I understand this I will hopefully apply to the huge data set I have, the example above is just a simple data table purely for research purposes