Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
3 years ago
Solved

Table Group with multiple criteria/keep the entry relative to the max date

Hello all, I am posting again on grouping tables. I have a new requirement. I need to group now by product by several criteria, summing the Amount, but for other fields I need to only keep the entry...
  • ValeriaBreve's avatar
    3 years ago

    I worked around this by ranking by date while grouping, so that then I can filter for the rank corresponding to the latest date. Code below.

     

    Does somebody have a better option or is this correct? Thanks!


    //Group by Concatenation
    Grouped = Table.Group(#"Changed Type" , {"Product"}, {{"AllRows", each _, type table},{"Amount", each List.Sum([Amount])}}),

     

    //Declare a function that adds a Rank column to a table
    RankFunction = (tabletorank as table) as table =>
    let
    SortRows = Table.Sort(tabletorank,{{"Date", Order.Descending}}),
    AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
    in
    AddIndex,
    //Apply that function to the AllRows column
    AddedRank = Table.TransformColumns(Grouped, {"AllRows", each RankFunction(_)}),
    #"Expanded Index" = Table.ExpandTableColumn(AddedRank, "AllRows", { "Rank", "Type", "Date", "Product Code"},{"Rank", "Type", "Date", "Product Code"})