Null Values in Date Column

[Steffen1]

Dear Community, 

I´ve already seen some post on this topic, but it kinda not solved my problem. 

I have a report that shows me the ship date for an order. However, if the order hasn´t shipped yet, the ship date will be blank - hence the value is null. 

In my query i have a custom column where I want to do a logic check if there is a specific ship date as value or if the value is null.

For the condition: if [shipdate] <> null then true-expression else false-expression -> the logic check works and returns the "true result" correctly, but for the "false result" I receive the error message: "[Expression.Error] The value "null" cann´t be converted to type logical"

If I change the condition as following: if [shipdate] = null then true-expression else false-expression -> it returns the "true result" correctly again and the error message as the "false result"

Full code: 

if [shipdate] <> null then Date.AddDays([shipdate],[deliverytime]) else Date.AddDays([nextpickup],[deliverytime])

if [shipdate] = null then Date.AddDays([nextpickup],[deliverytime]) else Date.AddDays([shipdate],[deliverytime]) 

Now I have diffren questions which would help me to understand this: 

1. Is there any value which I could use in the ship date column to replace the null value without using a dummy date like 01.01.1970 and keep it blank? 

2. Does m-language support any fuction to work with this null value, which would allow me to have a working if-expression? 

Thank you very much in advance. 

Best regards

[Steffen1]

Hi @Daryl-Lynch-Bzy 

thank you very much for your quick reply and the tip to check if its really containing null.

Im not an expert but for me it looks like it does contain null? 

Dax could be an option but my preference would be within the power query/M, if possible?

Thanks 

Steffen

[Daryl-Lynch-Bzy]

Hi @Steffen1 - it looks like you need to change something in Power Query to allow the Dax to work.  If you see the following images, the left contains (blank).  This is the desired result in data model.  Contrast this with the right, which is populated with text string "(Null)"

This is how the (Blank) and "(Null)" text string appears in Power Query.  We need the result on the left.

I would suggest using "Replace Values" to turn the string (Null) to null.  keep the null in lowercase so Power Query recognises this as null instead of "Null" or "NULL".

Also make sure that Data Type for the column is Date.

Many thanks

Daryl

[Steffen1]

Hi @Daryl-Lynch-Bzy,

in my data view it shows me as you said, the blank, which is the desired result but I wonder if there is a solution within the Power Query to deal with the null values via M-Language and not DAX?

Thank you very much for the support

Steffen

[Daryl-Lynch-Bzy]

Hi @Steffen1  - You could try changing the DAX to use the following:

NOT ( ISBLANK( [shipdate] ) )

Note the ISBLANK will return TRUE and NOT will swap this to FALSE.

However, I just want to double check that the Date is empty.  It doesn't contain "Null".

Many thanks

Daryl