Removing an empty value for a imported table shown in a slicer

Question

Hi,

I've imported a table and I've put the related code field in a slicer for a my pivot table. In the slicer I can see an empty box that I want to eliminate. I've tried to apply a filter for original data and powerpivot data from the powerpivot window. Moreover I've tried to uncheck the showing elements without data, but unsuccessfully. I've tried to update the imported tables after each applied change but no results.

Any suggests to me, please? Thanks

Answer 1

Filters in the PowerPivot window and in PowerPivottables work independently. Filters put in the PowerPivot window will not impact the results in your pivottables. To remove these data is to change the query in your data source or in case of a connection to an external workbook you may apply a filter in the 'Table Import Wizard'

---

Eddy N.

Answer 2

Hi EddyN,

I've already changed the query source without any results.

Any other suggests, please? Thanks

Answer 3

pscora

It is difficult to analyze the problem without file.

But be aware. When you refresh the PowerPivot data do not forget the to refresh the pivottables in Excel too. Pressing CTRL+ALT+F5 will refresh all pivottables at once.  

 

 

---

Eddy N.

Answer 4

Hi EddyN, thanks for your reply.

In my workbook I've two slicers with this behaviour. Fe for one of these I read only two values: I've seen only two values in the imported table but in the slices I can see these two values and an empty box. It is strange!

Thanks

Answer 5

Your source data must have some blank rows. Can you share your workbook?

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 6

Hi Chu Xu,

I cannot share my wb because it contains my customer data. In particular, for a slicer I read a SQL table (having a pk) with only 3 rows. In PowerPivot I've created a query to read only 2 rows, excluding the third row.

No refresh actions solve this issue.

Thanks

Answer 7

Hi there,

i'm facing a similar problem which i find it hard to understand.

1x fact table with 2 columns (to keep it simple):

   + col.1 - id_Division

   + col.2 - Sales_Amout

 

1x dimension table with the the Company Business Organization (Departments, Business Units, etc) with 3 columns:

   + col.1 - id_Division

   + col.2 - Business_Unit

   + col.3 - Business_Area

 

If i just load these two tables in my PowerPivot, then go to a worksheet and create a pivot table, selecting just the dimension table col.1 to the Values and col.2 to a slicer, i get the correct situation = 10x slicers corresponding to the 10x Business Units existing in the table, as you can see in the imagem attached.

If i create a relationship between the fact table col.1  (id_Divion) with the dimension table col.1 (id_division), and then refresh the same pivot i've just created before, i get an extra empty slicer with no values in it. IS there any reason for this to appear?

Appreciate your kind help and inputs. IF required i can share my workbook in order to get some further help.

Regards

Felino ([e-mail])

Answer 8

Hello Felino,

I'm facing a similar problem and I think I found the solution.

Maybe in your fact table (col.1 id_Division) there are some values that no mach with the dimension table col.1 (or that are blank).

So, even if your dimension table have no blank values, the slicer find it in the fact table.

Regards,

Silvia