Handle Null in IF Statement

[Rich_Wyeth]

Hi,

I have the following column witha multiple if:

= Table.AddColumn(#"Due Days", "Due Category", each if [Due Days] >= 0 then "Overdue" else if [Due Days] <= -365 then "Over 12 Months" else if [Due Days] < -180 then "6-12 Months" else if [Due Days] < -90 then "3-6 Months" else if [Due Days] < -30 then "1-3 Months" else "1 Month")

I need to incase an statement that says if [Due Days] = null then Due Category will read null, as at present it is returning error. I have tried to do this, but I can't seem to get it right.

How to do create the check whilst adding the column?

Thanks in Advance

[Angith_Nair · Accepted solution]

Hi @Rich_Wyeth 

Use the below M Code:

= Table.AddColumn(#"Due Days", "Due Category", each 
    if [Due Days] = null then null 
    else if [Due Days] >= 0 then "Overdue" 
    else if [Due Days] <= -365 then "Over 12 Months" 
    else if [Due Days] < -180 then "6-12 Months" 
    else if [Due Days] < -90 then "3-6 Months" 
    else if [Due Days] < -30 then "1-3 Months" 
    else "1 Month"
)

[FreemanZ · Accepted solution]

hi @Rich_Wyeth ,

try like:

= Table.AddColumn(

    #"Due Days", 

    "Due Category", 

   each 

    if [Due Days] >= 0 then "Overdue" 

    else if [Due Days] <= -365 then "Over 12 Months" 

    else if [Due Days] < -180 then "6-12 Months" 

    else if [Due Days] < -90 then "3-6 Months" 

    else if [Due Days] < -30 then "1-3 Months" 

    else if [Due Days] =null then null

    else "1 Month"

)

[Angith_Nair · Accepted solution]

Hi @Rich_Wyeth 

Use the below M Code:

= Table.AddColumn(#"Due Days", "Due Category", each 
    if [Due Days] = null then null 
    else if [Due Days] >= 0 then "Overdue" 
    else if [Due Days] <= -365 then "Over 12 Months" 
    else if [Due Days] < -180 then "6-12 Months" 
    else if [Due Days] < -90 then "3-6 Months" 
    else if [Due Days] < -30 then "1-3 Months" 
    else "1 Month"
)

[Rich_Wyeth]

Thank you, works a treat! I was so transfixed on an if, I hadn't thought of that! Thank you!