Forum Discussion
Helper IHandle Null in IF Statement
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
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" )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"
)
3 Replies
Angith_NairContinued Contributor
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_WyethThank you, works a treat! I was so transfixed on an if, I hadn't thought of that! Thank you!
FreemanZSuper User
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"
)