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:38 2000 2020-03-26 16:25:38 3500 2020-03-26 16:25:30 3500 2020-03-26 16:25:30...
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 

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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Table

       

       

       

       

       

       

       

       

       

       

       

       

      HI AlB 

       

      Here is the better image of table with two column.

      as you mentioned 16:25:382000 

       

      2000 is the value from other column. 

       

      The problem was i am unable to edit the HTML for table. i have tried to seprate the column with width and height but it alays gives me an error. 

       

      sorry for any inconvinience.

       

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        Anonymous 

        Try this in M

        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 Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([#"DateTime"])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "LatestInThisDate", each List.Max(Table.SelectRows(#"Added Custom",(inner)=>inner[Date]=[Date])[DateTime])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [LatestInThisDate] = [DateTime]),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Date", "LatestInThisDate"})
in
    #"Removed Columns"

        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