Forum Discussion
Solved
Total and Rank
Hi good day can anyone help me on my PQ, what we required is the Total sum of trade and Rank it from Highest to Lowest. if the Rank 1 is two date let say 5/14 and 5/13 used the latet date.
RESULT
| DATE | Direct and InDirect | Com | Sum Trade | Total Trade | Rank |
| 5/14/2025 | E&I Artisan | E&I | 8 | 113 | 1 |
| 5/14/2025 | Banksman | Scaff | 42 | 113 | 1 |
| 5/14/2025 | Marker | Mech | 63 | 113 | 1 |
| 5/9/2025 | E&I Artisan | E&I | 7 | 78 | 2 |
| 5/9/2025 | Banksman | Scaff | 33 | 78 | 2 |
| 5/9/2025 | Marker | Mech | 38 | 78 | 2 |
| 6/12/2025 | E&I Artisan | E&I | 3 | 74 | 3 |
| 6/12/2025 | Banksman | Scaff | 48 | 74 | 3 |
| 6/12/2025 | Marker | Mech | 23 | 74 | 3 |
| 5/7/2025 | E&I Artisan | E&I | 7 | 71 | 4 |
| 5/7/2025 | Banksman | Scaff | 36 | 71 | 4 |
| 5/7/2025 | Marker | Mech | 28 | 71 | 4 |
| 5/8/2025 | E&I Artisan | E&I | 4 | 68 | 5 |
| 5/8/2025 | Banksman | Scaff | 34 | 68 | 5 |
| 5/8/2025 | Marker | Mech | 30 | 68 | 5 |
| 5/6/2025 | E&I Artisan | E&I | 6 | 56 | 6 |
| 5/6/2025 | Banksman | Scaff | 37 | 56 | 6 |
| 5/6/2025 | Marker | Mech | 13 | 56 | 6 |
hello, AllanBerces . Create new (blank) query and replace everything inside with this code:
let // your code Source = #"Complete_List Actual POB", your_group = Table.Group( Source, {"DPR_DATE", "Combine Direct and InDirect", "BP's"}, {{"Sum Trade", each Table.RowCount(_), Int64.Type}} ), // my code my_group = Table.Group( your_group, "DPR_DATE", { {"tbl", (x) => x}, {"Total Trade", (x) => List.Sum(x[Sum Trade])} } ), rank = Table.AddRankColumn( my_group, "Rank", {{"Total Trade", Order.Descending}, {"DPR_DATE", Order.Descending}}, [RankKind = RankKind.Ordinal] ), xpand = Table.ExpandTableColumn(rank, "tbl", List.RemoveItems(Table.ColumnNames(your_group), {"DPR_DATE"})) in xpand
4 Replies
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], group = Table.Group( Source, "DATE", { {"tbl", (x) => x}, {"Total trade", (x) => List.Sum(x[Sum Trade])} } ), rank = Table.AddRankColumn( group, "Rank", {{"Total trade", Order.Descending}, {"DATE", Order.Descending}}, [RankKind = RankKind.Ordinal] ), xpand = Table.ExpandTableColumn(rank, "tbl", List.RemoveItems(Table.ColumnNames(Source), {"DATE"})) in xpandHi AlienSx thank you very much how can i add that to my current query
let
Source = #"Complete_List Actual POB",
#"Grouped Rows" = Table.Group(Source, {"DPR_DATE", "Combine Direct and InDirect", "BP's"}, {{"Sum Trade", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"hello, AllanBerces . Create new (blank) query and replace everything inside with this code:
let // your code Source = #"Complete_List Actual POB", your_group = Table.Group( Source, {"DPR_DATE", "Combine Direct and InDirect", "BP's"}, {{"Sum Trade", each Table.RowCount(_), Int64.Type}} ), // my code my_group = Table.Group( your_group, "DPR_DATE", { {"tbl", (x) => x}, {"Total Trade", (x) => List.Sum(x[Sum Trade])} } ), rank = Table.AddRankColumn( my_group, "Rank", {{"Total Trade", Order.Descending}, {"DPR_DATE", Order.Descending}}, [RankKind = RankKind.Ordinal] ), xpand = Table.ExpandTableColumn(rank, "tbl", List.RemoveItems(Table.ColumnNames(your_group), {"DPR_DATE"})) in xpand
