Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 |
Desired outcome
SubVersionName | SubVersionNumber | BuildNumber | RevitVersionName |
2023.1.1 | 23.1.10.4 | 20221122_1550 | Autodesk Revit 2023 |
2022.1.3 | 22.1.30.34 | 20220520_1515 | Autodesk Revit 2022 |
2021.1.7 | 21.1.70.21 | 20220517_1515 | Autodesk Revit 2021 |
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 )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |