Why you need Power Query even if you don’t use Big Data

I’m ashamed to admit I’m one of those who initially thought of Power Query as being a toy for hardcore financial modelling, or manipulating enormous data sets within multinational companies.

This may be because these are often the sort of examples provided. It’s very easy as a busy finance professional in a small or medium finance team to assume based on this that there are better things to spend your time on.

It was only after my second attempt at learning Power Query that I truly grasped how revolutionary it could be for all finance professionals and teams.  If you use Excel to do anything at all, the chances are Power Query will allow you to do it better. In this article I’ve set out four areas where knowledge of a bit of Power Query could transform your professional life.

Do you ever use lookups (eg VLOOKUP, HLOOKUP) or other formulas to compare data sets?

Often one of the first “advanced” formulas we accountants learn, and didn’t we just love it when we did? By using a Vlookup we can assign headings from a chart of account document to a trial balance we’ve imported from our accounting system and it makes us feel like gods, or at least like proper advanced excel users.

But lookups are not all that great. They don’t handle duplicate or missing lookup keys very well, and they take up a lot of processing power, so workbooks with lots of VLOOKUPs tend to get big very quickly. And perhaps we graduate to using INDEX/MATCH or other data comparison formulas – but they’re not necessarily the easiest to use.

Power Query gives you the simplicity of lookups with significantly more functionality and a lot less processing power. Using the Merge Query function you can take as many separate tables (for example, a trial balance feed from your accounting system, a chart of accounts spreadsheet, a list of current budget holders assigned to cost centres) and merge these all together to produce a nice data table that you can then pivot to your heart’s content. And all this for a fraction of the size of your legacy lookup workbooks.

And it being Power Query, you can do it again, and again.

Do you ever have to tidy up data you’ve received from somewhere else?

If the answer to this is no, then please contact me to tell me what the secret is.

Seriously, we all have to do this. At various points in your life you’ve probably spent more time tidying up data than actually analysing it. By “tidying up” I’m referring to a range of tasks which might be putting dates or names into a consistent format, hiding or removing columns you don’t want, extracting parts of columns or amalgamating bits of information from several columns.

there’s a button for that

One of the things I love about Power Query is that there is often just a button to do this thing for you and it’s pretty good at guessing exactly what you want to do. As an example,a common task might be to take data like this and split it into colums showing first name / last name

Customer ID	Name
1	Amy Beech
2	Ethel Smyth
3	Grazyna Bacewicz

With formulas in Excel, you can do this, but it’s fiddly (at least prior to 2013*) – you need create a formula (or nick one off the internet) to identify the space, count the number of characters from the end to the space, extract that number of letters …

Alternatively you could use the “text to columns” function, but you have to redo this every time you add more data. And it can struggle, eg if there are names with two spaces in.

With power query, there are several options. There is a “split column” option which will automatically detect delineators such as spaces, hyphens and dots, or you can manually set it. So usually in a couple of clicks, your data is transformed. There is also a “custom column” option where you can create a new column, type “Amy” in the first row, and it will then identify that you’re trying to find everything before the space and do the rest for you.

There are dozens of similar examples, where Power Query has introduced a button that replaces the need for complex formulas. It also has lots of built in tools for manipulating data that would have required cutting, pasting and other more traumatic techniques. For example, the wonderful “Unpivot” which transforms data like this….

Budget line	Apr	May	Jun
Widgets	124	197	144
Oojamaflips	83	73	94
Thingummies	109	76	148

To this, with just one button click:

Budget line	Month	Amount
Widgets	Apr	124
Widgets	May	197
Widgets	Jun	144
Oojamaflips	Apr	83

your original data is untouched

Perhaps most importantly though – with Power Query, you are not actually at any point editing the original data. And what you do can easily be reversed and/or repeated. It is also fully auditable, which might be important if you want to demonstrate that you haven’t deleted any important data. Power Query sets up each transformation you do as a navigable “step” so you can cycle back several transformations and check what you did, or try a different approach.

*2013 and above versions of Excel have Flashfill which is amazing at this sort of thing. I will do another post on this at some point as it is a fantastic tool, however, Flashfill is not auditable so for this reason I tend to prefer Power Query.

Do you ever have to repeat the same process?

Batch journals, month end processes, tidying up the same bloody spreadsheet each time someone else gets hold of it. It’s a good job we finance professionals love repetition and routine. But if you ever find yourself doing the same processing task on a regular basis and it is taking you more than a few seconds you need to think about how you can do it differently. (On a related subject, I love this cartoon from XKCD, and I have a copy glued to the inside of my notebook. https://xkcd.com/1205/)

Anyway Power Query is a wonderful tool for doing this, because of its repeatability. Firstly, clean up your data using one of the techniques mentioned in section 2. Then, you can simply point your query at next month’s spreadsheet and run exactly the same transformations. Even if something has changed in the structure of the source data, it is usually fairly easy to step back through the query you wrote and identify what needs to change

Do you ever have to amalgamate similar data from multiple sources?

I’m sure you will have had to do something like this. For example, bringing together individual budget submissions to produce an organisational budget, or inputting timesheets into a CSV file for upload into payroll.

This is bread and butter stuff for Power Query, because of the repeatability point above. If your data is all in the same format, you can point Power Query at a folder or series of folders and it will pull in and amalgamate all the data in there.

This functionality has enormous potential to speed up a lot of typical processing tasks – for example, processing manual expenses claims.  It also can make it a lot easier to carry out reforecasting exercises because you can just use the same original budget templates. Just point Power Query at an updated copy and there you go.

Want to know more?

I provide Power Query training for finance teams – see here for more details.