Forum Discussion
Post PartisanTable Group with multiple criteria/keep the entry relative to the max date
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"})
Advocate IIIn 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])}}
)
ValeriaBreveHello, 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: