Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Advocate II
Advocate II

Check for null in conditional column


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

Advocate II
Advocate II

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. 

Frequent Visitor

Good to know, thanks.

Frequent Visitor

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")


Not applicable

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 

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.

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!

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.




Not applicable

Thank you.


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

Memorable Member
Memorable Member

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?

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.
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.

Memorable Member
Memorable Member

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! 🙂 

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.



Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.