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

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
KGrice Established Member
Established Member

Re: Check for null in conditional column

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?

Highlighted
elz Member
Member

Re: Check for null in conditional column

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
KGrice Established Member
Established Member

Re: Check for null in conditional column

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.

elz Member
Member

Re: Check for null in conditional column

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

itvaleria Regular Visitor
Regular Visitor

Re: Check for null in conditional column

Thank you.

 

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

Re: Check for null in conditional column

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

marucho21 New Member
New Member

Re: Check for null in conditional column

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

 

jdourley Frequent Visitor
Frequent Visitor

Re: Check for null in conditional column

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!

NGTaylor Frequent Visitor
Frequent Visitor

Re: Check for null in conditional column

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 48 members 1,052 guests
Please welcome our newest community members: