PBI Column Based Unstructured Data Grouping

[pbin9_9]

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 ?

[Anonymous · Accepted solution]

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. 

[pbin9_9]

Hi @Anonymous ,
It's definetely working and the results what I want ! 
Thanks&Regards