Persons in the same team

[BENJO]

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

[dufoq3]

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

[AnalyticPulse]

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]

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]

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

[BENJO]

Thanks, that worked. But I want the Name in the row to not be in the Teammates List. Is that possible?

I also would like to do it within the power query editor, is that possible aswell?