Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find latest time per date / Grouping?

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...

ContentDate 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!

Need Help
  • danextian's avatar
    danextian
    6 years ago

    Hi Anonymous ,
    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.

3 Replies

  • danextian's avatar
    danextian
    Super User
    6 years ago

    Hi Anonymous ,
    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    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.