If column contains values from another column replace with valu

[tkchan08]

Hi Everyone,

If I have a table similar to below and I would like to search if any of the values in column Y exist in column x.  If it exists, I would like to replace the value with a Blank and output to a new colum.  For example, in row one column x, becomes 2+3+4+67 in the new column. 

Table1
Column X	Column Y
1+2+3+4+67	1

[v-zhangti · Accepted solution]

Hi, @tkchan08 

You can also try to solve it with Dax.

Column =
IF (
    SEARCH ( [Column Y], [Column X] ) <> BLANK (),
    REPLACE ( [Column X], FIND ( [Column Y], [Column X] ), 2, BLANK () )
)

For reference:

FIND function (DAX) - DAX | Microsoft Docs

SEARCH function (DAX) - DAX | Microsoft Docs

REPLACE function (DAX) - DAX | Microsoft Docs

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@tkchan08 ,

In Power Query create a new column

Text.Combine(List.RemoveItems(Text.Split([Column X], "+"), {[Column Y]}), "+")

In case Y is numeric column. Above will work with text column

Text.Combine(List.RemoveItems(Text.Split([Column X], "+"), {Number.ToText([Column Y])}), "+")

If need use Text.Split on Y