Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
85 | |
75 | |
71 | |
69 | |
55 |
User | Count |
---|---|
98 | |
97 | |
92 | |
78 | |
70 |