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 M code if not possible than DAX Measure.
Regards,
tar.
Solved! Go to 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
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
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
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).
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
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.
thanks for giving your valuable input.
I have tried removed duplicates. It seems that the final result, which i would like to have.
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.