Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

get Max value from column by comparing datetime column

Hi all,

 

I have table as below with Two column.

DateTime                     S_Value

2020-03-26 16:25:382000
2020-03-26 16:25:383500
2020-03-26 16:25:303500
2020-03-26 16:25:302050
2020-03-26 16:23:53500
2020-03-26 14:33:08689
2020-03-20 12:30:46356
2020-03-20 12:30:463456
2020-03-20 12:30:455678
2020-03-20 12:30:45890
2020-03-20 11:30:44490
2020-03-20 11:24:05732
2020-03-19 18:46:14689
2020-03-19 18:46:14263
2020-03-19 18:46:13794
2020-03-19 18:46:134478

 

I would like to compare datetime column and if multiple datetime exist and get only the max value from column S_Value for correspoding datetime column. 

 

so the output has to be follwoing.

DateTime                     S_Value

2020-03-26 16:25:383500
2020-03-26 16:25:303800
2020-03-26 16:23:53500
2020-03-26 14:33:08689
2020-03-20 12:30:463456
2020-03-20 12:30:455678
2020-03-20 11:30:44490
2020-03-20 11:24:05732
2020-03-19 18:46:14689
2020-03-19 18:46:134478

 

any help is greatly appreciated.

Need Help
  • AlB's avatar
    AlB
    6 years ago

    Anonymous 

    It's simpler then. Just use "Group By"

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBLDsAgCATQqxjXbTJ8ROUqTe9/jWp3pmq3vMAA1xUZjBNysgUy5+RS4tGqQLyPFUtaMv6ZkaYsnqTxrFldxNGjrdRREYjbXFd7k22nuuTUgy2XDZeKj9Kr2lSnyurovVl4UKqBSlvKSSc3jcomC+3fylU3qtpPuh8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, S_Value = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"S_Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"DateTime"}, {{"S_Value", each List.Max([S_Value]), Int64.Type}})
in
    #"Grouped Rows"

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

7 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi Anonymous 

    Do you need this in M or DAX?

     

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      AlB 

      M is better at first instance.

       

      for learning purpose if you can add DAX will be useful also for me and for others too.

       

      Thanks for your reply.

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        Anonymous 

        What is the last part of he SValue (in red below)?? Assuming the rests is hh:mm:ss. A complete explanation from the beginning would help us not to waste time unnecessarily with this type of questions

        16:25:382000

         

        Please mark the question solved when done and consider giving kudos if posts are helpful.

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

        Cheers