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


  • 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

    Monday, May 23, 2011 9:16 AM


All replies

  • 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.
    Monday, May 23, 2011 10:38 AM
  • Hi EddyN,

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

    Any other suggests, please? Thanks

    Monday, May 23, 2011 10:40 AM
  • 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.
    Monday, May 23, 2011 12:53 PM
  • 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!


    Tuesday, May 24, 2011 4:01 PM
  • 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
    Tuesday, May 24, 2011 7:19 PM
  • 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.


    Wednesday, May 25, 2011 9:30 AM
  • 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.


    Felino (

    Tuesday, September 13, 2011 12:41 PM
  • 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.




    Thursday, November 10, 2011 2:10 PM
  • Pscora,

    Is this still an issue?

    Thank you!

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, November 03, 2013 10:15 PM
  • Still a problem with me.  Felino and Silvia got the problem pretty much exactly.

    There are no blank values in the source data, it's just that there is no match in the dimension table.  In my case, I want to force this mismatch since I am doing row level security.

    Any help will be appreciated.

    Friday, January 24, 2014 6:45 PM