Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to Solution.
Hi @tarun89engg ,
You need to do the following steps:
Table.Max ([Details], "TimeDiff")
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"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @tarun89engg ,
You need to do the following steps:
Table.Max ([Details], "TimeDiff")
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"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshi @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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.