Group by depending on value in another column

[brankocareer]

Hi all,

There are 4 column in Power Query: [Position ID],[Modified Date],[Date],[FTE Count], the data type of [Modified Date] is Date/Time . Data Type of [Date] is Date.

I want to add a new column in power query after step "Filtered Row2".
The condition is: for each [Position ID] (I just filter one ID as example), for each day, get the value of [FTE Count] at the latest time of that day's  [Modified Date]. Result like this:

I want to insert step after, to group by, how should I write the code?

I greatly appreciate your help!!!

Branko

[wdx223_Daniel · Accepted solution]

=Table.FromRecords(Table.Group(#"Filtered Row2",{"Position ID","Date"},{"n",each Table.Sort(_,{"Modified Date",1}){0}})[n])