Creating New Column Based on a Set of Text Strings

Hi, NewBIEnthusiast 

Based on your description, you may click ‘Edit Query’, go to Query Editor, enter ‘Query Settings’, right-click last step, select ‘Insert Step After’.

Then you may input ‘Table.AddColumn(#"Changed Type", "Custom", each if [Color_Name] = "red" or [Color_Name] = "blue" or [Color_Name] = "red;blue" or [Color_Name] = "blue;red" then  "Exclude" else "Valid")’ in the formula area.

Result:

As a workaround, you can also create a calculated column as follows.

IsExclude =

IF(

    EXACT('Table'[Color_Name],"red")||

    EXACT('Table'[Color_Name],"blue")||

    EXACT('Table'[Color_Name],"red;blue")||

    EXACT('Table'[Color_Name],"blue;red"),

    "Exclude",

    "Valid"

)

Result:

Best Regards,

Allan

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