Exclude (blank) from PivotTable row label

[FrankieGTH]

Hello,

I have a real strange problem. I'm attempting to remove (blank) from a pivot table row label, I don't wish to untick (blank) as that method would not add any new labels when new names are added to the data set and I refresh my pivot. I youtubed and found this video -

The solution suggested was going to Label Filters > does not contain > (blank). This did not work. Upon further digging, when I typed the word 'blank' into the search bar I got no results, similarly, no results come up when I search for ( or ).

What the heck? There are definitely blanks in my dataset, yet when I search for 'blank' in row labels it says it isn't there. Has anyone come across this problem before?

 

[Alex Blakenburg]

Can you show us a picture of how you are seeing it in the Pivot Table.
Also how does it appear in the drop down of the Pivot Table Filter list.

In the below (blank) is an empty cell in the Data, while the other one is "" in the data.

I have no idea why this works so no guarantees but a Label Filter of Greater Than or Equal To 0, eliminated them both for me.

 

[FrankieGTH]

Can you show us a picture of how you are seeing it in the Pivot Table.
Also how does it appear in the drop down of the Pivot Table Filter list.

In the below (blank) is an empty cell in the Data, while the other one is "" in the data.

I have no idea why this works so no guarantees but a Label Filter of Greater Than or Equal To 0, eliminated them both for me.


View attachment 39691

Hello Alex,

Thanks for reaching out! I tried the two methods you mentioned and still no joy. Apologies, I cant interpret what you mean when you are asking for a picture. Do you want a picture of the Pivot Table or the PT fields?

For extra clarity, the Pivot Table is run from a data model (with relationships). I think this might be some of the difficultly. When I run a Pivot Table from the original table on the worksheet, the solution I mention in my original post works fine. However, it doesn't seem to work using data from the data model.

 

[Alex Blakenburg]

The data model is quite a different proposition and I can't get the Does not Equal (blank) to work.
But the Greater than or equal to zero still seems to work.

Do you think this is what you did ?

 

[FrankieGTH]

I think your Greater than or equal to zero method works because you are working with zip codes, I'm working with text fields.

Most annoying! Does not Equal (blank) method works for single table Pivot Tables, but not Data Model/Power Pivot...

 

[Alex Blakenburg]

I intentionally included some ZipCodes with an alpha prefix and it still works.

However if you can't get it to work there is an alternative.
Go into your data model and Add Column.
Put a formula similar to this into the Field. I don't know your Table name so for the 2 Name fields simply click on the Column that currently has the name.
Change the column name to a variation of UUK Name.
Use the new column in your pivot instead of the current column.
The apply the label filter for not equal to [No Name] or whatever you end up using.

Donors was my table name it will change when you select the UUK Name field.

=if(Donors[UUK Name]="","[No Name]",Donors[[UUK Name])

Dealing with Blanks in Your Data Model

 

[FrankieGTH]

Thanks Alex, that works well. Cheers for all your time.

 

[Alex Blakenburg]

No problem. Glad to have been able to help and thanks for letting me know.