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 relative to the max date:

 

 

I have this for now to give me the summing and max date:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Amount", each List.Sum([Amount])},{"Max Update Date", each List.Max([Date])}})

 

How do I tell Powerquery to only keep, for [Product Code] and [Type], the data relative to Max Date?

 

Thanks a lot for your help!

Kind regards

Valeria

Need Help
  • ValeriaBreve's avatar
    ValeriaBreve
    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"})

3 Replies

  • pfarias's avatar
    pfarias
    Icon for Advocate II rankAdvocate II
    3 years ago

    In this case, you should group by Product and Type.

     

      #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Product", "Type"}, 
    {{"Amount", each List.Sum([Amount])}, {"Max Update Date", each List.Max([Date])}}
  )
    • ValeriaBreve's avatar
      ValeriaBreve
      Icon for Post Partisan rankPost Partisan
      3 years ago

      Hello, this does not work unfortunately, because you will get a row by each single combination product and type and the max date will be calculated based on the product/type - see below:

       

       

  • ValeriaBreve's avatar
    ValeriaBreve
    Icon for Post Partisan rankPost Partisan
    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"})