Dealing with Null Values in a Conditional Formula

[Anonymous]

Hi,

I have a situation dealing with Null Values.  I'm importing a dataset in to Power BI.  One of the columns is DOB and is a numeric number.  In the Edit Query View I transformed the values to Date format.  I then added a new column called Age (with it's data type as years).  I then created a Custom Column in order to create Age Ranges.  Below is my syntax:

Age Range = if [Age] < 10 then "0-9"
else if [Age] >= 10 and [Age] <= 20 then "10-19"
else if [Age] >= 20 and [Age] <= 30 then "20-29"
else if [Age] >= 30 and [Age] <= 40 then "30-39"
else if [Age] >= 40 and [Age] <= 50 then "40-49"
else if [Age] >= 50 and [Age] <= 60 then "50-59"
else if [Age] >= 60 and [Age] <= 65 then "60-64"
else if [Age] > 64 then "65+"
else if [Age] = null then "N/a"
else "N/a"

The problem I'm having is that in the original dataset...some of the DOB values are imported as blank and come through as null.

In the last few lines of my syntax...you can se how I've attempted to have all null or blank values come in as "N/a"...however they still register as an 'Error' for each instance.

I'd like to clean this up so there are no errors when another co-worker opens up the documet.  How can I have Null values come in as "N/a"

Please help,

Thank you

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Icey]

Hi @Anonymous ,

Does @az38  solve your problem?

If he does, please accept his reply as a solution so that people who may have the same question can get the solution directly.

If not, please let me know.

My test result:

Best Regards,

Icey

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

[amitchandak]

isblank is used to handle null

if ([Age] < 10 ,"0-9"
,if ([Age] >= 10 &&[Age] <= 20 ,"10-19"
,if ([Age] >= 20 &&[Age] <= 30 ,"20-29"
,if ([Age] >= 30 &&[Age] <= 40 ,"30-39"
,if ([Age] >= 40 &&[Age] <= 50 ,"40-49"
,if ([Age] >= 50 &&[Age] <= 60 ,"50-59"
,if ([Age] >= 60 &&[Age] <= 65 ,"60-64"
,if ([Age] > 64 ,"65+"
,if (isblank([Age]) , "N/a"
,"N/a"))))))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

An error comes back with your solution:

"Token RightParen Expected"

[amitchandak]

if ([Age] < 10 ,"0-9"
,if ([Age] >= 10 &&[Age] <= 20 ,"10-19"
,if ([Age] >= 20 &&[Age] <= 30 ,"20-29"
,if ([Age] >= 30 &&[Age] <= 40 ,"30-39"
,if ([Age] >= 40 &&[Age] <= 50 ,"40-49"
,if ([Age] >= 50 &&[Age] <= 60 ,"50-59"
,if ([Age] >= 60 &&[Age] <= 65 ,"60-64"
,if ([Age] > 64 ,"65+"
,if (isblank([Age]) , "N/a"
,"N/a")))))))))

[az38]

@amitchandak 

it's a DAX

@Anonymous is trying to create a custom column in Power Query Editor

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

@Anonymous 

are you sure the problem is in Age Range? As I understand [Age] is custom column too..

and you have some miscontinious: 

else if [Age] >= 10 and [Age] <= 20 then "10-19"
else if [Age] >= 20 and [Age] <= 30 then "20-29"

What Age range should be for 20?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Yes.  Your are correct.  I've fixed it.  Values come in with no greater than 6 decimal places so I've adjusted the equation to account for all ranges:

if [Age] < 10 then "0-9"
else if [Age] >= 10 and [Age] <= 20 then "10-19"
else if [Age] >= 20.000000001 and [Age] <= 30 then "20-29"
else if [Age] >= 30.000000001 and [Age] <= 40 then "30-39"
else if [Age] >= 40.000000001 and [Age] <= 50 then "40-49"
else if [Age] >= 50.000000001 and [Age] <= 60 then "50-59"
else if [Age] >= 60.000000001 and [Age] <= 65 then "60-64"
else if [Age] > 64.000000001 then "65+"
else if [Age] = null then "N/a"
else "N/a"

You are also correct in that both Age and Age Range are calculated fields.  Age Range is based upon the Age column while the Age column is dependent on the DOB column.  The DOB column is not a calculated field. DOB is simply a numerical field in its original form when first brought in.

The issue I am having is that DOB has some instances where it is blank.  When it is transformed to Age...null values result.  Then, the Age Range field (and my calculation) cannot place a null value in a range...resulting in errors.  I want these errors to be given values of "N/a" and I do not know how to do that.

[az38]

@Anonymous 

right click to field and "Replace Errors" 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

@Anonymous 

is Replace error function a good solution for you as I see here https://community.powerbi.com/t5/Desktop/how-to-replace-error-in-query-editor/m-p/490379#M228507 ?

do not hesitate to give a kudo to useful posts and mark solutions as solution