Forum Discussion
Concatenate multiple columns without repeating the duplicate values , please help
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 ,
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.
Solution SageHi 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