cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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.

 

Table1Table1

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

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

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Helper I
Helper I

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

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.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021