Concatenate / Combine values based on another column

[Anonymous]

Kia Ora

I would like to know if there is a way of achieving the following: I have a table that has codes and a key that identifies related codes. In the example below all one's are related, all the two's and so on.

I need an output that combines the codes when they are related by a key - see the desired outcome column.

As seen in the example below there can be multiple codes that are the same for the key. I just need a combined outcome of each distinct code for each key.

I also need the outcome to shoew in the same format for each row. i.e. for key one each row must be ABC123 / BCD234 and not a mix of ABC123 / BCD234 & BCD234 / ABC123.

I would eb very gratful if anyone know if this is possible.

Code	Key		Desired Outcome
ABC123	1		ABC123 / BCD234
ABC123	1		ABC123 / BCD234
BCD234	1		ABC123 / BCD234
BCD234	1		ABC123 / BCD234
CDE345	2		CDE345 / DEF456
CDE345	2		CDE345 / DEF456
DEF456	2		CDE345 / DEF456
DEF456	2		CDE345 / DEF456
EFG567	3		EFG567 / FGH678
EFG567	3		EFG567 / FGH678
FGH678	3		EFG567 / FGH678
FGH678	3		EFG567 / FGH678

[Zubair_Muhammad · Accepted solution]

@Anonymous

You can use this as calculated column

Column =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( TableName[Code] ),
        ALLEXCEPT ( TableName, TableName[Key] )
    ),
    [Code],
    " / ",
    [Code], ASC
)