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 to reduce your efforts in searching, shaping, and preparing data. You might agree that getting your important personal/official data into an analysis-ready format can become a task when using a spreadsheet; hence, this plugin overcomes this hurdle. You can do everything 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 it is that 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 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. After installing the add-in, you will see the new Data Explorer tab. Let’s start with the basics first!
If you look closely, the tab 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.
The main Microsoft Power Query Help page has links to various how-to tutorials. You can refer to them for better understanding. The tutorials range from simple to more complex and go 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 immediately.
Read: Calculate the number of Leap Years between two dates in Excel.
