get Max value from column by comparing datetime column

[tarun89engg]

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	2050
2020-03-26 16:23:53	500
2020-03-26 14:33:08	689
2020-03-20 12:30:46	356
2020-03-20 12:30:46	3456
2020-03-20 12:30:45	5678
2020-03-20 12:30:45	890
2020-03-20 11:30:44	490
2020-03-20 11:24:05	732
2020-03-19 18:46:14	689
2020-03-19 18:46:14	263
2020-03-19 18:46:13	794
2020-03-19 18:46:13	4478

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:38	3500
2020-03-26 16:25:30	3800
2020-03-26 16:23:53	500
2020-03-26 14:33:08	689
2020-03-20 12:30:46	3456
2020-03-20 12:30:45	5678
2020-03-20 11:30:44	490
2020-03-20 11:24:05	732
2020-03-19 18:46:14	689
2020-03-19 18:46:13	4478

any help is greatly appreciated.

[AlB]

Hi @tarun89engg 

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 

[tarun89engg]

@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]

@tarun89engg 

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 

[tarun89engg]

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]

@tarun89engg 

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 

[tarun89engg]

Hi again @AlB 

I have tried your solution but it didn't give me a desired result.

Table

for each instance of timestamp check the same timestamp exist and if it exist than get the max value row from the table for each timestamp as shown in figure.

for ex. for a first two row with a same time stamp the max value is 3500. so final has to be 

3/26/2020 4:25:38 PM           3500

currectly your solution gives me a partial result. 

next time i will add more descripiton. 

regads

tar.

[AlB · Accepted solution]

@tarun89engg 

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