Help with Power Query custom column with multiple conditions and Text.Contains

[Alicia_Anderson]

I am trying to create a Custom Column in Power Query that looks for a text string in any of three different text fields.  My main issue is that any of the fields may contain a null value and this is causing errors.   

For example:  I want to set a new column, IsRelease, to "Y" if Release1 or Release2 or Release3 contains "ABC" else set to "N".

Release1	Release2	Release3	IsRelease
ABC	null	null	Y
ABC	XYZ	null	Y
ABC	null	ABC	Y
XYZ	ABC	DEF	Y
null	null	ABC	Y
null	null	null	N
XYZ	null	XYZ	N

I need help with the proper way to write this...

If (Release1 Text.Contains "ABC" or

Release2 Text.Contains "ABC" or

Release 3 Text.Contains "ABC" ) then "Y", else "N"

[BA_Pete]

Hi @Alicia_Anderson ,

Try this:

if Text.Contains([Release1], "ABC")
    or Text.Contains([Release2], "ABC")
    or Text.Contains([Release3], "ABC")
then "Y"
else "N"

Pete

[Alicia_Anderson]

That is what I have.  Any row that has a null in one of those fields produces an Error.

[Alicia_Anderson]

If I change the nulls to "" beforehand, it works without a problem.    Not sure what problems or concerns there may be with doing that.

[BA_Pete · Accepted solution]

Hi @Alicia_Anderson ,

Try this instead:

try
if Text.Contains([Release1], "ABC")
    or Text.Contains([Release2], "ABC")
    or Text.Contains([Release3], "ABC")
then "Y"
else "N"
otherwise "N"

This gives the following output:

Pete