Error IN FORMULA

[sks2701]

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!

[Greg_Deckler · Accepted solution]

@sks2701 perhaps add an if statement (I would do it as the first) such that [#"Employees"] = null then "N/A"

[v-yingjl · Accepted solution]

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.

[Greg_Deckler · Accepted solution]

@sks2701 perhaps add an if statement (I would do it as the first) such that [#"Employees"] = null then "N/A"