Forum Discussion
Help with Power Query custom column with multiple conditions and Text.Contains
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"
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
4 Replies
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
That is what I have. Any row that has a null in one of those fields produces an Error.
If I change the nulls to "" beforehand, it works without a problem. Not sure what problems or concerns there may be with doing that.
