Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

extract values from comma separated values

Hi All,   I have data in below format : ID(unique) Name 1 App1,App2,App3,Code1 2 App2,App6,DevCode 3 null 4 Abc   The output should be : ID(unique...
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Anonymous ,

     

    as requested here is a DAX solution.

     

    Based on my sample data:

    I created this calculated column, as DAX does not allow to overwrite existing values.

    Name new = 
var _path = SUBSTITUTE('Table'[Name] , "," , "|" )
var _pathlength = PATHLENGTH(_path)
var _newstring = 
    CONCATENATEX(
        FILTER(
            ADDCOLUMNS(
                GENERATESERIES(1 , _pathlength)
                , "_name" , PATHITEM(_path, ''[Value])
            )
            , LEFT([_name], 3) = "App"
        )
        , [_name]
        , ","
    )
return
_newstring

    The following screenshot shows the table with the new column:

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey Anonymous ,

 

as requested here is a DAX solution.

 

Based on my sample data:

I created this calculated column, as DAX does not allow to overwrite existing values.

Name new = 
var _path = SUBSTITUTE('Table'[Name] , "," , "|" )
var _pathlength = PATHLENGTH(_path)
var _newstring = 
    CONCATENATEX(
        FILTER(
            ADDCOLUMNS(
                GENERATESERIES(1 , _pathlength)
                , "_name" , PATHITEM(_path, ''[Value])
            )
            , LEFT([_name], 3) = "App"
        )
        , [_name]
        , ","
    )
return
_newstring

The following screenshot shows the table with the new column:

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you. It worked. 🙂