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)
- 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?”
Hi Ken,
Happy to help.
-winston
i do not use PQ while legacy works fine
I use the legacy get external data features and vba.
I fit that description and would be happy to help out.
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.
Why yes, I *do* use these features, including using VBA to scrape data from HTML tables 🙂
Yes, that's me too, if you need another volunteer.
Thanks all. I've sent him your emails. 🙂
That's me, if you need anyone else.
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
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....
@Dick, @Debra, @Patrick:
Shock! Gasp! {total disbelief} 😀
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!
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.
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.)
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...).