Forum Discussion
Solved
Filtering a table on the Max date by department
Hi - I am trying to do the following (I posted something earlier this month and I think the example was incorrect). I would like to pull the max date based upon a specific department. Input: D...
Thank you everyone for examples and assistance - appreciate it.
I ended up doing the following:
- Sort by Department (Ascending) then by date (Decending - putting the most recent date first)
- Adding ranking logic by Department
- The result is the most recent date results in a ranking of 1 for each departmnt
- Filter on the 1 Ranking
As the data dynamically updates, I will always get the most recent date.
Thanks - Jerry
Hi jerryr125
You can easily handle this problem using GroupBY command, but in the third argument you need to apply a modification.
consider the following code: (copy and past it in advanced editor to see the result)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01Dcw1DcyMDIFcQyUYnWilZzQxY2QxI2QxI0h4s4Y4qZgcRcg08AS2XwUcUNTNPNdwE5AFgeqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DEPARTMENT = _t, KPIDATE = _t, KPISCORE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"KPIDATE", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"DEPARTMENT"}, {{"Count", each Table.Max(_,"KPIDATE")}}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"KPIDATE", "KPISCORE"}, {"KPIDATE", "KPISCORE"})
in
#"Expanded Count"
see in the Table.Group function, I have just modify the third arguemnt and replaced it by Table.Max(_,"KPIDATE")