Forum Discussion
get Max value from column by comparing datetime column
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
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
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.
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