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 li...
Need Help
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