Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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's avatar
    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

     

barritown's avatar
barritown
Icon for Solution Sage rankSolution Sage
2 years ago

Hi Anonymous,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    NameAccessories(Calculated Column Measure)
    ABag,Monitor,Mouse
    BMouse
    CMonitor,Bag

    The result should be 

    NameAccessories (Calculated Column Measure)
    ABag
    AMonitor
    AMouse
    BMouse
    CMonitor
    CBag

     

    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's avatar
      barritown
      Icon for Solution Sage rankSolution Sage
      2 years ago

      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 _tbl3

      The playground PBIX file is also attached.

      Best Regards,

      Alexander

      My YouTube vlog in English

      My YouTube vlog in Russian