get all column by group on datetimestamp with max vlaue on other column.

[tarun89engg]

Hi all,

I have following table 

ID	DateTime	Machine	Message	TimeDiff
77809	2020-03-26 16:25:38	170 R1	Y1 Air Pressure Drops	2000
77810	2020-03-26 16:25:38	170 R2	Error: Source of Power Signal Missing	3500
77811	2020-03-26 16:25:30	BA01_60RT2	60R2 Safety door Opens	3800
77812	2020-03-26 16:25:30	180R3	Error: Glue is missing from part	3800
77815	2020-03-26 16:23:53	190 WZ1	Error: unable to create Signal in DB345	500
77820	2020-03-26 14:33:08	BA03__380SGT1R	60R2 Safety door Opens	689
77825	2020-03-20 12:30:46	120 R2	Error: Source of Power Signal Missing	356
77826	2020-03-20 12:30:46	190 R1	Y1 Air Pressure Drops	3456
77827	2020-03-20 12:30:45	150 R2	Error: Glue is missing from part	5678
77843	2020-03-20 12:30:45	BA03_90RT2	90R2  Safety door Opens	890
77844	2020-03-20 11:30:44	180R3	Error: Power Truns Down Saftety opens	490
77845	2020-03-20 11:24:05	110 WZ1	Failure Signal from the controller	732
77850	2020-03-19 18:46:14	170 R2	Error: Glue is missing from part	689
77860	2020-03-19 18:46:14	BA03__380SGT1R	380SG Safety door Opens	263
77863	2020-03-19 18:46:13	380WZ1	Error: Power Truns Down Saftety opens	794
77864	2020-03-19 18:46:13	380WZ2	Y1 Air Pressure Drops	4478

As shown in below Figure I Have table with 5 Column what I would like to have is for each instance of datetime Stamp column find the max value from TimeDiff Column and corresponding ID, Machine and Message column. For ex there for first two row the timesamp value is same but the corresponding value in TimeDiff is different but I would like to have only Max value from TimeDiff so the correct row would be the second row.

Table1

I have tried group by on DateTime Stamp with Max value from TimeDiff column which gives me correct results but with only two columns, I need also other column. I have tried joins, but nothing is helping in this case (May be I am doing something wrong with joins)

any help is greatly appreciated.

PS: looking for solutio in M code if not possible than DAX 

Regards,

tar.

[MFelix · Accepted solution]

Hi @tarun89engg ,

You need to do the following steps:

• Group by:
  • DateTime
  • Columns to be grouped
    • TimeDiff - Max
    • All Rows - I have called it Details
• Add a custom column with the following syntax

Table.Max ([Details], "TimeDiff")

• Remove the Details column
• Expand the Last column you have created
  
  • ID
  • Message
  • Machine

Check full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldPdboIwGAbgW/nisYn9o5Seadw8WmbUZNkWY5irjgSpKRCzu9m17MrWogIqYHZEQ+Dh+3l5f+/5vkBBr98jfIDogCCCAHNJPEmFvYt9BDNsD68YhpGBqVFpmhsFY6P3qXsNIdRb9gsHo26H2MODMdpImOvcrBXoDUz1QRmYR9skjOEpStMo2drnqFdzcaPrvjYaIrziaLZwtr0SmIcblX3Dp9YGnvcqcUVSUcNIK4YFmtGqxkmcK4hS2B2Lgo3RO9iHJrsWvVuRSs9JOEDw8oYrM0/Cj1hBpmFtVJipc+NRAuMRZU6qNU6uB8okpRKJY+N0tbJlzCcLPOtqnoug9IpKUeUR27hk3FVK/r0gXrK8nQ3u5Mf2XDl+o+OKxt5leV278bgvziSjrWQxweAUHXslvz+N8xNBuQ/GrjRcaOw2O8ehLUyepDDWh8TJmaP1SWU19XorWBImUdE2PufnMYxiN7fTHop+sy8Fa51kRsexMvYhn5Iz6hWBDipU2IVIzBr+xa5Z1sLD28WbMBbHxmkSTkuQNoL0+P7Fb3N3nH7ASpZ1s6QjjYy56Cz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DateTime = _t, Machine = _t, Message = _t, TimeDiff = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DateTime", type datetime}, {"Machine", type text}, {"Message", type text}, {"TimeDiff", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DateTime"}, {{"TimeDiff", each List.Max([TimeDiff]), type number}, {"Details", each _, type table [ID=number, DateTime=datetime, Machine=text, Message=text, TimeDiff=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Top", each Table.Max ([Details], "TimeDiff")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Details"}),
    #"Expanded Top" = Table.ExpandRecordColumn(#"Removed Columns", "Top", {"ID", "Machine", "Message"}, {"ID", "Machine", "Message"})
in
    #"Expanded Top"

[tarun89engg]

hi @MFelix ,

Thanks for your solution it works.

I had this post twice because somehow this post was spam erarlier with multiple edit, after that i created new one. I had already got my answer in another post but it interesting to see different answer for the same problem.

Thanks.