Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
1 year ago
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.   ...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago

    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
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
1 year ago
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
    xpand
AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
1 year ago

Hi 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"

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    1 year ago

    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
    • AllanBerces's avatar
      AllanBerces
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      Hi AlienSx thank you very much for the reply working perfectly