What is PowerQuery?

What is PowerQuery?

An Introduction to PowerQuery

PowerQuery is an Excel tool now known as Get Data and Transform, and is pretty much as advertised; it gets data, puts it into an Editor in Excel, and then transforms it from ‘dirty’ non columnar data into clean columnar data.
So how does it work?

Well we, the users of Excel, can command an Excel workbook to go and find an Excel file (or a folder of files on Windows Explorer, a Sharepoint folder, an Outlook folder, a PDF, an Oracle table, a Salesforce table and quite a few others) and then to extract specific information contained in that file or folder. That request to go and find information, and the information that is returned, is called a ‘query’.
That information is then compiled in our Excel workbook and we can do lots of really useful things with it such as:

And at this point you’d probably be thinking (if you know your way around a spreadsheet), ‘I can do all that stuff in Excel so why would I bother?’

Well PowerQuery can also do this lot:

The Video

Our first video shows a demonstration of how the different screens of Excel look when using PowerQuery as well as the different stages of a transformation.

Resource Listing

So lots of stuff you can’t do very easily in standard Excel, unless you know VBA or are handy with functions.

But all of these capabilities that I’ve put above, you can accomplish in PowerQuery without knowing a scrap of code. We’re talking simple point and click stuff here – find the button you need and then click on it.

But as the song goes…the best is yet to come…

PowerQuery automates all of these transformations!

Essentially, the Excel workbook where you’ve stored the query, remembers all your commands to transform the data from how it looks in the original base dataset, to how you want it to look at the end. Everytime you get that same type of file, you just need to activate the query, by clicking the refresh button, and it will do all the cleaning for you almost instantly!

That means you only need to perform your transformation once.

That leads to big time savings at the initial transformation, but a massive time saving every time you transform that same file in future.