Forum Discussion
Table Group with multiple criteria/keep the entry relative to the max date
- 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"})
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"})