Concatenate multiple columns without repeating the duplicate values , please help

[sonalisaha2310]

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
	Headset	Monitor
Notebook	Diary
Bag, Notebook,Bottle			Bag
Diary

I want to concatenate (overall + sub1 given + sub2  given ) - sub3 return , If overall , sub1given,sub2given contains the value as in sub3 return then the new column should elimate that value.  Also if overall , sub1 given , sub2 given contains the same string then it should write the string only one time ,The result should look like below

Overall Accessories

Bag

Bag,Bottle,Purse

Bag

Headset,Monitor

Notebook,Diary

Notebook,Bottle

Thank you

Regards,

Sonali Saha

[barritown]

Hi @sonalisaha2310,

Can you share with me how you see the expected output?

[sonalisaha2310]

Hello @barritown ,
I have written calculated column measure with the query that  you have provided 
Now suppose the table looks like below

Name	Accessories(Calculated Column Measure)
A	Bag,Monitor,Mouse
B	Mouse
C	Monitor,Bag

The result should be 

Name	Accessories (Calculated Column Measure)
A	Bag
A	Monitor
A	Mouse
B	Mouse
C	Monitor
C	Bag

Please note, i have tried to split it with delimiter from power query the column is not getting displayed if i open power query , please help me in splitting with delimiter and expand it in rows as shows above.

[barritown]

Hi @sonalisaha2310,

Sure, if you created a table or a column in DAX, you won't see it in Power Query.

Here is a way to create a new table with expanded rows via DAX:

I didn't remove temporary columns in order to let you see how this code works - you can get rid of them with the help of SELECTCOLUMNS.

Here is the same code as plain text:

Table3 = 
VAR _tbl1 = ADDCOLUMNS ( ADDCOLUMNS ( Table2, "Accesories H", SUBSTITUTE ( [Accessories(Calculated Column Measure)], ",", "|" ) ), 
                         "No of Items", 
                         PATHLENGTH ( [Accesories H] ) )
VAR maxLen = MAXX ( _tbl1, [No of Items] )
VAR _tbl2 = FILTER ( CROSSJOIN ( GENERATESERIES ( 1, maxLen ), _tbl1 ), [No of Items] >= [Value] )
VAR _tbl3 = ADDCOLUMNS ( _tbl2, "Item", PATHITEM ( [Accesories H], [Value] ) )
RETURN _tbl3

The playground PBIX file is also attached.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown · Accepted solution]

Hi @sonalisaha2310,

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

[sonalisaha2310]

Hello @barritown  , Thank you so much this worked as column measure , can you also please include the logic of splitting the text suing comma delimiter and expand it in the form of rows ?