How do you learn more Power Query? This is a post addressed to people who have already learned initial navigation and have played around with a few of the buttons. Perhaps you attended one of my Introduction to Power Query courses.
When I decided to learn more Power Query, I chose to work through a text book on “M”, which is the code that Power Query uses. In my case, I used “M is for (Data) Monkey” but there are others, including a newer edition by the same authors.
However, this is not an approach I would recommend. Don’t get me wrong, the textbook is great. Lots of examples, clearly explained. But trying to learn it all at once is not a good way to ensure long term retention of the knowledge.
It’s like when you’re at school and you cram Spanish vocabulary the night before the exam. It’s ok for a short-term boost, but when you need to have a conversation six months later you can’t remember any of it.
However, there didn’t use to be many options to learn more Power Query. There was a real jump between beginner resources which looked at the user interface and then you were straight into learning the code.
And if (like me) you got into Power Query because of its ease of use, this is a bit of a shock.
More ways to learn *more* Power Query
I think the answer to this might be video shorts focussed on particular problems. There is now quite a bit of material out there which starts with the user interface and then adds a bit of code to solve a practical problem.
For example, there’s this video featuring Chandeep Chhabra and Oz du Soleil, exploring use of Lists in Power Query. I really like how this gives practical examples for where a little bit of code really opens up Power Query.
Also on Goodly, I enjoyed this Power Query tricks battle with Mark Proctor.
Mark Proctor generally has lots of great stuff – for example this 15 minute video goes through ten Power Query tips which include some code examples.
Finally, if you want a longer example of how you can go from Power Query user interface to incorporating bits of M code, these two examples from Malina Cierzniewska Skweres are excellent.
Do I need to learn more Power Query?
I probably do 95% of what I need without doing any coding. I’m not a data analyst though. I tend to work with small organisations and the data I have to deal with is not usually that complex.
For me, the beauty of Power Query is it offers automation solutions to people without needing to code (much). It’s in reach of small finance teams (or lone operators) without specialised IT resource or a coding background.
However, there are certain things that come up again and again where you do need code to sort. The main issues for me are where you have columns that might change their name. Or where you might need to add more columns in future.
The good news is that it’s easier and easier to find solutions to help with this.
If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.