Forum Discussion

AB174's avatar
AB174
Regular Visitor
3 years ago
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...
Dax Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
AB174's avatar
AB174
Regular Visitor
3 years ago

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?