Forum Discussion
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 | ||
| 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
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 resBest Regards,
Alexander
5 Replies
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 resBest Regards,
Alexander
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 ?
Hi Anonymous,
Can you share with me how you see the expected output?
Hello barritown ,
I have written calculated column measure with the query that you have provided
Now suppose the table looks like belowName 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.
Hi Anonymous,
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 _tbl3The playground PBIX file is also attached.
Best Regards,
Alexander