Search a text in each row then create a new column

[MB_PowerBI]

Hello,

I have the following sample data

ID	DATA
1	A
1	A
2	A
2	B
3	B

My need is to create a third column, which will check for each ID,
if there is at least and only A then list A
if there is at least and only B then list B
Else (there is at least one A and one B then A & B

ID	DATA	Result
1	A	A
1	A	A
2	A	A&B
2	B	A&B
3	B	B

Thanks for your help.

Regards,

[CNENFRNL]

For fun only, a showcase of powerful Excel worksheet formula,

[FreemanZ · Accepted solution]

hi @MB_PowerBI 

try to add a column like this:

Result2 = 
VAR _id = [ID]
VAR _table=
CALCULATETABLE(
    VALUES(TableName[Data]),
    ALL(TableName),
    TableName[ID] = _id
)
RETURN
CONCATENATEX(
   _table,
    TableName[Data],
    "&"
)

it works like this:

[FreemanZ]

or something like this:

Result2 = 
VAR _table=
CALCULATETABLE(
    VALUES(TableName[Data]),
    ALLEXCEPT(TableName, TableName[ID])
)
RETURN
CONCATENATEX(
   _table,
    TableName[Data],
    "&"
)