group by on datetime stamp with max value from other column and returning all columns.

[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 M code if not possible than DAX Measure.

Regards,

tar.

[ziying35 · Accepted solution]

Hi, @tarun89engg 

my code as below:

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZRbi9swEIX/yuDnLIwulm297eJ26cPSJTGU3ghuKmcFjhRkm6WU/vfK2V6sxE7i0tdJ8HfmnDn6+D16k0cySVLMFlFetqrQOxXJiCLFG2Q3VBRESBpLlkaL6KHcPGnT/04ShCXpR6ppym0/ek/gVjt4dH7SOQW5s/vG/6P/Yq6rKpIUEX8s/iAJzkfSAPnKOeskrGznNgpsBY/2WTlY6a0pa3jQTaPNNpDA4lACuSABAwl3t0jWApdFKMNPKKzKSrXf4Ku1Dt7ulQlXZ2nIpbO4JMUlG9v8vu4U6AZ2L6tC5ewO9qVrz8Ljc3AmYxbCM4R3H8gYvjPll1pBa2HjlP/ib+e1gfyO8ThQETpPp8PnkjGJ6bHzbL32i6zuC7Kc675IsyF6Yn8sCPXOSy7C/el/uTsxVCDmKcj+oWze/gCZXEDGITKeWvq6k4tFkg7onM2iH9LOTnrmJ/RTh1jihbzTbHhqnE/CyQHOryvbS8iF60wDuX02vYi2V2FPBPBQwPTBEUm5xCPvyWnhXpe67nP+dWIH09snBRtrWmfrWrmAnzA64MfjXSNZQVJ/a5IcGTD50F6Xfdg2MQ9+puiH4YXkqWBD9vjZ/WWHL50HTDx0M6JPMj5UMH57ZxXQmU3n3Hft808=",BinaryEncoding.Base64),Compression.Deflate))),
    group = Table.Group(Source,"DateTime",{"t",each Table.MaxN(_,each [TimeDiff],1)}),
    cmbTbls = Table.Combine(group[t]),
    result = Table.TransformColumnTypes(cmbTbls,{{"DateTime", type datetime}})
in
    result

If my code solves your problem, mark it as a solution

[AlB]

Hi @tarun89engg 

The last step is required only if you want to keep the row with the lowest ID in the case that TimeDiff is repeated for the timestamp (as is the case in rows with IDs 77811 and 77812). From your expected result it would seem you want that. If it doesn't matter, you can end the query at the second step:  #"Filtered Rows"

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZRbi9swEIX/yuDnLIwulm297eJ26cPSJTGU3ghuKmcFjhRkm6WU/vfK2V6sxE7i0tdJ8HfmnDn6+D16k0cySVLMFlFetqrQOxXJiCLFG2Q3VBRESBpLlkaL6KHcPGnT/04ShCXpR6ppym0/ek/gVjt4dH7SOQW5s/vG/6P/Yq6rKpIUEX8s/iAJzkfSAPnKOeskrGznNgpsBY/2WTlY6a0pa3jQTaPNNpDA4lACuSABAwl3t0jWApdFKMNPKKzKSrXf4Ku1Dt7ulQlXZ2nIpbO4JMUlG9v8vu4U6AZ2L6tC5ewO9qVrz8Ljc3AmYxbCM4R3H8gYvjPll1pBa2HjlP/ib+e1gfyO8ThQETpPp8PnkjGJ6bHzbL32i6zuC7Kc675IsyF6Yn8sCPXOSy7C/el/uTsxVCDmKcj+oWze/gCZXEDGITKeWvq6k4tFkg7onM2iH9LOTnrmJ/RTh1jihbzTbHhqnE/CyQHOryvbS8iF60wDuX02vYi2V2FPBPBQwPTBEUm5xCPvyWnhXpe67nP+dWIH09snBRtrWmfrWrmAnzA64MfjXSNZQVJ/a5IcGTD50F6Xfdg2MQ9+puiH4YXkqWBD9vjZ/WWHL50HTDx0M6JPMj5UMH57ZxXQmU3n3Hft808=",BinaryEncoding.Base64),Compression.Deflate))),
    
#"Filtered Rows" = Table.SelectRows(Source, each List.Max(Table.SelectRows(Source, (inner)=> inner[DateTime]=[DateTime])[TimeDiff])=[TimeDiff]),

#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each List.Min(Table.SelectRows(#"Filtered Rows", (inner)=> inner[DateTime]=[DateTime])[ID])=[ID]) 

in
    #"Filtered Rows2"

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 

[artemus]

What you can do is:

1. Do a group on DateTime, Add Max for TimeDiff as a new column, and add "All rows" as a column called Row

2. Expand the Row table column (don't include prefix) and do not include the TimeDiff or the DateTime column when you expand (since you already have them).

[tarun89engg]

@artemus 

I have tried that in that case i have duplicate value from timestamp. 

here is a screenhot before and after adding new column from table (row)

before 

before

as you can see until now everything works. as soon as i add other column it gives me for same timestamp same time diff which i don't need. I would like to have only first instance. I have marked the region with red. 

after

thanks for giving your valuable input.

[tarun89engg]

I have tried removed duplicates. It seems that the final result, which i would like to have.