Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jagostinhoCT
Post Partisan
Post Partisan

Grouping By Max value but keep the corresponding values from other columns

Hello,

 

I have the table below with version numbers. I would like to be able to Group the results using the RevitVersionName column with the aggregation of the Max value for the BuildNumber column. But I would like to show the corresponding values for the other columns, as presented below.

 

How can I do this? Thank you in advance

 

Source table

SubVersionNameSubVersionNumberBuildNumberRevitVersionName
First Customer Ship23.0.1.31820220304_1515Autodesk Revit 2023
2023.0.1 - Install23.0.11.1920220503_1030Autodesk Revit 2023
2023.0.1 - Update23.0.10.1820220429_1500Autodesk Revit 2023
2023.0.223.0.20.2120220726_1500Autodesk Revit 2023
2023.123.1.1.2420221114_1515Autodesk Revit 2023
2023.1.123.1.10.420221122_1550Autodesk Revit 2023
First Customer Ship22.0.2.39220210224_1515Autodesk Revit 2022
2022.0.122.0.10.2820210616_0115Autodesk Revit 2022
2022.122.1.1.51620210921_1515Autodesk Revit 2022
2022.1.122.1.10.54120211103_1515Autodesk Revit 2022
2022.1.222.1.21.1320220123_1515Autodesk Revit 2022
2022.1.322.1.30.3420220520_1515Autodesk Revit 2022
First Customer Ship21.0.0.38320200220_1100Autodesk Revit 2021
2021.1 - Update21.1.0.10820200708_1515Autodesk Revit 2021
2021.1 - Install21.1.1.10920200715_1022Autodesk Revit 2021
2021.1.1 - Update21.1.10.2620200909_1515Autodesk Revit 2021
2021.1.1 - Install21.1.11.2720200917_1545Autodesk Revit 2021
2021.1.2 - Update21.1.20.4420201109_1530Autodesk Revit 2021
2021.1.2 - Install21.1.21.4520201116_1100Autodesk Revit 2021
2021.1.3 - Update21.1.30.7420210426_1515Autodesk Revit 2021
2021.1.421.1.40.9520210805_1400Autodesk Revit 2021
2021.1.521.1.50.2720211018_1515Autodesk Revit 2021
2021.1.621.1.60.2520220123_1515Autodesk Revit 2021
2021.1.721.1.70.2120220517_1515

Autodesk Revit 2021

 

 

Desired outcome

SubVersionNameSubVersionNumberBuildNumberRevitVersionName
2023.1.123.1.10.420221122_1550Autodesk Revit 2023
2022.1.322.1.30.3420220520_1515Autodesk Revit 2022
2021.1.721.1.70.2120220517_1515Autodesk Revit 2021
1 REPLY 1
HotChilli
Super User
Super User

In Power Query, using the interface, you can:

Duplicate the query.

Perform a 'Group By' on RevitVersionName with aggregation Max on BuildNumber .

Merge that table with the original (Inner join on RevitVersionName and BuildNumber )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.