Hide pivot table columns where all values are null but show 0s

[rbartley]

Hi everyone,

I have created a pivot table and have a mix of positive values, nulls and zeros in the data columns.  I would like to be able to display the following:

1) For columns where all values are null, do not display

2) For columns with non-null values display nulls as '-', 0 as 0 and positive values as 0 000 00.##.  

I tried using the measure formula: if(Not IsNull(Value),Value), but this still displays the columns where all records are null if I check the Add-ons/Data handling/Include zero values and if I uncheck it, the 0 values disappear.

Any ideas?

Thanks in advance.

[rbartley]

Unfortunately, this won't work either as I want to show NULL values for years where not all the values are NULL.

[rbartley]

In theory, this formula should work: If(Sum(Len(Value))>0,Value,-1), but the restriction on the Year column does not remove the -1 columns.  

[rbartley · Accepted solution]

I found a way of achieving this by converting the value to text:

If(Value=0,Text('0'),Text(Value))

The pivot table now shows nulls and zeros, but hides 'empty' columns.

[Shivam_Sahu]

Could you please attach an example here.