Forum Discussion
PBI Column Based Unstructured Data Grouping
Hi All,
I have a dataset as below,
| Data |
| Group1 |
| Jeremiah Lee |
| Joshua Doyle |
| Group2 |
| Julie Miller |
| Beth Stein |
| Group3 |
| Sierra Carroll |
| Zachary Williams |
| Group1 |
| Paul Wilson |
| Group2 |
| Abigail Nelson |
| Group3 |
| Raymond Dillon |
| Mark Rodriguez |
| Group1 |
| Glenn Collins |
| Robin Horne |
| Group2 |
| Angelica White |
| Robert Ryan |
| Robert Wright |
| Raven Davis |
| Tara Meyers |
| Group3 |
| Cynthia Smith |
| John Sanchez |
| Lori Gilbert |
The column name is Data
Basically I want to group names for each Groups
The expected output is something like that for a few example,
| Group1 | Jeremiah Lee |
| Group1 | Joshua Doyle |
| Group1 | Paul Wilson |
| Group2 | Julie Miller |
| Group2 | Beth Stein |
| Group2 | Abigail Nelson |
| Group3 | Sierra Carroll |
| Group3 | Zachary Williams |
| Group3 | Raymond Dillon |
| Group3 | Mark Rodriguez |
Power Query or DAX solutions would be fine, actually I have no idea how can I make this grouping.
Is there anyone have idea on it ?
Hi pbin9_9 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2)Click "Transform Data" to go to the Power Query editor, add index columns, click Apply and close.
(3) We can create a table.
Table 2 = var a=ADDCOLUMNS('Table',"groups",LOOKUPVALUE('Table'[Data],'Table'[Index],MAXX(FILTER('Table',[Index]<EARLIER('Table'[Index])&&CONTAINSSTRING([Data],"Group")),[Index]))) var b=ADDCOLUMNS(a,"Combine",IF(CONTAINSSTRING([Data],"Group"),BLANK(),[groups]&":"&[Data])) return SUMMARIZE(FILTER(b,[Combine]<>BLANK()),[Combine])(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi pbin9_9 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2)Click "Transform Data" to go to the Power Query editor, add index columns, click Apply and close.
(3) We can create a table.
Table 2 = var a=ADDCOLUMNS('Table',"groups",LOOKUPVALUE('Table'[Data],'Table'[Index],MAXX(FILTER('Table',[Index]<EARLIER('Table'[Index])&&CONTAINSSTRING([Data],"Group")),[Index]))) var b=ADDCOLUMNS(a,"Combine",IF(CONTAINSSTRING([Data],"Group"),BLANK(),[groups]&":"&[Data])) return SUMMARIZE(FILTER(b,[Combine]<>BLANK()),[Combine])(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
It's definetely working and the results what I want !
Thanks&Regards
