Do you use (legacy) Get External Data features?

Hey everyone,

A contact at Microsoft is looking for people who DO NOT use Power Query extensively, but DO use “legacy” methods to get external data into Excel.  These “legacy” methods include using:

  • Any/all of the commands from Data (Tab) –> Get External Data (group)

SNAGHTML5cf2dc50

  • VBA to retrieve, clean up and/or land data in Excel

In his own (okay, slightly edited) words, my contact is looking for users:

…who are importing data using the classic Get-Data Excel capabilities and have very little (or zero) familiarity with Power Query. [I’d like to] get their feedback on new ribbon sketches.

UPDATE:  My contact at Microsoft now has sufficient users for his study, so no more users will be accepted at this point.  Thanks!

This is a cool opportunity to get in touch with Microsoft and give your impressions and feedback on some potential designs that they are thinking about for future product improvements.  If you are interested and are not a heavy Power Query user, just drop a comment below (please don’t include your email.  I can access that via the blog control panel.)

16 thoughts on “Do you use (legacy) Get External Data features?

  1. I just started using Power Query about 6 or 8 months ago. Still using it very sparsely. The majority of my data pulls are done through custom SQL passed through Excel using a "home built" add-in.

  2. Why yes, I *do* use these features, including using VBA to scrape data from HTML tables 🙂

  3. Late to the Party - I use PQ where the client is ready to install it (2010/2013) - This is usually a no go if IT is involved
    Hardly any of my Clients are on 2016 - Some are on 2007 - So then the "Pre-historic" - Get External Data is the only option - combined with VBA to clean Data

  4. There are couple of scenarios where the Old still beats the New

    For example the "From Web" option from External Data - launches a mini - Internet explore inside Excel and lets you freely navigate to a web page of your choice
    The From Web of Power Query forces you to either type of copy paste a URL from some where else

    Also using the legacy option you could pull data from a OLAP Cube File (.cub) stored locally on your machine
    PQ has no such option - which is surprising

    The PQ team has made great improvements to the UI over a period of time - but some times they slip (under pressure from the marketing folks) - Like calling it "Get and Transform" in 2016 or deprecating a "great" feature like Data Catalog search in 2016....

  5. Hey everyone. I've submitted your emails to him. If you're selected for his study, he'll be in contact with you.

    He also let me know that he's got enough people for his study now, so no further emails will be submitted.

    Thanks all!

  6. I hope your contact at MS reads your blog. Here are some comments:

    By using ADO you can join data from Excel named ranges, Access, SQL Server, text files, etc. And multiple left outer joins works just fine. However, it's about time to include support for Excel TABLES (ListObjects)! There are 10 year old posts about this!

    QueryTables are really great. By using ODBC drivers you can also create parameterized queries! QueryTables connected to ListObjects, i.e. Excel tables, would however benefit from improved formating. It is about time to start supporting parameters using OLE DB providers as well!

    And please improve the UX. Using MS Query to connect to text files is not the best experience one could imagine. I prefer to use VBA instead.

    Talking about VBA, please make an effort every 15 years or so in order to bring this stuff up to standards. I am not talking about the object model, I am talking about the UX. Anthony Green or some of the other guys on the VB/C#/F# team could probably help you out. If you want to discontinue VBA in the future, please give us something else instead, e.g. VB.NET, C# or Python. Perhaps you could incorporate Excel-DNA into Excel or possibly VSTA, since there now is a 2015 version out. Meanwhile, don't stop updating the UX. MS Query feels like driving a Trabant from Easy Germany post WWII and the VBE is the equivalent of using Gopher pre WWW.

    QueryTables from Power Query unfortunately does not support queries like SELECT * FROM table WHERE column > value (only the equal operator is supported). With Power Query there is however no need for schema.ini anymore.

    Excel used to be a great product, but is not all that great anymore. It is a decent product yes, but with some love from Microsoft it could be an outstanding product. A scripting language is a necessary component and a decent environment for creating scripts as well. Macro recording is a superb feature.

  7. Hey there,

    They do read my blog, yes.

    With regards to the VBA and ADO stuff... I love VBA as well, but it's not going to see much in the way of investment. The reason for this is fairly simple: the language is not portable to the web as it's too free and can do too many dangerous things. The team will still add new properties and methods as they are added to the product (we have new VBA methods and properties for Power Pivot in Excel 2013+ and Power Query in Excel 2016+), but I don't foresee them making too many other changes. The UI - yes it's old and could really use some improvements - but I wouldn't hold my breath for those either. To be clear, VBA will still be supported going forward for the foreseeable future, (XLM is still supported today, even though VBA replaced it over 20 years ago,) it's just probably won't be improved much.

    Re the SELECT * FROM WHERE column > value, I'm lost. We can totally make this happen in Power Query. When you're filtering, look for the "Number Filters", "Text Filters" or "Date Filters" (immediately above the search box. There are options for Equals, Does Not Equal, Greater Than, Greater Than or Equal To, Less Than, Less Than or Equal To and Between in those menus. (Obviously context sensitive.)

  8. Thanks for sharing your insights regarding the future for VBA Ken!

    Re the SELECT * FROM WHERE column > value issue:
    When you use VBA in order to create a table from Power Query code you cannot use "SELECT * FROM [Power Query] WHERE column > value" as command text, but "SELECT * FROM [Power Query] WHERE column = value" works just fine.

    The following article from Gil Raviv explains how to use VBA in order to automate Power Query:
    https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1/view/Discussion

    If you want to have a look at the command text issue, you can download Gil's sample code. Put FALSE in cell D13 and TRUE in cell E13. Then press Create. This will generate an Excel table on a new worksheet. On the Data tab, click Properties, Connection Properties, Definition Page and then alter the command text.

    SELECT * FROM [My first query] WHERE 1 = 1 will work just fine, but
    SELECT * FROM [My first query] WHERE 1 > 0 will not work.

    I hope that MS will add support for more operators in the future.

    Re MS Query:
    It would be great if MS could at least fix bugs. Try to define a format using ODBC Text Setup for example (Data tab, From Other Sources, Microsoft Query, , OK, Microsoft Access Text Driver (*.txt, *.csv), Connect, Options, Define Format...).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.