If statement with multiple nulls

[NZCraig]

Hi all,

I am new to M language and have gone down a rabbit hole trying to create a column to return a single date from many possiblities based on a heirarchy.

The plain language version of what I am trying to acheive is:

If the [assignment.scheduledate] field is not empty, and the [assignment.scheduledate] is greater than or equal to the [schedstart] field, then retun the [assignment.scheduledate].

 If the [assignment.scheduledate] field is not empty, and the [assignment.scheduledate] is less than the [schedstart] field, then retun the [schedstart] field.

If the [assignment.scheduledate] field is empty, and the [schedstart] field is not, return the [schedstart] field.

If the [assignment.scheduledate]  field and [schedstart] fields are both empty, return the [targstartdate] field.

I've tried various versions of the statement below but had no luck. Any help would be appreciated!

Thanks.

if [assignment.scheduledate] <> null and ([assignment.scheduledate] => [shedstart]) then [assignment.scheduledate] else
if [assignment.scheduledate] <> null and ([assignment.scheduledate] < [shedstart]) then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] <> null then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] is null then [targstartdate] else null)

[mahoneypat · Accepted solution]

Your statement was very close!  Two typos - 1. [schedstart] was spelled [shedstart] in two places and 2. you typed "=>" instead of ">=", so M thought you were starting to type a function.  Here is the code that works:

if [assignment.scheduledate] <> null and ([assignment.scheduledate] >= [schedstart]) then [assignment.scheduledate] else
if [assignment.scheduledate] <> null and ([assignment.scheduledate] < [schedstart]) then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] <> null then [schedstart] else
if [assignment.scheduledate] is null and [schedstart] is null then [targstartdate] else null

If it works, please mark this as the solution.  Please let me know if any questions.

[NZCraig]

Thanks so much, I had gotten so far down the rabbit hole trying to work out nested statements, I missed the most obvious mistakes!

Really appreciated.