Check for null in conditional column

[elz]

Hi,

I am trying to create a conditional column for a date/time field and need to check to see if the field is null.

What should I enter in the Value field on the Add Conditional Column dialog box?

I tried null, 'null' and leaving the Value field empty, but none of them worked

There are 2 other conditions dor the date field (they work).

I then tried using the "Otherwise" option on the dialog box, thinking that if the date field did not meet either of the conditions, that the text i entered in the Otherwise field would be displayed in the new conditional column field, but that didn't happen either.

I checked the code created by this dialog box and the Otherwise criteria is there, so I do not understand why it isn't being executed.

I also noticed that even though I save the file, the Operators that I select are not there when I re-open the Add Conditional Column dialog box.
Has any one else noticed this?

I'm hoping someone can tell me how to resolve checking for null values in a date field and how to use the Otherwise option to display a message, when none of the conditions are met.

thank you
Tracy

[blodus]

The way to check for NULL values (as of 2024) in a column or measure calculation is to use the BLANK() function. Not sure when it was added. 

I know this is an old post but this is the first result in search results. 

[LC_CCS]

Good to know, thanks.

[LC_CCS]

Same question here. The column I am looking at is a Text column. Comparing to null does not work.

I solved this by, in the New Conditional Column dialog editor, putting any value in the string, then editing the M directly in the Formula bar and removing all text between the quotes:

Example: Start with:

= Table.AddColumn(#"Changed Type1", "My New Column", each if [Timecard Pay Code] = "temporary value" then "R" else "OT")

and change to:

= Table.AddColumn(#"Changed Type1", "Time Type", each if [Timecard Pay Code] = "" then "R" else "OT")

[Anonymous]

Hi -

If you right click on the column that contains null values > Replace Values > Change null to a new text (for example I used "OS is Blank" > Then run the conditional column against your new text value... the query will work.

#"Replaced Value" = Table.ReplaceValue(#"Removed Columns4",null,"OS is Blank",Replacer.ReplaceValue,{"computer.operatingSystem"}),

- Ryan 

[atifmir]

I just had the same issue and was scratching my head to why the null statement didn't work. After coming across this thread and putting null in the first statement, it worked a treat, thank you.

I was wondering, isn't this something they can't fix? Seems relatively straightforward.

[jdourley]

Thanks a lot for this post.  When learning a new program and you use correct logic/context, it is really frustrating spend so much time to only find out there is a tribal work around.

I put the null value in the first line and all of my errors went away as well.

Thank you!

[marucho21]

I just ran across this myself, when creating a synthetic key from multiple fields, with the use of concatenation. Once completed, the synthetic key was used against a singular field in a conditional column, whereby if the synthetic was null then the alternative was chosen.

The new, conditional column would fail.

The root cause was that one of the fields used to crate a synthetic key was itself a null at times. Thus the key then failed. That then made the conditional column fail - a domino effect. I.e. in SQL this would not occur so it took me forever to figure this out. 

Microsoft has a problem here for sure.

Mariusz

[Anonymous]

Thank you.

I had the same issue today. But now I know the trick thanks to you 🙂

[KGrice]

Using the null in the conditional column should work fine. Here's an example:

You can see in the Date column that it actually shows null. Do you see the same thing in the column you're checking? Or is it something like a blank, or maybe a single space?

[augustindelaf]

very good solution.

MSFT should propose more arguments in the List "Equals [...] Contains..." for the conditional columns.

it should propose "is empty" and "is not empty'

thank you anyway.

the solution is very easy but it seemed quite hard to figure out

[elz]

Hi KGrice,

Yes my date field shows null.
Because of your reply, I went back and created a new conditional column, with just this criteria and that worked.

Then something told me to add this criteria with the other criteria I'm using, but to put this check for null as the first criteria and low and behold, that worked. So I guess for some strange reason, the null criteria only works (at least for me), if it is the first row.

I guess I can live with that, it's just a bit quirky.

Thank you.
Tracy

[NGTaylor]

Thank you for this.  Been stuck on it for a while and moved the null check to first condition and now it works fine.

[KGrice]

Thanks for trying that out. I tried as well and got the same issue. When I added a second condition to my column, it worked fine with the null check as the first step. When I moved the null check down to the second position, I get an error and the column wouldn't work.

Can't think of an intended reason for this. Sounds buggy.

[Anonymous]

@KGrice 3 years later and it seems that what you said still holds true. Anyways thanks to past you for the help! 🙂 

[Anonymous]

This can be explained simply. The compiler cannot verify anything if there are null values, as null values return an error when an operation is performed on them. The null type is a distinct type, it is not a string or numbers. If you do not test for null values first, then it creates an error since you are asking the compiler to perform the operation on null values. Once the null values are out of the equation, the compiler can execute any other operations on the other fields. Always remember to think about the logical hierarchy when testing conditions. 

[elz]

hi kgrice,

yesterday, i submitted a help ticket because it does seem like a bug, that at least this null condition doesn't work if its not the first condition.

if i get a solution, i will post it.

tracy