Help to filter records with minimum duration for each date / Excel PowerQuery

[Vara]

Hi all,

could you please help to filter rows with minimum duration for each date ?.

Data set: 

Date modified        SelectedDate               Duration
19/06/2017 01:03   01/07/2017 00:00       273.6862792
22/06/2017 09:53   01/07/2017 00:00       192.8536673
22/06/2017 09:53   01/07/2017 00:00       192.8536673
29/06/2017 11:42   01/07/2017 00:00       23.03526859*
19/06/2017 01:03   01/07/2017 00:00      273.6862792
29/06/2017 11:42 02/07/2017 00:00      47.03526859*
22/06/2017 09:53 02/07/2017 00:00 216.8536673
19/06/2017 01:03 02/07/2017 00:00 297.6862792
19/06/2017 01:03 02/07/2017 00:00 297.6862792
22/06/2017 09:53 02/07/2017 00:00 216.8536673
02/07/2017 20:24 04/07/2017 00:00 14.34479694
03/07/2017 09:20 04/07/2017 00:00 1.414989898*
02/07/2017 20:24 04/07/2017 00:00 14.34479694
29/06/2017 11:42 04/07/2017 00:00 95.03526859
02/07/2017 20:24 05/07/2017 00:00 38.34479694
03/07/2017 09:20 05/07/2017 00:00 25.4149899*
02/07/2017 20:24 05/07/2017 00:00 38.34479694
29/06/2017 11:42 05/07/2017 00:00 119.0352686

Expecting the values marked as * for each Selected Date .

Thanks in advance.

[Vara]

Thanks it worked.

[Vera_33 · Accepted solution]

Hi @Vara 

You want to filter the table down to min duration for each selected date only? Here is one way, paste in Advanced Editor to see the Groupby and join

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBLCgMhEETv4npQ+2+fRbz/NUKSCXQvDCOuGh9Vj5qzgLeuDTtYuUqH1u13oFHVoWiOZV2zIO5IcKxDSNXolNy3U+0kqEP8Qx54JhIDyZYzs2ckETR5wjYT3VL7c/J5e/rsHPfkSszm6vwlaUtWBvbxfoeZec9IuuQ9c6aEg8Y/z0ii3KKnkVkzkgB+e2pZ6wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date modified" = _t, SelectedDate = _t, Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date modified", type date}, {"SelectedDate", type date}, {"Duration", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SelectedDate"}, {{"MinDuration", each List.Min([Duration]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"SelectedDate", "MinDuration"}, #"Changed Type", {"SelectedDate", "Duration"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Date modified"}, {"Date modified"})
in
    #"Expanded Grouped Rows"