Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

If statements with multiple null conditions

Hi,

 

I am slowly learning Power Query M, but I've gone down a rabbit hole and neeed some help to correct my thinking please! I need to return a column that provides a single date from multiple date fields, based on a heirarchy.

 

The plain language version is:

If the [assignment.scheduledate] date field is not empty, return this date, unless the [schedstart] field has a date and this date is greater than the [assignment.scheduledate].

If the [assigment.schedule] date is not empty then return the [schedstart] date field. 

If the assignment.schedule date and the schedstart date fields are both empty, return the targstartdate field.

 

I've tried many variations of the below, but cannot seem to get an error in the results when there is an [assignment.scheduledate] . Any help would be very much appreciated!

 

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)

 

1 Reply

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support

    Hi Anonymous , 

    What is your error information of above expression? I think you need to delete the ")" at the end of expression. In addition, if possible, could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.