cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elz
Advocate II
Advocate II

Check for null in conditional column

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

13 REPLIES 13
ryan_mulhollem
Helper I
Helper I

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
Frequent Visitor

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
Frequent Visitor

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
New Member

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

 

itvaleria
Helper I
Helper I

Thank you.

 

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

KGrice
Solution Sage
Solution Sage

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

 

NullDate.PNG

 

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?

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

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
Frequent Visitor

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
Solution Sage
Solution Sage

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.

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

Anonymous
Not applicable

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. 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.