Power BI dealing with N/As

[gazpage]

Hi all,

My dataset includes a column for the size of a property (in square metres). For a minority of the properties, this column shows an N/A. I need to use this column in measures, for example total property size, and then segment in a visual by say region.

In order to make that work I need to convert the column in Get Data to a number; this turns the N/As into error. At first I replaced these N/As with 0 and this worked fine for SUM etc. However I also need to stratify the properties into size buckets and these properties should go in an N/A bucket rather than the 0 - 50 sqm bucket.

Any suggestions? Should I duplicate the column and have one for measures and one for stratification?

Thanks
Gary

 

[tallan]

Gary,

Instead of replacing with 0, what happens when you replace with null?

Tom

 

[gazpage]

Hmm, so I would put everything in then check everything for blank when counting etc? It still seems to count the rows with blanks.

 

[tallan]

Gary,

Generally, a null is a better replacement for N/A than 0.

You probably already know about it, but for stratification (segmentation) have you considered the static segmentation method described here, but checking first for ISBLANK ( [PropertySize] ) to assign "N/A" to the stratification bucket?

Best regards,

Tom