Forum Discussion
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) | Name |
1 | App1,App2,App3 |
2 | App2,App6 |
3 | null |
4 | null |
Output should have all the values starts only with App* and if a row is having different value other than App* then it should be displayed as null.
Note: I want to achieve it using DAX. I tried split and merge but its not working. Any help would be appreciated.
TIA
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 _newstringThe following screenshot shows the table with the new column:
3 Replies
Anonymous , these three M operations should give you desired results
Text.Split
https://docs.microsoft.com/en-us/powerquery-m/text-splitList.FindText
https://docs.microsoft.com/en-us/powerquery-m/list-findtextText.Combine
https://docs.microsoft.com/en-us/powerquery-m/text-combineHey 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 _newstringThe following screenshot shows the table with the new column:
