topic Re: Creating new column without selected values from original column in DAX Commands and Tips

Creating new column without selected values from original column

AB174 — Wed, 05 Apr 2023 12:38:53 GMT

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!

Re: Creating new column without selected values from original column

Greg_Deckler — Wed, 05 Apr 2023 12:46:17 GMT

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

Re: Creating new column without selected values from original column

AB174 — Wed, 05 Apr 2023 12:57:41 GMT

Great! Thank you so much!

Re: Creating new column without selected values from original column

AB174 — Wed, 05 Apr 2023 14:25:32 GMT

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?