locked
Problem with xlnm filtered database in Powerpivot RRS feed

  • Question

  • Good evening everybody,

    I have posted this query on many forums and I have been waiting for the response for this particular query over many Excel forums but nobody has replied this.

    I have a bunch of sheets in my Excel file. When I try to bring the same to the Powerpivot environment, at some point, it will show us the list of sheets available under the pop up List of Tables and Views under which all the sheets/tables are listed out to be checked and selected.
    In that list, I have a sheet named "Salesbook". I can see the same in the List of Tables and Views as "Salesbook$" also "Salesbook$xlnm#_FilterDatabase". Where in the actual list of sheets, I have only one sheet with that name. Also I have no filters applied.

    What is the significance of this sheet/table? Is this actually exists or not, if yes why this? I can even mail post the actual Excel books I tried to work on. Please help me on this.

    Thanks for your effort.

    Friday, July 4, 2014 1:09 PM

Answers

  • Hi drxlsx,

    I would recommend ignoring the xlnm filter tables altogether and not loading them into your Power Pivot data models. To avoid creating them you'll need to avoid using auto filters in the source Excel workbook.

    With regards to the confusion with creating table relationships and using the drag and drop feature of pivot tables, could you explain this a bit further, perhaps by giving an example?


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    • Marked as answer by Charlie Liao Sunday, July 13, 2014 12:34 PM
    Saturday, July 5, 2014 12:13 PM
  • Hi drxlsx,

    In addition to AutoFilters there may be some other actions in Excel that trigger the xlnm filter table to be created. In any case, what you should take away from this is that Excel creates these worksheets internally to keep track of how these features are being used/applied. In my experience, once they are created, they're there to stay.

    As mentioned previously, you can safely ignore these tables when loading data into Power Pivot because, with the exception of Power Pivot and using development APIs to interact with workbooks, users can't interact with these worksheets directly.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Tuesday, July 8, 2014 10:45 AM

All replies

  • I can even mail post the actual Excel books I tried to work on.

    Hello,

    That would be very helpful and would speed it up. But instead of mailing you could upload the Excel Workbook to your free cloud drive from MS: https://onedrive.live.com/ and share the link to it here.

    One additional question, which version of Excel / Power Pivot are you using?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 4, 2014 4:56 PM
  • Hi drxlsx,

    This is a hidden sheet that Excel creates internally whenever you place Auto Filters on some data within a worksheet. The method that Power Pivot uses to connect to Excel workbooks happens to expose these hidden sheets but it's nothing to be concerned about. Generally speaking, you should only bother with the worksheet names that end with a '$' or named ranges which will normally appear without a '$' in the name.

    Edit: I should also add that even if you currently have no auto filters on the worksheet but they have been applied at some point during the lifecycle of the worksheet, this hidden worksheet will still be present.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn



    Friday, July 4, 2014 10:49 PM
  • Hi Michael,

    Thanks for the reply which cleared my longtime doubt. But I still am facing a confusion when creating Relationships between tables AND when working with  Pivot tables as which table to be dragged and dropped in the Pivot table Drop-zones. Should I touch those xlnm filter tables anywhere in the process of building PivotTables with PowerPivot or not ?? Is there a way that I can avoid creating these kinds of confusing tables?? What precaution to be followed to escape from this confusion??

    Thank you.

    drxlsx

    Saturday, July 5, 2014 9:07 AM
  • Thanks for the help Olaf Helper. BTW I am using Excel 2010 PowerPivot .
    Saturday, July 5, 2014 9:11 AM
  • Hi drxlsx,

    I would recommend ignoring the xlnm filter tables altogether and not loading them into your Power Pivot data models. To avoid creating them you'll need to avoid using auto filters in the source Excel workbook.

    With regards to the confusion with creating table relationships and using the drag and drop feature of pivot tables, could you explain this a bit further, perhaps by giving an example?


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    • Marked as answer by Charlie Liao Sunday, July 13, 2014 12:34 PM
    Saturday, July 5, 2014 12:13 PM
  • Michael,

    As I proceed to create relationships among different tables in the PowerPivot environment these filtered databases are showing up and I have a feeling that I am going to miss something if I ignore them completely.

    When I tested, these are getting created even if I did not apply the Auto-filters for any table. What I have is a remote price list (in 3 columns XEF2:XEH20, Product, Cost, Price) which will be applied to one of the Cost column in column G using a VLOOKUP.

    But why it is created for the reason mentioned by you? I even cleared all of the Excel real-estate using Alt+H+E+A (for Clear All). Thanks for the previous reply.

    Monday, July 7, 2014 9:15 AM
  • Hi drxlsx,

    In addition to AutoFilters there may be some other actions in Excel that trigger the xlnm filter table to be created. In any case, what you should take away from this is that Excel creates these worksheets internally to keep track of how these features are being used/applied. In my experience, once they are created, they're there to stay.

    As mentioned previously, you can safely ignore these tables when loading data into Power Pivot because, with the exception of Power Pivot and using development APIs to interact with workbooks, users can't interact with these worksheets directly.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Tuesday, July 8, 2014 10:45 AM