Concatenate multiple columns without repeating the duplicate values , please help

I have a multiple columns named as overall , sub1 given, sub2 given , sub3 return Overall sub1 Given sub2 Given sub 3 Return Bag Bag Bottle Purse Bag Bag ...

Need Help

barritown

2 years ago

Hi Anonymous,

Here is a way to solve it with DAX:

DAX code in plain text for convenience (see also the attached file):

Result = 
VAR curRow = SUBSTITUTE ( [Overall], " ", "" )
VAR curRowH = SUBSTITUTE ( curRow, ",", "|" )
VAR nItems = PATHLENGTH ( curRowH )
VAR res = IF ( NOT ISBLANK ( nItems ), 
    VAR _tbl1 = SUMMARIZE ( ADDCOLUMNS ( GENERATESERIES ( 1, nItems ), "sub", PATHITEM ( curRowH, [Value] ) ), [sub] )
    VAR _tbl2 = DISTINCT ( UNION ( _tbl1, { [sub1 Given], [sub2 Given] } ) )
    VAR _tbl3 = FILTER ( _tbl2, NOT ISBLANK ( [sub] ) && [sub] <> [sub3 Return] )
    RETURN CONCATENATEX ( _tbl3, [sub], "," ),
    VAR _tbl4 = FILTER ( DISTINCT ( { [sub1 Given], [sub2 Given] } ), NOT ISBLANK ( [Value] ) && [Value] <> [sub3 Return] ) 
    RETURN CONCATENATEX ( _tbl4, [Value], "," ) )
RETURN res

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

community-concatenate.pbix38 KB