none
'SheetName!_FilterDatabase' method in Excel 2016 RRS feed

  • Question

  • Hi,

    I've just discovered that Excel 2016 doesn't support 'SheetName!_FilterDatabase' method to take a data from worksheet :-(

    That was very useful and flexible in query design mode in excel 2010 and 2013.
    Is this a bug or intentionally... ?

    Regards

    Friday, August 28, 2015 8:59 AM

Answers

  • Thank you for surfacing this issue. We will add this to our backlog post Excel 2016 release. To better prioritize it in the upcoming updates that will follow the release, can you share more feedbacks about the importance of for this capability vs.the import from tables and named ranges?
    Tuesday, September 1, 2015 5:59 PM

All replies

  • Hi Bill,

    if this isn't related to Power Query, you might better move your question to the Office-Excel-Forum instead :-)

    https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel


    Imke Feldmann TheBIccountant.com


    Saturday, August 29, 2015 4:05 PM
    Moderator
  • Hi Imke :-)

    Thanks for your reply.

    This construction is strictly related to Power Query. We can get data from current worksheet by table, named range, print area and also by selecting a range for autofilter.

    I made a video about this almost a year ago ( but in polish). You can see the use of this in some Mike Girvin's (ExcelIsFun) videos.
    Here is a link to the last one: ExcelIsFun video
    I frequently use 'sheetname!_FilterDatabase' on first stage of design the query or for one-time job.

    Regards :-)  sq

    Sunday, August 30, 2015 8:34 PM
  • Hi Bill,

    not sure if I got it :-)

    So what's the difference from this method compared to what you see if you simply try to import your current workbook in a query (From file - from Excel) : Then you will also see all these element & can choose one.

    And if you want to choose more than one, you go via: file - from Folder instead. This will give you the chance to open multiple elements at once. Everything related to your current workbook.

    ? & sq :-)


    Imke Feldmann TheBIccountant.com

    Monday, August 31, 2015 4:43 AM
    Moderator
  • Hi Bill,

    had another look at it (in your workbook here: https://www.youtube.com/watch?v=U49bDZfFXaI

     Brilliant solution, BTW :-))

    So you've used this technique to pass data to Power Query that is actually not a table nor a named range, so doesn't pop up if you use Excel.CurrentWorkbook() to show the content of your current workbook. Yes, this seems pretty useful.

    So this is a question for the MS experts then :-)


    Imke Feldmann TheBIccountant.com

    Tuesday, September 1, 2015 10:13 AM
    Moderator
  • Hi Imke :-)

    Thanks for your kind words :-)

    You explained exactly what I meant :-).
    Unfortunately, this functionality is not available in excel 2016 (PQ built-in). :-(

    I'm waiting for MS answer.... hope, here.

    Regards

    Tuesday, September 1, 2015 3:07 PM
  • Thank you for surfacing this issue. We will add this to our backlog post Excel 2016 release. To better prioritize it in the upcoming updates that will follow the release, can you share more feedbacks about the importance of for this capability vs.the import from tables and named ranges?
    Tuesday, September 1, 2015 5:59 PM
  • Thanks Gill for your answer :-)

    As I can see this is a less known method.

    Next week I will try to make a short video about this.
    For sure - we can replace 'SheetName!_FilterDatabase' method by another one but in some cases it is a time saving method (and very flexible - especially for testing the queries).

    Regards

    Friday, September 4, 2015 9:34 AM
  • I have confirmed that the behavior is indeed a bug, and is seemingly related to the Power Query bits integrated into Excel 2016.

    _FilterDatabase is a hidden name that is created after you filter a range using the Excel Advanced Filter, or after you apply an AutoFilter to a range (the hidden name is retained for the range even if you subsequently remove the AutoFilter arrows). Additionally, you can you use _FilterDatabase as a range name in VBA. In Excel, if you press the F5 function key and enter _FilterDatabase (the name is not case sensitive) into the Reference box of the Go To dialog box that appears, Excel will highlight the _FilterDatabase range.

    In Excel 2010 or 2013, if you highlight a range with this hidden name and you choose to import data from Table, Power Query creates a query from the range with the following source expression:

    Source = Excel.CurrentWorkbook(){[Name="Sheet2!_FilterDatabase"]}[Content],

    The _FilterDatabase name is applied at the sheet level, and so Excel prepends the name with the sheet name. The point to note here is that Power Query uses the hidden name (if one exists) in the query created from the imported range, even if another name is defined subsequently for the range through the UI.

    In Excel 2016, attempting to import a range with the _FilterDatabase name results in an following error: 

    Expression.Error: We couldn't find an Excel table named 'Sheet2!_FilterDatabase'.
    Details:
        Sheet2!_FilterDatabase

    The workaround would be to create a table from the range. In this case, the _FilterDatabase name is retained in Excel, but the data import uses the table name.

    Bill, I'm wondering what circumstances exist that would prevent you from converting your _FilterDatabase range to a table, and what advantages there might be for using the _FilterDatabase name instead of a table name.

    Saturday, September 5, 2015 4:21 PM
  • Hi all,

    I am still experiencing this problem. If not sorted every month I need to convert a file with about 10 tabs onto tables after a file I receive. This is very time consuming and doesn't make sense when it could be done very quickly with = Excel.CurrentWorkbook() 

    Any help will be greatly appreciated. 

    Nat

    Friday, January 17, 2020 10:56 AM