Filter out most recent date user level in PowerQuery

[Wilm117]

Hi Everybody,

I am looking for a solution to filter out or mark the most recent date in from a salary registration export. table 1 shows an example of the dataset, which in reality contains thousands of records. My goal is to generate a piece of M-code to filter out the records with the most recent "Start date Salary" per Employee_ID and obtain the desired outcome presented below in table 2.

- The amount of records differs per person. Some only have one record, but  others more than 20. 

- The sorting order per person is random, so not automatically from oldest to most recent date.

Is anybody able to help me with the correct M-code for this issue? Thanks in advance!

table 1: Demo

Employee_ID	FTE	Start date Salary	most recent date
P21008	1	1-11-2021 00:00
P21008	1	1-1-2022 00:00
P21008	0,9	1-1-2023 00:00	X
P21008	1	1-7-2022 00:00
P23585	1	1-1-2023 00:00	X
P23585	1	1-1-2022 00:00
P23585	1	1-7-2022 00:00
P23585	1	1-11-2021 00:00
P23914	1	1-11-2021 00:00
P23914	1,1	1-1-2022 00:00
P23914	1,1	1-7-2022 00:00
P23914	1,1	1-1-2023 00:00
P23914	1	1-1-2024 00:00	X
P26523	0	25-9-2022 00:00	X

Table 2: Desired outcome

Employee_ID	FTE	Start date Salary
P21008	0,9	1-1-2023 00:00
P23585	1	1-1-2023 00:00
P23914	1	1-1-2024 00:00
P26523	0	25-9-2022 00:00

[DOLEARY85]

Hi,

hopefully this helps, m - query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9LDoAgDESvYliTMG1BwVO4J9z/GsJK+QgmndWbTqcxqosJ8EorygIZIsO0ASegku55HuYRhw6PQ2YJR5sgzrv6giz4dH+R338ogewfrr8btI6+wyBDJi0KtxXfHUthWewMwutCugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee_ID = _t, FTE = _t, #"Start date Salary" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee_ID", type text}, {"Start date Salary", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee_ID"}, {{"Maxdate", each List.Max([Start date Salary]), type nullable datetime}, {"Details", each _, type table [Employee_ID=nullable text, FTE=nullable number, Start date Salary=nullable datetime]}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"FTE", "Start date Salary"}, {"Details.FTE", "Details.Start date Salary"}),
#"Added Custom" = Table.AddColumn(#"Expanded Details", "Custom", each if [Details.Start date Salary] =[Maxdate] then 1 else 0),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Flag"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Flag] = 1))
in
#"Filtered Rows"

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍