Problem with conditional column

[kjartank]

Hi.

I have a problem making Conditional Columns work.

Have a column with values from 0-100 and some blanks. I just want all the rows that contain a number to have text and the blanks to be empty. PowerBI won't accept making a null into a null somehow. Is there a workaround?

Thanks in advance

[KHorseman]

How exactly are you trying to do this?

[kjartank]

I'm trying with the conditional column tool.

It just won't accept a blank as a blank.

[KHorseman]

Type null in that last box. Lowercase, no quotation marks or anything.

[kjartank]

I've tried that and it just wont accept it. I get an error.

[KHorseman]

Show me the settings for the custom column again now that you've changed them.

[kjartank]

It looks like this.

[KHorseman]

Is there a Change Data Type step after you add this column? When I create a column with the same settings it works fine.

[kjartank]

No, There are no further steps. And if I change the type manually, the errors persist. I've created conditional columns before, but have never had this issue.

[KHorseman]

I can't replicate that error. Maybe add another condition that checks for null values in your satisfaction score column first?

[kjartank]

I have tried it. Have no idea why it won't work. I just might have to duplicate the column and replace all the values.

[MarcelBeug · Accepted solution]

The cause of the problem is that you test the values if they are > 0, which won't work for nulls.

Options:

• Test if the value = null
• Test if the value <> null
• Test if the value is a number (via "Add Custom Column", not "Add Conditional Colum")

= if [Satisfaction Score] = null then null else "String"

= if [Satisfaction Score] <> null then "String" else null 

= if [Satisfaction Score] is number then "String" else null

[Anonymous]

I tested for null values first and then went on to check the values.  @Anonymous's answer helped me with this. Hope this works for you guys too. 

[kjartank]

Thanks a lot! 🙂