Forum Discussion

EaglesTony's avatar
EaglesTony
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

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

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 ?

 

Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    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:) 

5 Replies

  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    ToddChitt
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    1 year ago

    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"

     

     

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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.