Detecting null dates in Power Query

[jasonyeung87]

Hi,

I have the following function in Power Query that returns the fiscal quarter, based on the month of the date passed.

= (AnyDate as date) =>
let
    // CurrentDate = AnyDate,
    CurrentMonth = if AnyDate = null then 13
		else Date.Month(AnyDate),
    CurrentQuarter = if CurrentMonth = 1 then "Q4" 
		else if CurrentMonth = 2 then "Q1" 
		else if CurrentMonth = 3 then "Q1" 
		else if CurrentMonth = 4 then "Q1" 
		else if CurrentMonth = 5 then "Q2" 
		else if CurrentMonth = 6 then "Q2" 
		else if CurrentMonth = 7 then "Q2" 
		else if CurrentMonth = 8 then "Q3" 
		else if CurrentMonth = 9 then "Q3" 
		else if CurrentMonth = 10 then "Q3" 
		else if CurrentMonth = 11 then "Q4" 
		else if CurrentMonth = 12 then "Q4"
		else "Unknown" 
	,
    CurrentFiscalQuarter = CurrentQuarter
        in
    CurrentFiscalQuarter

It works fine if there is a valid date is passed to the function. (Our fiscal quarters start in Feb instead of Jan).

I want the function to return "Unknown" if the date passed is null (e.g. If "AnyDate" is null, then set the CurrentMonth value to the dummy value of 13 so the CurrentQuarter goes to the "else" part.). I'm using this function in a custom column and some of the dates in the recordset is null.

However I get the following error message:

We cannot convert the value null to type Date.

I was wondering how do I check whether the date is null in the above function. 

Jason

[jasonyeung87]

Hi Owen,

thanks for your help!

Sincerely,

Jason

[OwenAuger · Accepted solution]

Hi @jasonyeung87 

To fix the immediate issue, change

(AnyDate as date)

to

(AnyDate as nullable date)

I won't pretend to be an expert on this, but this ensures that null values of AnyDate can be handled.

See this article from Ben Gribaudo.

Regards,

Owen