This overview aims to provide a basic understanding for Excel users who haven’t yet got to grips with Power Query, to get you up and running with it.
If you are already accustomed to doing whizzy things in Excel with macros and VBA, or you have experience with Graphical User Interface (GUI) query tools like MS Access and Business Objects then Power Query is the logical next step. It is an ETL (Extract, Transform, Load) tool which provides an easier and more user-friendly method of doing the key querying tasks:
- Extracting the raw data
- Transforming it into a usable format
- Summarising the data and reporting the findings
- Automating and replicating the above steps
You can install the Power Query add-in if you use Excel 2010 or 2013. Or if you’re using 2016 it is now built into Excel, in the Data group on the ribbon with the less jazzy name of ‘Get & Transform’.
You can import into Power Query from every data source imaginable, whether that’s a SQL Server database, social media data, or simply from an Excel table in a worksheet. In this example we just use an Excel table with Premier League match data from this season so far (courtesy of www.football-data.co.uk – thanks). Note: When using Excel worksheet data for Power Query the dataset must first be formatted as an Excel table.
In Excel 2016, choose ‘From Table’ from the Get & Transform menu group and you will be taken into the Query Editor screen.
Similarly to native Excel, there is a ribbon along the top with numerous options and your data is shown in a tabular format.
A simple change you may make is the format of the Date field. You can click to highlight the column and then drop-down the Data Type to change it.
Another regular thing you’ll want to do when transforming your data is remove some of the columns, in this case you don’t want to keep the Referee or the foul stats in your dataset so you highlight them and click to Remove Columns. Alternatively you could highlight the columns you want to keep and choose to Remove Other Columns.
As you go through the process of transforming your data, Power Query remembers each step similarly to how Excel works when you record a macro.
Power Query will lump steps together and cut out any slack to transform the whole query into one statement which you can edit using the programming language known as ‘M’. You don’t need to learn M, as Power Query writes the procedure for you as you build it, however some knowledge of the language will broaden your horizons and open up more possibilities for the really advanced stuff. You can click on any of the steps in the Query Settings panel and the data will populate as of that particular step. This makes it very easy for you to visually debug errors, go into the step which is causing the error and correct it.
The functionality available in Power Query gets much, much more sophisticated than these simple examples. Unpivot data is a particularly powerful function and there are many more which we’ll explore in future posts.
Power Query gives you two choices for where to output your results, either into an Excel table on your worksheet or into the Excel Data Model, which has no limit on the amount of data it can handle. Or you can choose neither if you just want the query to be used as source data for another query later on.
Once you’ve built your queries they are reusable, can be refreshed at any time and can be linked to other queries in your data model.