Find latest time per date / Grouping?

[_n_MarianLein]

Hi all,

I'm trying to wrap my head around the following:

I have auto-generated reports for a specific topic. These files are generated each 15 minutes, and I want to select the latest available file for each date to work with.

What I end up with is a table that looks like this...

Content	Date Modified
[Binary]	2020-04-01-23-30-00
[Binary]	2020-04-01-23-45-00
[Binary]	2020-04-02-00-00-00
	...
[Binary]	2020-04-02-23-30-00
[Binary]	2020-04-02-23-45-00
[Binary]	2020-04-03-00-00-00
	...
[Binary]	2020-04-22-15-15-00
[Binary]	2020-04-22-15-30-00

Now, how can I properly select (from this table) only the files which are the latest available per day?

Again, not meaning "only 23:45" but "only time per day"

Thanks!

[danextian · Accepted solution]

Hi @_n_MarianLein ,
Are  you, by chance, connecting to a folder? If so, do the following:

• Sort Date modified in descending order and wrap the resulting formula in Table.Buffer( <formula in power query>).
• Select Date modified and go to Add Column>Date >Date Only.
• Right-click the newly created  column and then select Remove Duplicates which should keep just the first item which is the latest date modified per date.

[_n_MarianLein]

Thanks, @danextian - that worked and was way less complicated than I anticipated.

[dax]

Hi @_n_MarianLein , 

It seems that you want to get the last datettime of each day, right? If so, you could refer to below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WinbKzEssqoxV0lEyMjAy0DUw0TUw1DUy1jUGsg2UYnXwKDExxafECCgJQXiUELTIiLBFxgQtMjLSNTQFIUJKoG6JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Content = _t, #"Date Modified" = _t]),
    #"Split Column by Positions" = Table.SplitColumn(Source, "Date Modified", Splitter.SplitTextByPositions({0, 10, 11}), {"Date Modified - Copy.1", "Date Modified - Copy.2", "Date Modified - Copy.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Positions","-","T",Replacer.ReplaceText,{"Date Modified - Copy.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","-",":",Replacer.ReplaceText,{"Date Modified - Copy.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Date Modified - Copy.1", "Date Modified - Copy.2", "Date Modified - Copy.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Merged", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "DATE", each DateTime.Date([Merged])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"DATE"}, {{"ALL", each _, type table [Content=text, Merged=datetime, DATE=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([ALL], "index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Content", "Merged", "index"}, {"Content", "Merged", "index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ALL"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([index] = 1))
in
    #"Filtered Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.