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

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

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

Regards,

tar.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: group by on datetime stamp withHi max value from other column and returning all columns.

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

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

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

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).

Highlighted
Helper I
Helper I

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

@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 

beforebefore

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. 

 

afterafter

 

thanks for giving your valuable input.

 

 

 

 

Highlighted
Helper I
Helper I

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

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

Highlighted
Resolver II
Resolver II

Re: group by on datetime stamp withHi max value from other column and returning all columns.

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

View solution in original post

Highlighted
Super User III
Super User III

Re: group by on datetime stamp withHi max value from other column and returning all columns.

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 

SU18_powerbi_badge

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