Getting Started with Microsoft Power Query

I always enjoy the PASS Summits, and most of the time there is always a handful of things that I get excited about. I don’t even wait ‘til I get back to work to try these out,  I usually try it out right after the session or the night after the session.

Lately I have been more involved in data warehousing, ETL and visualization/dashboarding/analytics. I have not had the chance to look more closely at some of the new packages or tools from any vendor for that matter. At this week’s PASS summit, I attended many of the Power BI and Power Query sessions. After I saw the first session on Power Query, I was hooked. There were some data sets that I’ve been trying to scrape with other tools – SSIS, PowerShell etc – that seemed to have been scraped and cleaned much, much easier (or should I say magically) by Power Query. Power Query – you had me at “hello”.

What is Power Query?

Let me quote Microsoft on this definition.

Microsoft Power Query for Excel, is a new add-in that provides a seamless experience for data discovery, data transformation and enrichment for Information Workers, BI professionals and other Excel users

To put it differently, it is the desktop ETL tool you wish you had, which you now do. I am no Excel guru, and I usually don’t look to Excel if I need to do any kind of data dump, cleanup and transform. Before even thinking of Excel, I would probably think of ways to do the cleanup and transform in T-SQL, SSIS, PowerShell, C# (sometimes in this order, sometimes not). After seeing Power Query, my list and order of preference for these tools have just changed.

So what do you need to start your Power Query adventure?

Download and Install

First off, you will need to download the Power Query add-in. You can download it from http://www.microsoft.com/en-us/download/details.aspx?id=39379. There is an Excel 2010 and Excel 2013 version, and there is also a 32 and a 62 bit version. So just make sure you know which Excel version you have. Should you make a mistake, the installer will tell you anyway, so no harm done.

MS Power Query for Excel

Continue reading