Forum Discussion

BENJO's avatar
BENJO
Regular Visitor
2 years ago

Persons in the same team

Hi all,

 

I have a table with two columns. In the first column are names and in the second column the corresponding team.

I would like a third column in which all teammates in the same team are listed. How can I make it so that there is one row per name with all teammates?

 

Solution for BerLIN: autumn,AAAAY, Lysoar, Life

Solution for autumn: BerLIN,AAAAY,Lysoar,Life

 

 

Thanks and best regards

 

 

Need Help

5 Replies

  • AnalyticPulse's avatar
    AnalyticPulse
    Solution Sage
    2 years ago

    Let's assume your table is called "Teammates" with columns "Name" and "Team". You want to add a calculated column named "Teammates List" that lists all teammates in the same team.
    create a calculated column as below:
    Teammates List =
    VAR CurrentTeam = Teammates[Team]
    RETURN
    CONCATENATEX (
    FILTER ( Teammates, Teammates[Team] = CurrentTeam ),
    Teammates[Name],
    ", "
    )

    If this helped, Follow this blog for more insightful information about data analytics
    https://analyticpulse.blogspot.com/
    Please Subscribe AnalyticPulse on YouTube for future updates:
    https://www.youtube.com/@AnalyticPulse
    Please subscribe CogniJourney On Youtube For Daily fun facts:
    https://www.youtube.com/@CogniJourney


    • BENJO's avatar
      BENJO
      Regular Visitor
      2 years ago

      Hey AnalyticPulse,

       

      thanks for the quick response.

      What do you mean with calculated column? Custom Column? I get an error here

       

      Solution for BerLIN: autumn,AAAAY, Lysoar, Life

      Solution for autumn: BerLIN,AAAAY,Lysoar,Life

      • AnalyticPulse's avatar
        AnalyticPulse
        Solution Sage
        2 years ago

        calculated columns means the on eyou add in data view mode from data modeling tab, not inn power query

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi BENJO,

     

    check every single step, maybe you want one of the middle steps as result

     

    Final reslut

     

    v1

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc9BCsIwEIXhq5SsLPQScSGIIRZbBFu6iHGkselEmwRsT29x52bibP+PB9O2bBextNFnZe8AzTvbnLk4nrisc1awLUxiL1lXJJyKIY6Ydny9S5qJ2Ts1/eHMHb6qAgwGwfrf3oC13niK1IAL1R+ux1egxJNLrW6KIpXSM9UXGAZAcsHoA7ngxuv6Z/cB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Name = _t]),
    GroupedRows = Table.Group(Source, {"Team"}, {{"Names", each Text.Combine([Name], ", "), type table}, {"All", each _, type table}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Name"}, {"Name"}),
    RemovedName = Table.ReplaceValue(ExpandedAll,
        each [Names], 
        each [Name],
        (x,y,z)=> Text.Combine(List.Select(Text.Split(y, ", "), each _ <> z), ", "),
        {"Names"} )
in
    RemovedName

     

     

    v2

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc9BCsIwEIXhq5SsLPQScSGIIRZbBFu6iHGkselEmwRsT29x52bibP+PB9O2bBextNFnZe8AzTvbnLk4nrisc1awLUxiL1lXJJyKIY6Ydny9S5qJ2Ts1/eHMHb6qAgwGwfrf3oC13niK1IAL1R+ux1egxJNLrW6KIpXSM9UXGAZAcsHoA7ngxuv6Z/cB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Name = _t]),
    GroupedRows = Table.Group(Source, {"Team"}, {{"NameList", each [Name], type list}, {"All", each _, type table}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Name"}),
    Ad_Names = Table.AddColumn(ExpandedAll, "Names", each Text.Combine(List.Select([NameList], (x)=> x <> [Name]), ", "), type text),
    RemovedColumns = Table.RemoveColumns(Ad_Names,{"NameList"})
in
    RemovedColumns