If statement generates an error for blank rows

[Anonymous]

So I'm using the following if statement to classify each "status" in the status column as either active or inactive. However, when there is a empty row, I get an error message for that row. The custom column still works, so I can just ignore this. But my brain won't allow it, please help me for my sanity.

I used custom column in power query to write this...

'''if Text.Contains([Status], "Assigned") or Text.Contains([Status], "In Progress") then "Active"

else if Text.Contains([Status], "Not in FORCE") or Text.Contains([Status], "Cancelled") then "Inactive"

else if Text.Contains([Status], "Awarded") then "Admin"

else null'''

[DOLEARY85 · Accepted solution]

Hi,

add this before the start of you if statement

 if [Status] = null then null else

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anonymous]

Thank you, that worked like a charm!

Just wondering, why do I have to keep the null you suggested AND the null I had at the end? Don't they do the same thing?

[DOLEARY85]

No Problem,

The first checking for null is just to remove the blank error as text.contains can't start looking for anything in a null field, so we're just eliminating it using that at all.

The last null is if anything doesn't appear in you Text.contains statements it'll just return null, if you're sure that there will always be one of the values listed in the text.contains statements then you could rewrite it to remove that part.

Personally i'd keep it for potential error handling if something in the data changes.