Last week there was an update rolled out to my copy of Microsoft Office. Usually this wouldn’t merit much discussion, but this update included improvements to a tool I’ve been using extensively for the past few months; Power Query.
What improvement was that? It was the super useful Intellisense feature, as well as syntax highlighting.
Basically, Intellisense is like auto-complete. You start typing and it guesses what function you want and gives guidance on how to complete it. It’s slightly helpful in Excel, but you still need to know what function you want.
In Power Query it is so much better than that.
In Power Query, you don’t need to know a function’s name to start using it. If you want to manipulate some text, start typing text and a whole bunch of options shows up. You can pick something from the list or keep typing to narrow it down.
I’ve been getting more comfortable with Power Query’s language, M, but this has made it much easier to use and get used to.
Syntax highlighting is something programmers have appreciated in their development tools for years. Basically, it highlights text in different colours when it has a special meaning. It makes reading through code much easier, and therefore debugging or rewriting code is a lot faster. It also makes it much easier to spot mistakes, as when you think you have used a function but have mistyped it, the lack of colour change will help you notice.
This doesn’t sound like fancy new skills
I was getting to that.
I’ve been at my new job for around 6 months now, and when I began I had just started using tables for everything in Excel.
There was a learning curve there, but it’s paid off splendidly since.
Also, I was not using Power Query. At all.
I know this because today I reopened an Excel file I created in March to re-use it. I was a little taken aback at how lacking it felt in comparison to the way I work today!
It was full of static data tables, with no references to the source files. There were sheets with data in that didn’t seem to go anywhere. The formatting was inconsistent. It really didn’t feel like my work.
But it was, and at the time I was pretty happy with it.
This is the difference that using Power Query makes.
Now in my analyses, I can see where all the data is stored, why it’s there and what it’s being used for. Everything unneccesary is left out or removed. Everything important is labelled and commented if the source isn’t in a data folder.
So even though I’ve been banging on about it for months, using Power Query really is a fancy new skill. I’m looking forward to seeing how much better I get in another 6 months!