Here are the five most useful power query functions for accountants, in my personal opinion:
- Conditional Columns
- Merge Queries
- Data from Folder
It is, of course, impossible to pick just five. However, the point of this post is to illustrate that the power query functions I use the most are actually the simple ones. They are still enormous timesavers. Let’s have a look.
You use this to extract text from a column, so this is really useful for data cleansing. Let’s take this example, and say you want to extract the surname from the first column.
In standard Excel, this is fiddly – the text lengths are different so using formulas like LEFT/MIDDLE/RIGHT won’t work on their own. You can get there with a series of formulas but it’s awkward. You can solve this one with the Split Column function in Excel or with Flash Fill, but in both these cases you have to redo these if the data changes, and there isn’t an audit trail in the same way as if you used a formula.
In Power Query, you get loads of options for chopping up text:
And within each option, there are further choices, with the result that if there is a logic to the data, you will be able to extract in a single step:
Conditional columns (instead of If)
The conditional columns dialogue in Power Query is one of my all-time favourite examples of how Power Query just makes existing Excel use much easier. You’ll never need a nested If again.
In this example, I want to map student grades to categories. A = Excellent, B = Good, C = Pass, everything else = Fail.
Look how much easier that is than a nestedIF.
The user interface doesn’t allow multiple conditions, however, you can get round this by either editing the code (here’s a blog post about that) or by setting up helper columns. If you can handle the logic in Excel, you’ll find it a doddle in Power Query.
Merge query (Vlookup and identifying things that don’t match)
The flaws of VLOOKUP are well-documented. XLOOKUP solves most of these issues, but Merge Query in Power Query allows you to do even more.
Merge Query is actually more akin to a database join. First you identify your data tables and the common column or key, and then you bring the data together in a particular way depending on the join type.
The dialogue window is clear, with the key columns highlighted, and there are two things I particularly like:
- it defaults to the equivalent of the VLOOKUP result, i.e. you want to keep all the rows in the first table and then bring through everything that matches from the second;
- there is a preview at the bottom of the number of matching records which helps you see whether you’re doing the right thing or not.
But, THERE’S MORE! Other types of join are possible. The Anti-join brings back rows that don’t match. The inner join brings back only rows that match in both tables. You can use these (or a combination thereof) to compare two sets of data and just extract the data that doesn’t match. It’s the future of reconciling.
Data from Folder
Is this actually a function? Who knows, but I use it all the time. Basically, if you have data in the same format but different sources, and you need to combine it, you can do this by setting up a query from Folder instead of from a file. Here is a link to a worked example of this in the context of combining budget templates.
I’ve picked this one because it’s something you just can’t do in Excel. It’s quite hard to describe in plain English but basically it means turning something in this format:
Into this format:
In selecting the most useful power query functions to highlight, I have considered what I actually use on a day to day basis. Power Query is a gamechanger because it allows you to do transformations that would be tricky or impossible in Excel with a set of simple dialogue windows.