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

Question

Hello,&nbsp;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.&nbsp;How can I do this? Thank you in advance&nbsp;Source tableSubVersionNameSubVersionNumberBuildNumberRevitVersionNameFirst Customer Ship23.0.1.31820220304_1515Autodesk Revit 20232023.0.1 - Install23.0.11.1920220503_1030Autodesk Revit 20232023.0.1 - Update23.0.10.1820220429_1500Autodesk Revit 20232023.0.223.0.20.2120220726_1500Autodesk Revit 20232023.123.1.1.2420221114_1515Autodesk Revit 20232023.1.123.1.10.420221122_1550Autodesk Revit 2023First Customer Ship22.0.2.39220210224_1515Autodesk Revit 20222022.0.122.0.10.2820210616_0115Autodesk Revit 20222022.122.1.1.51620210921_1515Autodesk Revit 20222022.1.122.1.10.54120211103_1515Autodesk Revit 20222022.1.222.1.21.1320220123_1515Autodesk Revit 20222022.1.322.1.30.3420220520_1515Autodesk Revit 2022First Customer Ship21.0.0.38320200220_1100Autodesk Revit 20212021.1 - Update21.1.0.10820200708_1515Autodesk Revit 20212021.1 - Install21.1.1.10920200715_1022Autodesk Revit 20212021.1.1 - Update21.1.10.2620200909_1515Autodesk Revit 20212021.1.1 - Install21.1.11.2720200917_1545Autodesk Revit 20212021.1.2 - Update21.1.20.4420201109_1530Autodesk Revit 20212021.1.2 - Install21.1.21.4520201116_1100Autodesk Revit 20212021.1.3 - Update21.1.30.7420210426_1515Autodesk Revit 20212021.1.421.1.40.9520210805_1400Autodesk Revit 20212021.1.521.1.50.2720211018_1515Autodesk Revit 20212021.1.621.1.60.2520220123_1515Autodesk Revit 20212021.1.721.1.70.2120220517_1515Autodesk Revit 2021&nbsp;&nbsp;Desired outcomeSubVersionNameSubVersionNumberBuildNumberRevitVersionName2023.1.123.1.10.420221122_1550Autodesk Revit 20232022.1.322.1.30.3420220520_1515Autodesk Revit 20222021.1.721.1.70.2120220517_1515Autodesk Revit 2021

hotchilli · Answer

In Power Query, using the interface, you can:
Duplicate the query.
Perform a 'Group By' on&nbsp;RevitVersionName&nbsp;with aggregation Max on&nbsp;BuildNumber&nbsp;.
Merge that table with the original (Inner join on&nbsp;RevitVersionName and&nbsp;BuildNumber )

Forum Discussion

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

1 Reply

Related content

Grouping rows by Column and then updating Column

Create columns group

Grouping Columns

Matrix column not grouped

Fabric & Azure Data User Group Program Requirements & Benefits

SubVersionName	SubVersionNumber	BuildNumber	RevitVersionName
First Customer Ship	23.0.1.318	20220304_1515	Autodesk Revit 2023
2023.0.1 - Install	23.0.11.19	20220503_1030	Autodesk Revit 2023
2023.0.1 - Update	23.0.10.18	20220429_1500	Autodesk Revit 2023
2023.0.2	23.0.20.21	20220726_1500	Autodesk Revit 2023
2023.1	23.1.1.24	20221114_1515	Autodesk Revit 2023
2023.1.1	23.1.10.4	20221122_1550	Autodesk Revit 2023
First Customer Ship	22.0.2.392	20210224_1515	Autodesk Revit 2022
2022.0.1	22.0.10.28	20210616_0115	Autodesk Revit 2022
2022.1	22.1.1.516	20210921_1515	Autodesk Revit 2022
2022.1.1	22.1.10.541	20211103_1515	Autodesk Revit 2022
2022.1.2	22.1.21.13	20220123_1515	Autodesk Revit 2022
2022.1.3	22.1.30.34	20220520_1515	Autodesk Revit 2022
First Customer Ship	21.0.0.383	20200220_1100	Autodesk Revit 2021
2021.1 - Update	21.1.0.108	20200708_1515	Autodesk Revit 2021
2021.1 - Install	21.1.1.109	20200715_1022	Autodesk Revit 2021
2021.1.1 - Update	21.1.10.26	20200909_1515	Autodesk Revit 2021
2021.1.1 - Install	21.1.11.27	20200917_1545	Autodesk Revit 2021
2021.1.2 - Update	21.1.20.44	20201109_1530	Autodesk Revit 2021
2021.1.2 - Install	21.1.21.45	20201116_1100	Autodesk Revit 2021
2021.1.3 - Update	21.1.30.74	20210426_1515	Autodesk Revit 2021
2021.1.4	21.1.40.95	20210805_1400	Autodesk Revit 2021
2021.1.5	21.1.50.27	20211018_1515	Autodesk Revit 2021
2021.1.6	21.1.60.25	20220123_1515	Autodesk Revit 2021
2021.1.7	21.1.70.21	20220517_1515	Autodesk Revit 2021