Tournament Results

[jimrward]

This is part 3 of my quest to learn how to use power tools
I have called this generically Tournament results so that it might be useful in other scenarios
Ok here we go Power Tools gurus

Over a month a competitor can complete up to 3 rounds for a competition which has a few categories for the results, overall, Ladies, Vets, Juniors, Junior Ladies
The data is a rolling log by date, I can group by name and sum the scores for a total to give me Name and total, but I want to include the round scores on each line as opposed to vertically
Here is some sample raw data, the set is incomplete butr hopefully enough data for a solution

Book1
	A	B	C	D	E	F
1	Date	Name	Cat1	Cat2	Round	Score
2	01/04/2023	Jim	Vet		1	5
3	01/04/2023	Bill	Vet		1	7
4	01/04/2023	Bob			1	4
5	01/04/2023	Fred		Junior	1	8
6	02/04/2023	Fred		Junior	2	8
7	03/04/2023	Jim			2	6
8	03/04/2023	Alan			1	11
9	03/04/2023	Debs	Ladies		1	11
10	04/04/2023	Kirsty	Ladies	Junior	1	12
11	04/04/2023	Kim	Ladies		1	5
12	04/04/2023	Jim	Vet		3	2
13	04/04/2023	Bob			2	7
14	04/04/2023	Bill	Vet		2	10
Sheet1

This is what I would like to achieve, overall might be easier as it doesnt need the category logic, I havent decided on sorting yet when there are rounds not completed, looking for the method first

Book1
	H	I	J	K	L	M
1	Overall	Name	R1	R2	R3	Total
2		Jim	5	6	2	13
3		Bill	7	10		17
4		Bob	4	7		11
5		Fred	8	8		16
6		Alan	11			11
7		Debs	11			11
8		Kirsty	12			12
9		Kim	5			5
Sheet1

And now by a single category, I have listed all sections but the logic will be the same with an edit for the category

Book1
	H	I	J	K	L	M
11	Cat
12	Vet	Name	R1	R2	R3	Total
13		Jim	5	6	2	13
14		Bill	7	10		17
15
16	Cat
17	Ladies	Name	R1	R2	R3	Total
18		Debs	11			11
19		Kirsty	12			12
20		Kim	5			5
21
22	Cat
23	Junior	Name	R1	R2	R3	Total
24		Fred	8	8		16
25		Kirsty	12			12
Sheet1

Finally using 2 categories for Junior Ladies

Book1
	H	I	J	K	L	M
27	Cat
28	Junior	Name	R1	R2	R3	Total
29	Ladies	Kirsty	12			12
Sheet1

 

[smozgur]

My approach by using M code directly, which I believe is the most powerful tool in Power Tools.
To me, Power Query with interface-only usage is similar to using Excel with functions only. While it is still powerful with the interface, M makes everything more doable in Power Query, just like how it feels good in Excel with VBA.

Assumptions:
R1, R2, and R3 rounds for corresponding 1, 2, and 3 round values.
If R3 exists for a name then it means R1 and R2 must also exist for the same name.
Source table name is "Table1"

Step names in the provided M codes shortly explain what's going on. So, you can see what's happening by simply clicking on the steps after copying & pasting these codes into the blank queries in the Advanced View in the Power Query interface.

Source table as it is provided in the question:

1235916
	A	B	C	D	E	F
1	Date	Name	Cat1	Cat2	Round	Score
2	4/1/2023	Jim	Vet		1	5
3	4/1/2023	Bill	Vet		1	7
4	4/1/2023	Bob			1	4
5	4/1/2023	Fred		Junior	1	8
6	4/2/2023	Fred		Junior	2	8
7	4/3/2023	Jim			2	6
8	4/3/2023	Alan			1	11
9	4/3/2023	Debs	Ladies		1	11
10	4/4/2023	Kirsty	Ladies	Junior	1	12
11	4/4/2023	Kim	Ladies		1	5
12	4/4/2023	Jim	Vet		3	2
13	4/4/2023	Bob			2	7
14	4/4/2023	Bill	Vet		2	10
Data

Overall:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Round", Int64.Type}, {"Score", Int64.Type}}),
    SetRoundNames = Table.AddColumn(ChangeTypes, "RoundName", each "R" & Text.From([Round])),
    RemoveUnnecessaryColumns = Table.RemoveColumns(SetRoundNames,{"Date", "Cat1", "Cat2", "Round"}),
    PivotRoundColumn = Table.Pivot(RemoveUnnecessaryColumns, List.Distinct(RemoveUnnecessaryColumns[RoundName]), "RoundName", "Score", List.Sum),
    RoundCount = Table.ColumnCount(PivotRoundColumn) - 1,

    AddMissingRounds = if RoundCount < 3 then
        let
            addR3 = Table.AddColumn(PivotRoundColumn, "R3", each 0),
            addR2 = if RoundCount < 2 then Table.AddColumn(addR3, "R2", each 0) else addR3,
            Result = addR2
        in
            Result
        else PivotRoundColumn,
    ReorderColumns = Table.ReorderColumns(AddMissingRounds,{"Name", "R1", "R2", "R3"}),
    ReplaceNullRounds = Table.ReplaceValue(ReorderColumns,null,0,Replacer.ReplaceValue, {"R1", "R2", "R3"}),
    AddTotal = Table.AddColumn(ReplaceNullRounds, "Total", each [R1]+[R2]+[R3])
in
    AddTotal

1235916
	A	B	C	D	E
1	Name	R1	R2	R3	Total
2	Alan	11	0	0	11
3	Bill	7	10	0	17
4	Bob	4	7	0	11
5	Debs	11	0	0	11
6	Fred	8	8	0	16
7	Jim	5	6	2	13
8	Kim	5	0	0	5
9	Kirsty	12	0	0	12
Overall

By single category:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Name", type text},  {"Round", Int64.Type}, {"Score", Int64.Type}}),
    UnpivotCategories = Table.Unpivot(ChangeTypes, {"Cat1", "Cat2"}, "Attribute", "Value"),
    RenameCategoryColumn = Table.RenameColumns(UnpivotCategories,{{"Value", "Category"}}),
    SetRoundNames = Table.AddColumn(RenameCategoryColumn, "RoundName", each "R" & Text.From([Round])),
    RemoveUnnecessaryColumns = Table.RemoveColumns(SetRoundNames,{"Date", "Round", "Attribute"}),
    PivotRoundColumn = Table.Pivot(RemoveUnnecessaryColumns, List.Distinct(RemoveUnnecessaryColumns[RoundName]), "RoundName", "Score", List.Sum),
    RoundCount = Table.ColumnCount(PivotRoundColumn) - 2,

    AddMissingRounds = if RoundCount < 3 then
        let
            addR3 = Table.AddColumn(PivotRoundColumn, "R3", each 0),
            addR2 = if RoundCount < 2 then Table.AddColumn(addR3, "R2", each 0) else addR3,
            Result = addR2
        in
            Result
        else PivotRoundColumn,
    ReorderColumns = Table.ReorderColumns(AddMissingRounds,{"Name", "Category", "R1", "R2", "R3"}),
    ReplaceNullRounds = Table.ReplaceValue(ReorderColumns,null,0,Replacer.ReplaceValue, {"R1", "R2", "R3"}),
    AddTotal = Table.AddColumn(ReplaceNullRounds, "Total", each [R1]+[R2]+[R3]),
    GroupByCategory = Table.Group(AddTotal, {"Category"}, {{"GroupedByCategory", each _, type table [Name=nullable text, Category=text, R1=nullable number, R2=nullable number, R3=nullable number, Total=number]}}),
    RemoveBlanks = Table.SelectRows(GroupByCategory, each ([Category] <> "")),
    ExpandColumns = Table.ExpandTableColumn(RemoveBlanks, "GroupedByCategory", {"Name", "R1", "R2", "R3", "Total"})
in
    ExpandColumns

1235916
	A	B	C	D	E	F
1	Category	Name	R1	R2	R3	Total
2	Vet	Bill	7	10	0	17
3	Vet	Jim	5	0	2	7
4	Ladies	Debs	11	0	0	11
5	Ladies	Kim	5	0	0	5
6	Ladies	Kirsty	12	0	0	12
7	Junior	Fred	8	8	0	16
8	Junior	Kirsty	12	0	0	12
ByCategory

By multiple categories:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Round", Int64.Type}, {"Score", Int64.Type}}),
    SetRoundNames = Table.AddColumn(ChangeTypes, "RoundName", each "R" & Text.From([Round])),
    FilteredRowsByMultiCats = Table.SelectRows(SetRoundNames, each ([Cat1] <> null and [Cat2] <> null)),
    RemoveUnnecessaryColumns = Table.RemoveColumns(FilteredRowsByMultiCats,{"Date", "Cat1", "Cat2", "Round"}),
    PivotRoundColumn = Table.Pivot(RemoveUnnecessaryColumns, List.Distinct(RemoveUnnecessaryColumns[RoundName]), "RoundName", "Score", List.Sum),
    RoundCount = Table.ColumnCount(PivotRoundColumn) - 1,

    AddMissingRounds = if RoundCount < 3 then
        let
            addR3 = Table.AddColumn(PivotRoundColumn, "R3", each 0),
            addR2 = if RoundCount < 2 then Table.AddColumn(addR3, "R2", each 0) else addR3,
            Result = addR2
        in
            Result
        else PivotRoundColumn,
    ReorderColumns = Table.ReorderColumns(AddMissingRounds,{"Name", "R1", "R2", "R3"}),
    ReplaceNullRounds = Table.ReplaceValue(ReorderColumns,null,0,Replacer.ReplaceValue, {"R1", "R2", "R3"}),
    AddTotal = Table.AddColumn(ReplaceNullRounds, "Total", each [R1]+[R2]+[R3])
in
    AddTotal

1235916
	A	B	C	D	E
1	Name	R1	R2	R3	Total
2	Kirsty	12	0	0	12
ByMultiCategory

I think these could help as a starting point. Many steps could be structured by using other M functions and methods that might be more effective or even the whole structure for each query could be redesigned by using alternative logic, for example, I would actually create a function query that could be reused for AddMissingRounds step and likely shorten each query. There is really not a single way to do things in Power Query. This is just my initial approach above.

 

[jimrward]

Wow awesome many thanks for that I will have a play over the weekend and see if I can get my head around it

 

[smozgur]

Wow awesome many thanks for that I will have a play over the weekend and see if I can get my head around it

I hope it helps. Please let us know if any questions.