Maxifs - Power Query to get latest row details where duplicate available

[Vish_BI_Toddler]

Hey there,

I have sample table below based on MS form responses with response date, user details like ID and response for question. Problem is sometimes a user submits multiple responses for a single day - I would like to get latest response date and time for each user for each day response submitted.

 
in above example user 123 submitted 2 responses for 26 aug and 1 response for 24 aug. Column D is required output which will show for user 123 for response date 26 aug - 7 am (being latest reaponse) .. for 24 aug its single response hence same value being reported. Similar for other users too.

thanks in advance and really learning a lot from community 🙂

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRstA3MtM3MjAyVjC3MjAACvjlK8XqRCtZWlpCZE0gsoZ4ZS0gspGpxWBpuNGmKEZjSJtgs9nc3BzFXaY4NGORjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EMP ID" = _t, #"Response Date" = _t, Response = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMP ID", Int64.Type}, {"Response Date", type datetime}, {"Response", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EMP ID"}, {{"Latest Response", each List.Max([Response Date]), type nullable datetime}, {"r", each _, type table [EMP ID=nullable number, Response Date=nullable datetime, Response=nullable text]}}),
    #"Expanded r" = Table.ExpandTableColumn(#"Grouped Rows", "r", {"Response Date", "Response"}, {"Response Date", "Response"})
in
    #"Expanded r"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Vish_BI_Toddler]

Thanks a lot for detailed response.. it works as expected.. ☺️