Show Highest Value Per Two Variables (Category) and (Hour)

[mmunozjr5]

Hello,

I would like to create a table or graph that would only show the Hour and Total Count for that hour but for only the highest value for each category. For example, for this scenario, it would show 4 bars or rows in a table as follows: (Dataset enclosed) Thank you!

Call    |  2:00 PM | Total = 2

Email |  1:00 PM | Total = 3

Page  | 9:00 AM | Total = 2

Text   | 8:00 AM |  Total  = 5

Category	Hour	Count
Text	1:00 PM	1
Email	9:00 AM	1
Email	10:00 AM	1
Call	11:00 PM	1
Page	9:00 AM	1
Text	8:00 AM	1
Email	10:00 PM	1
Email	4:00 PM	1
Call	2:00 PM	1
Page	3:00 PM	1
Text	8:00 AM	1
Email	2:00 PM	1
Email	1:00 PM	1
Call	10:00 PM	1
Email	8:00 AM	1
Call	2:00 PM	1
Email	1:00 PM	1
Text	10:00 PM	1
Text	8:00 AM	1
Text	2:00 PM	1
Text	1:00 PM	1
Text	10:00 PM	1
Text	8:00 AM	1
Text	2:00 PM	1
Call	1:00 PM	1
Call	4:00 PM	1
Email	2:00 PM	1
Email	11:00 AM	1
Page	8:00 AM	1
Email	4:00 PM	1
Page	9:00 PM	1
Email	10:00 PM	1
Call	9:00 AM	1
Email	1:00 PM	1
Text	10:00 AM	1
Text	9:00 AM	1
Page	10:00 AM	1
Page	11:00 PM	1
Page	9:00 AM	1
Text	8:00 AM	1

[ryan_mayu · Accepted solution]

@mmunozjr5 

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

[mmunozjr5]

@ryan_mayu Thank you so much for your time and effort in providing me with this solution. Perfect! Thanks!

[ryan_mayu]

you are welcome