Microsoft, some days ago released an add-in for Microsoft Excel called Data Explorer. The free add-in has been designed with the sole purpose for reducing your efforts in searching, shaping and preparing data. You might agree, getting your important personal/official data into analysis-ready format can become a task when using a spreadsheet hence, Data Explorer for Office Excel to overcome this hurdle. You can do it all, from reshaping to importing, filtering and grouping your data in a new ribbon tab interface.
Microsoft Power Query for Excel
As mentioned, Data Explorer is a free add-in. What’s cool about Data Explorer is it can impressively handle a wide variety of formats such as CSV, HTML tables, Facebook and more. One can combine data from multiple, disparate data sources and then shape it in order to prepare the data for further analysis in tools like Excel and PowerPivot.
Data Explorer ably imports from multiple databases including MySQL, PostgreSQL, Oracle and IBM DB2 as well as Microsoft’s own SQL Server, Azure and Access too. After installing the add-in, you will see the new Data Explorer tab. Let’s start with the basics first!
The tab if you observe, houses various useful options under the ‘Get External Data’ section. Using this, you can either import any data from
- Other sources
My interest lies in importing data from web so, I select the first option.
Simply enter the address of the website in the URl bar and hit Apply.
A query window activates, displaying tables found on the site. You can click one to see what the data looks like.
If required you can re-arrange the data (ascending /descending) order, transform, refresh the data in a single click based on the original data source and do lot more. Simply click on the drop-down arrow and choose the desired action.
Online Search Feature
One of the main highlights of the Data Explorer is its ‘Online Search’ feature. You can enter your query in the online search box and hit enter and get the results relevant to your query, displayed from popular websites like Wikipedia.
Another killer feature of Data Explorer is the ‘Merge’ option. Using this, one can merge or append from multiple sources. Also, you can build stunning visualization using Power View in Excel 2013.
The main Data Explorer Help page has links to various how-to tutorials. you can refer them for your better understanding: It ranges from simple to more complex tutorial that goes step by step through importing data from multiple sources, joining tables on common columns and grouping, sorting and filtering results.
You can download it from here and start exploring its features right away. It is now called Microsoft Power Query for Excel.