Power Query Split to Rows and Reference columns within Groups

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:00 Intro

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 )

=LET(

_Data,TEXTSPLIT(B5,”,”,),

FilteredList, FILTER(Data,ISNUMBER(SEARCH(“Yes”,_Data))),

TEXTJOIN(“, “,,TRIM(_FilteredList))

)

Link to the File

https://aasolutions.sharepoint.com/:x…

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.