What is PowerQuery?

Private: PowerQuery for Finance Teams 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:

Take out unnecessary information such as unwanted columns or unwanted rows
Replace values with other values
Convert data easily into date, date time, number currency and other formats
Split coumns at standard delimiters
Group information by characteristics
Add custom columns of data
Add conditional columns
Transpose rows and columns

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:

Connect to folders (Windows folders, Outlook folders and Sharepoint folders) of files of the same information and combine them instantly and perform the same transformations on all files.
Perform complex nested conditional formulas with no limit on the amount of permitted nests
Split columns of data into rows
Split columns of data based on the number of characters
Split columns of data based on line feeds
Identify duration between dates and times and convert into specific time periods.
Extract information based on delimiters
Extract date information without the need for functions
Fill blank cells based on the last non blank value in the cells above or below
Merge queries with information from other tables (no more VLOOKUPS)
Append queries one under the other (no more copy and pasting reports one under the other!)
One click extraction of month name, weekday name, day of the month from dates

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.

00:30 - What are dirty or clean data
02:35 - Stage 1: Connect Power Query to the datasource
02:52 - Stage 2 - Perform your dataset transformations
03:38 - Stage 3 - Close the Power Query Editor and Load your data
04:00 - Stage 4 - Analyse your clean dataset
04:18 - Stage 5 - Adding more data

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.

Back to Course

Next Lesson