Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kjartank
Helper II
Helper II

Problem with conditional column

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

1 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

 

Specializing in Power Query Formula Language (M)

View solution in original post

13 REPLIES 13
KHorseman
Community Champion
Community Champion

How exactly are you trying to do this?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I'm trying with the conditional column tool.

 

ConditionalColumn.png

 

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

error.pngerror1.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It looks like this.

 

error2.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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

 

Specializing in Power Query Formula Language (M)

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

image.png

Thanks a lot! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.