Forum Discussion
Error IN FORMULA
Hi - I have a Total Employees column , however some rows doesnt have value and therefore the power query automatically shows null, now am trying to create a new custom column as Employee range with the below formula:
if [#"Employees"] <= 100 then "Under 100" else if [#"Employees"] <= 500 then "101 - 500" else if [#"Employees"] <= 1000 then "501 - 1000" else if [#"Employees"] <= 2000 then "1001 - 2000" else if [#"Employees"] <= 3000 then "2001 - 3000" else if [#"Employees"] > 3000 then "Over 3000" else "N/A"
the formula is working fine , however for all the records where it was null (in the Employee column) the value in the custom column shows error, howeevr i would want it to be shown as N/A
please can someone help me- thank you!
sks2701 perhaps add an if statement (I would do it as the first) such that [#"Employees"] = null then "N/A"
Hi sks2701 ,
Besides check the null value at first, you can also replace errors with "N/A":
= Table.ReplaceErrorValues(#"Added Custom", {{"Custom", "N/A"}})Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
sks2701 perhaps add an if statement (I would do it as the first) such that [#"Employees"] = null then "N/A"
Hi sks2701 ,
Besides check the null value at first, you can also replace errors with "N/A":
= Table.ReplaceErrorValues(#"Added Custom", {{"Custom", "N/A"}})Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.