Concatenate value with a group by in a new column

[Anonymous]

Hello,

I would like to concatenante Item in a new column (Item Final) per Category (group by) and without duplicates

Input : 

Field1	Field2	Category	Item
	Test1	MUL	A
Group1	Test1	NGA
	Test1	NIMI
	Test1	NIMI	A
	Test1	NIMI	B
	Test1	NIMI	A

Output : 

Field1	Field2	Category	Item	Item Final
	Test1	MUL	A	A
Group1	Test1	NGA
	Test1	NIMI		A-B
	Test1	NIMI	A	A-B
	Test1	NIMI	B	A-B
	Test1	NIMI	A	A-B

For exemple for each Category=  NIMI the Item Final should be equal to the concatentation of item value for NIMI

Thanks for your help,

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

You could create a column as below:-

Column = 
VAR cat_ = 'Table'[Category]
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ('Table'[Item]),
            FILTER ( 'Table', 'Table'[Category] = cat_ && 'Table'[Item] <> BLANK())
        ),
        'Table'[Item],
        ","
    )

Output:-