Forum Discussion
Solved
Creating new column without selected values from original column
Hi all,
I'm trying to use DAX to remove the values in each row of this column that have multiples and then place the remaining values in a new column. E.g if 20 appears twice in a row then remove them both and give the remaining values in a seperate column
Alternate example:
Column 1 Column 2
1,2,3,4,5,2,3,7,8 1,4,5,7,8
Would appreciate any help!
AB174 Sure:
Column 2 = VAR __Column1 = [Column1] VAR __Num = LEN(__Column1) - LEN(SUBSTITUTE(__Column1, ",", "")) + 1 VAR __TableText = SUBSTITUTE(__Column1, ",", "|") VAR __Table = ADDCOLUMNS( GENERATESERIES(1, __Num, 1), "__Value", PATHITEM(__TableText, [Value]) ) VAR __Table1 = ADDCOLUMNS( __Table, "__Num", VAR __Value = [__Value] VAR __Result = COUNTROWS(FILTER(__Table, [__Value] = __Value)) RETURN __Result ) VAR __Result = CONCATENATEX( FILTER( __Table1, [__Num] = 1 ), [__Value], ",") RETURN __Result
3 Replies
- Greg_Deckler
Community Champion
AB174 Sure:
Column 2 = VAR __Column1 = [Column1] VAR __Num = LEN(__Column1) - LEN(SUBSTITUTE(__Column1, ",", "")) + 1 VAR __TableText = SUBSTITUTE(__Column1, ",", "|") VAR __Table = ADDCOLUMNS( GENERATESERIES(1, __Num, 1), "__Value", PATHITEM(__TableText, [Value]) ) VAR __Table1 = ADDCOLUMNS( __Table, "__Num", VAR __Value = [__Value] VAR __Result = COUNTROWS(FILTER(__Table, [__Value] = __Value)) RETURN __Result ) VAR __Result = CONCATENATEX( FILTER( __Table1, [__Num] = 1 ), [__Value], ",") RETURN __ResultGreat! Thank you so much!
Greg_Deckler Thanks again for the formula. Apologies to bother again but is there a method to do this within power query? I've just realised i'm going to need to split the column by the delimiter so i can use a User Information list alongside it but currently can't because of this being a calculated column?
