I have 20 golfers, each playing 18 holes. I can't seem to figure out the formula, in power query, that I need to find the single minimum score (no duplicates) per each hole. Any help would be greatly appreciated.
Sounds like a "Group By" transformation to me. If you group by hole (and player probably, not sure) and choose a Count as aggregation, you will be able to remove those with more than 1 record (duplicates).
I don't want to delete or change anything. I want to know the unique (single value) low score on that hole. I am new to power query and haven't been able to figure this out.
Can you upload a sample file so we can see what's in there?
I don't play golf ... My knowledge on this is full of holes. 😉
If you bring up the queries and open tblDay1 you will see the query. Each player has 18 scores, one score on each of the 18 holes. The end result is to find the single lowest unique (no duplicates) score value of all the players on each of the holes. The maximum number of results is 18 - 1 value per hole. Any other questions, feel free to ask. Thanks for your involvement. Here's a link to the file;
https://www.dropbox.com/s/mejh61fjcq7hukz/NewBoeingTournament.xlsm?dl=0
Sal
Here is a picture of the query:
Hi Sal,
I added 4 steps to your query, please check if this is what you need:
let
Source = Excel.CurrentWorkbook(){[Name="tblDay1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Player", type text}, {"Sex", type text}, {"SCGA", type number}, {"Hcp", Int64.Type}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"F9", Int64.Type}, {"F Net", Int64.Type}, {"10", Int64.Type}, {"11", Int64.Type}, {"12", Int64.Type}, {"13", Int64.Type}, {"14", Int64.Type}, {"15", Int64.Type}, {"16", Int64.Type}, {"17", Int64.Type}, {"18", Int64.Type}, {"B9", Int64.Type}, {"B Net", Int64.Type}, {"Gross", Int64.Type}, {"Net", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Player", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"SCGA"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Player", "Sex", "Hcp", "B9", "B Net", "Gross", "Net"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Attribute] <> "F Net" and [Attribute] <> "F9"),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Hole #"}, {"Value", "Score"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Player", "Sex", "Hcp", "Score", "Hole #", "B9", "B Net", "Gross", "Net"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"B9", "B Net", "Gross", "Net"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Hole #", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", { "Hole #"}, {{"Min", each List.Min([Score]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Hole #"}, #"Grouped Rows", {"Hole #"}, "Grouped Rows", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(#"Merged Queries", "Min Value for current Hole", each if [Grouped Rows][Min]{0}=[Score] then [Score] else null),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Grouped Rows"})
in
#"Removed Columns2"
Hi Catalin,
Not bad for being full of holes. Almost, I need the min to be the lowest non-duplicated value. In english, the single lowest score. The answer per hole will be one unique score or none.
When I was trying to figure this out, I thought I saw a min function, in M, that gave a single unique value and minN function for the number of min values found. Did I misunderstand?
Sal
Hi Sal,
Have you tried the query provided?
I continued your query with this step:
#"Grouped Rows" = Table.Group(#"Changed Type1", { "Hole #"}, {{"Min", each List.Min([Score]), type number}}),
This step returns a list of 18 holes with the minimum score for them, no matter which player achieved that score.
I can see no duplicates here, so I don't understand your point.
After this step, I added a #"Merged Queries" step with the following logic:
While each hole has a single minimum score, there can be more than one layer that may have achieved that minimum score.
Therefore, the last step that creates the column "Min Value for current Hole" will identify all the players that achieved the minimum score for each hole. If you filter out the null values in this column, you may see one ore more players with the minimum score for that hole.
If there is something else you need, please provide a manual example of how the final result should look like, it's much easier than learning the entire game.
Hi Catalin,
Yes I tried the query and found several players on a hole that had the same min value. That's why I said "almost" in my reply. Anyway, The file I attached shows the result that is wanted. I did this in VBA, now attempting the upgrade.
Sal
If more than 1 player have the same minimum score, which one should be removed from the list? Based on what criteria or calculation?
Hello Catalin,
Nothing nor anybody to be removed. Referring back to the example, just below the net scores are the "Skins". A skin is either a birdie or a low net score. I have done birdies, now trying to do low nets to arrive at the skins. The skins are shown by player and denote the type and shows each players total number of skins and the total for the round. Hope this helps.
Sal
Doesn't help, just brings more confusion. Remember, I know nothing about this game and its rules.
You mentioned that " I have done birdies, now trying to do low nets to arrive at the skins.", but I have no idea what a "bird" or an "eagle" or even "nets" means in this game.
Based on the query structure you provided, what do you expect to get? Below is an extract of results, showing the min Score for current hole.
| Player | Sex | Hcp | Score | Hole # | Min Value for current Hole |
| Bauman, Dick | M | 33 | 6 | 1 | null |
| Bauman, Dick | M | 33 | 7 | 2 | null |
| Bauman, Dick | M | 33 | 8 | 3 | null |
| Bauman, Dick | M | 33 | 4 | 4 | null |
| Bauman, Dick | M | 33 | 5 | 5 | null |
| Bauman, Dick | M | 33 | 4 | 6 | null |
| Bauman, Dick | M | 33 | 6 | 7 | null |
| Bauman, Dick | M | 33 | 5 | 8 | null |
| Bauman, Dick | M | 33 | 4 | 9 | null |
| Bauman, Dick | M | 33 | 7 | 10 | null |
| Bauman, Dick | M | 33 | 4 | 11 | null |
| Bauman, Dick | M | 33 | 5 | 12 | null |
| Bauman, Dick | M | 33 | 6 | 13 | null |
| Bauman, Dick | M | 33 | 5 | 14 | null |
| Bauman, Dick | M | 33 | 4 | 15 | null |
| Bauman, Dick | M | 33 | 5 | 16 | null |
| Bauman, Dick | M | 33 | 4 | 17 | 4 |
| Bauman, Dick | M | 33 | 8 | 18 | null |
| Bauman, Maria | F | 36 | 6 | 1 | null |
| Bauman, Maria | F | 36 | 9 | 2 | null |
| Bauman, Maria | F | 36 | 5 | 3 | null |
| Bauman, Maria | F | 36 | 4 | 4 | null |
| Bauman, Maria | F | 36 | 6 | 5 | null |
| Bauman, Maria | F | 36 | 7 | 6 | null |
| Bauman, Maria | F | 36 | 5 | 7 | null |
| Bauman, Maria | F | 36 | 7 | 8 | null |
| Bauman, Maria | F | 36 | 3 | 9 | 3 |
| Bauman, Maria | F | 36 | 6 | 10 | null |
| Bauman, Maria | F | 36 | 4 | 11 | null |
| Bauman, Maria | F | 36 | 7 | 12 | null |
| Bauman, Maria | F | 36 | 7 | 13 | null |
| Bauman, Maria | F | 36 | 7 | 14 | null |
| Bauman, Maria | F | 36 | 5 | 15 | null |
| Bauman, Maria | F | 36 | 6 | 16 | null |
| Bauman, Maria | F | 36 | 7 | 17 | null |
| Bauman, Maria | F | 36 | 8 | 18 | null |
| Brown, Mark | M | 11 | 6 | 1 | null |
| Brown, Mark | M | 11 | 6 | 2 | null |
| Brown, Mark | M | 11 | 4 | 3 | 4 |
| Brown, Mark | M | 11 | 3 | 4 | 3 |
| Brown, Mark | M | 11 | 5 | 5 | null |
| Brown, Mark | M | 11 | 5 | 6 | null |
| Brown, Mark | M | 11 | 5 | 7 | null |
| Brown, Mark | M | 11 | 6 | 8 | null |
| Brown, Mark | M | 11 | 4 | 9 | null |
| Brown, Mark | M | 11 | 6 | 10 | null |
| Brown, Mark | M | 11 | 4 | 11 | null |
| Brown, Mark | M | 11 | 7 | 12 | null |
| Brown, Mark | M | 11 | 6 | 13 | null |
| Brown, Mark | M | 11 | 4 | 14 | 4 |
| Brown, Mark | M | 11 | 4 | 15 | null |
| Brown, Mark | M | 11 | 7 | 16 | null |
| Brown, Mark | M | 11 | 5 | 17 | null |
| Brown, Mark | M | 11 | 7 | 18 | null |
Hi Catalin,
Sorry for all the confusion. Let me start fresh. Looking at the net scores section. Lets say that it shows that I have 11 Salesman and 18 days of sales. For whatever reason I want to find the worst single salesman on each day. This is shown as the red numbers.
In the power query, we have players and score per hole. I want to find the lowest single player score on each hole.
You questioned birdie, low net and skins - names are not important to our task. I did the birdies in another query that I will merge with this one, when it is completed, and get the "skins".
I have attached a Pivot table of the query and red bold the wanted results .
Add a new column with this formula (formula is in red):
= Table.AddColumn(#"Added Custom", "Is Single Min", (rw)=> Table.RowCount(Table.SelectRows(#"Added Custom", each [Min Value for current Hole]<>null and [#"Hole #"]=rw[#"Hole #"]))=1 and rw[Min Value for current Hole]<>null, type logical)
This column returns TRUE for those 4 values in red from your example.
