Log In
Register
Home
Excel Tables
May 27, 2019
Offline
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.
See Answer
November 8, 2013
Offline
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).
Excel Tables
May 27, 2019
Offline
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.
November 8, 2013
Offline
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. 😉
Excel Tables
May 27, 2019
Offline
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/mejh......xlsm?dl=0
Sal
Excel Tables
May 27, 2019
Offline
Here is a picture of the query:
November 8, 2013
Offline
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"
Excel Tables
May 27, 2019
Offline
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
November 8, 2013
Offline
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.
Excel Tables
May 27, 2019
Offline
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
November 8, 2013
Offline
If more than 1 player have the same minimum score, which one should be removed from the list? Based on what criteria or calculation?
Excel Tables
May 27, 2019
Offline
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
November 8, 2013
Offline
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 |
Excel Tables
May 27, 2019
Offline
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 .
November 8, 2013
Offline
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.
Excel Tables
May 27, 2019
Offline
Hello again Catalin,
Thank you for the formula. As I said before, I am new to structured formulas, and was wondering if you could explain the formula in English. Also in verifying the example did your query include the min column also? Thanks, Sal
November 8, 2013
Offline
Here is the full query then:
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"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns2", "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)
in
#"Added Custom1"
The formula takes into account the previous column added "Min Value for current Hole". You can see it in the formula if you take a closer look.
What the formula does:
Count the rows from entire table (from previous step) where "Min Value for current Hole" is not empty for the current hole. If this count is =1 and current row "Min Value" is not null, it will return TRUE.
Excel Tables
May 27, 2019
Offline
Hello Catalin,
Can you tell me what rw means in the formula.
November 8, 2013
Offline
rw refers to current row context.
Table.AddColumn(#"Removed Columns2", "Is Single Min", (rw)=> Table.RowCount(
is equal to:
Table.AddColumn(#"Removed Columns2", "Is Single Min", each Table.RowCount(
1 Guest(s)
