PowerQuery is an amazing addition to Excel.
It can transform entire columns of data in a couple of clicks. It can unpivot monthly trackers, instantly normalising the data. You can use it to stitch multiple csv/txt files together just by pointing it at a folder. And it can read, import and transform any sheets or tables in any Excel file, without opening it.
It has a lot of other abilities, but simplifying cleansing and transformation of data is the main one that I am going to focus on here.
A few weeks ago, I was trying to put hundreds of records of daily data, then using that to split other data. It was a bit of a mess, and having only just begun using Power Query, I had trouble with it.
The first problem I had that brought my Excel to a standstill was trying to do everything in one file. Even though Power Query can do a lot, couple that with a workbook full of calculations and you’re in for some slow refreshes. I am talking sitting there for minutes at a time for small changes in the query.
Tip Number 1: Do your query transformations in a separate file
Cleansing and transforming data can be quite intensive on a workbook. Even more so when that workbook is trying to calculate things itself. Couple that with Power Query reading from internal tables and it can all fall over.
Since Power Query can pull from external workbooks, I’ve had great success with doing all my transformations in one file and then just pulling the results into another.
Tip Number 2: Use 64-bit Excel
Not everyone will have this option and some may have add-ins that aren’t compatible with 64-bit Excel. However, I’ve seen even seemingly small workbooks and queries sucking up 2.5gb of memory since switching to 64-bit. Excel 32-bit is limited to 1gb, so better to go with 64 if that is an option.
Tip Number 3: Put The Link To Your Source In A Named Cell And Use That In Your Queries
This one isn’t as intuitive as the others, I can’t even remember how I came to be looking how to do this! Basically, every query has a source, and the source can be a table or range in the workbook, or an external source.
When it’s an external source, you can view the source in the Power Query editor, but what about when the source changes?
Initially, I was editing these manually. And then I ended up with multiple queries using one source. While my first solution was to have a separate query just for the source and then reference it a bunch of times, I found a better way.
Editing a cell is far easier than editing the source in the Power Query editor. If you name the cell, you can pull that into Power Query as a data source and use that in your other queries. This means that to change the source, you just edit the cell and refresh. Just like magic!
Tip Number 4: Don’t Use Power Query For Everything
I wrote a post about this before, but it’s worth mentioning again.
As great as Power Query is, it doesn’t do everything well. For example, I like to use the ISOWEEKNUM formula in Excel tables, but Power Query doesn’t have a function like that. I would have to build a custom function to replace it in PQ.
It’s far simpler to carry on doing the things that Excel formulas do really well within the spreadsheets and then just pull the results into PQ from there. I have several files where I load a query to a table, add some custom columns, then load the table back up into a new query that I can refer to in other files.
It’s a bit convoluted, but it will also help you get comfortable with Power Query.
That’s it for now!
I’m still at the beginning of my Power Query journey, so I’m bound to learn more things to share. As an analyst, data cleansing and transformation feature heavily in my work. Power Query is a great way to lessen the load and get to your final analysis, faster.