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