Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
9:00 AM | 1 | |
10:00 AM | 1 | |
Call | 11:00 PM | 1 |
Page | 9:00 AM | 1 |
Text | 8:00 AM | 1 |
10:00 PM | 1 | |
4:00 PM | 1 | |
Call | 2:00 PM | 1 |
Page | 3:00 PM | 1 |
Text | 8:00 AM | 1 |
2:00 PM | 1 | |
1:00 PM | 1 | |
Call | 10:00 PM | 1 |
8:00 AM | 1 | |
Call | 2:00 PM | 1 |
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 |
2:00 PM | 1 | |
11:00 AM | 1 | |
Page | 8:00 AM | 1 |
4:00 PM | 1 | |
Page | 9:00 PM | 1 |
10:00 PM | 1 | |
Call | 9:00 AM | 1 |
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 |
Solved! Go to Solution.
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
Proud to be a 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
Proud to be a Super User!
@ryan_mayu Thank you so much for your time and effort in providing me with this solution. Perfect! Thanks!
you are welcome
Proud to be a Super User!
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |