Microsoft has released an add-in for Microsoft Excel called Data Explorer. It is now called Microsoft Power Query. The free add-in has been designed with the sole purpose of reducing your efforts in searching, shaping and preparing data. You might agree, getting your important personal/official data into an analysis-ready format can become a task when using a spreadsheet hence, this plugin, 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 is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration
Microsoft Power Query for Excel
As mentioned, Microsoft Power Query is a free add-in. What’s cool about Microsoft Power Query 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.
Microsoft Power Query 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
- Web
- File
- Database
- 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 Microsoft Power Query Help page has links to various how-to tutorials. you can refer to 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 Microsoft Power Query from here and start exploring its features right away.
