Forum Discussion
Show Highest Value Per Two Variables (Category) and (Hour)
you can do this in pq
let
Source = Table,
#"Grouped Rows" = Table.Group(Source, {"Category", "Hour"}, {{"value", each List.Sum([Count]), type nullable number}}),
Custom1 = Table.AddColumn(#"Grouped Rows","Rank",each Table.RowCount(Table.SelectRows(#"Grouped Rows",(x)=>x[value]>[value] and x[Category]=[Category]))+1),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Rank] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rank"})
in
#"Removed Columns"pls see the attachment below
Super User
you can do this in pq
let
Source = Table,
#"Grouped Rows" = Table.Group(Source, {"Category", "Hour"}, {{"value", each List.Sum([Count]), type nullable number}}),
Custom1 = Table.AddColumn(#"Grouped Rows","Rank",each Table.RowCount(Table.SelectRows(#"Grouped Rows",(x)=>x[value]>[value] and x[Category]=[Category]))+1),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Rank] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rank"})
in
#"Removed Columns"
pls see the attachment below
