Power Query is that answer to so many data challenges and problems. Whether using it in Power BI, Excel or elsewhere. I love it!
This was my solution to a Reddit Forum question
00:26 The Question / Challenge
01:36 Split to Rows
03:34 Group By
04:13 Reference a Grouped Column
06:04 An Excel Dynamic Array Solution:
The Excel DA Formula ( Excel 365 Only )
Link to the File
Power Query Split to Rows and Reference columns within Groups is a feature of Microsoft Power Query, which is a data transformation and mashup tool. This feature allows users to transform their data into a table format by splitting a single column into multiple rows based on the values within a group. In addition, users can also reference columns within the group to create additional columns. This feature is useful for transforming data sets into a more organized structure for further analysis.