How can I collapse multiple rows to single with last row as concatenated

[EaglesTony]

Hi,

  I have the following data that is grouped:

Area        Domain      Sub-Domain      TeamMember

A             B                 C                        John

A             B                 C                        Tom

A             D                 F                        Sue

A             D                 F                        Mary

A             E                  L                        Frank

I need to concat the last column and collape the table to look like

Area        Domain      Sub-Domain      TeamMember

A             B                 C                        John  / Tom

A             D                 F                        Sue / Mary

A             E                  L                        Frank

Any way to get this alike ?

[v-pnaroju-msft]

Thank you, @ToddChitt , @MasonMA , and @Omid_Motamedise , for your responses.

Hi @EaglesTony,

We appreciate your inquiry raised through the Microsoft Fabric Community Forum.

We would like to follow up to know if you have had the opportunity to review the solutions provided by @MasonMA, @Omid_Motamedise to address the issue. We hope that the information shared will assist in resolving your query.

Should you have any further questions or require additional assistance, please do not hesitate to reach out to the Microsoft Fabric Community.

Thank you.

[Omid_Motamedise]

Hi @EaglesTony 

just copy the following code and paste it into the Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcjy0QElH6dACJyjtDKa98jPylGJ1cEmH5OeiyLpAaTcwHVyaikfWN7GoEkXaFUz7gEm3osS8bKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Area " = _t, #"Domain " = _t, #"Sub-Domain " = _t, TeamMember = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Area ", "Domain ", "Sub-Domain "}, {{"Team members", each Text.Combine([#"TeamMember"]," / "), type nullable text}})
in
    #"Grouped Rows"

[MasonMA · Accepted solution]

Hi @EaglesTony 

I'd recommend leveraging UI as much as you can and only coding wherever not possible with UI. For this You can use UI to Group by first 3 Columns and have UI generate the M code, then with some modification on the code to concatenate the Names. 

The UI will generate M code below

= Table.Group(Source, {"Area", "Domain", "Sub-Domain"}, {{"teamMembers", each _, type table [Area=text, Domain=text, #"Sub-Domain"=text, TeamMember=text]}})

then Replace the part

{{"teamMembers", each _, type table [Area=text, Domain=text, #"Sub-Domain"=text, TeamMember=text]}}

with below M code to concatenate those names. 

{{"TeamMembers", each Text.Combine([TeamMember], " / "), type text}}

Hope this helps:) 

[ToddChitt]

What is the MAX number of names you might come across for any one group? two? ten? one hundred? If known and manageable, you could pivot (or unpivot, i never remember which does which) so you get a column for each row in Team Member column. Then create a calculated column that concatenates all the values. 

Yoiu will need to put in some logic to not leave trailing slashes if it has less than the max number of members.

[EaglesTony]

There is no max.