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
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.
Hello Catalin,
Can you tell me what rw means in the formula.
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(
Catalin - you've been great, thanks for your patience and all your assistance. Sal
