How to remove items from a list if contained in another list

[dmoresco]

I have one column which is a list of items. I have a second column which is an exclusion list. I want to create a third column (or modify the first) to remove any item present in the second column. Please see example below. Thanks in advance for any help.

Column 1	Column 2	Result
X,M,J,Z	Z	X,M,J
B,C,V		B,C,V
C,D,F,G	F,G	C,D

[Zubair_Muhammad · Accepted solution]

@dmoresco 

Using M/QueryEditor/Power Query...you can use this custom column

=Text.Combine(
List.RemoveItems
(Text.Split([Column 1],","),Text.Split([Column 2],","))
,
",")

[Zubair_Muhammad · Accepted solution]

@dmoresco 

As a DAX calculated column you can use.

Please see attached file with both ways

Calculated Column =
VAR List1 =
    SUBSTITUTE ( [Column 1], ",", "|" )
VAR T1 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( List1 ) ),
            "Item", PATHITEM ( List1, [Value], TEXT )
        ),
        "Item", [Item]
    )
VAR List2 =
    SUBSTITUTE ( [Column 2], ",", "|" )
VAR T2 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( List2 ) ),
            "Item", PATHITEM ( List2, [Value], TEXT )
        ),
        "Item", [Item]
    )
RETURN
    CONCATENATEX ( EXCEPT ( T1, T2 ), [Item], "," )

[Zubair_Muhammad · Accepted solution]

@dmoresco 

Using M/QueryEditor/Power Query...you can use this custom column

=Text.Combine(
List.RemoveItems
(Text.Split([Column 1],","),Text.Split([Column 2],","))
,
",")