 "Pin It")
This year I got serious about learning Power Query! Also known as Get & Transform. Read my post to follow my journey.
What is Power Query?
Previously Power Query was an optional add-in. In Excel 2016 it’s a built in tool! What does it do?
- Connects to data (connects multiple files)
- Shapes the data to make it easier to analyze (normalizes it!)
- Can replace heavy formulas and vba code
Once your data is in the perfect shape you can export it to an Excel sheet.
How have I Learned?
I believe it’s essential to get theory & practice in a continuous loop. So, how have I learned this year?
- made a commitment to myself to learn
- looked for free resources to get started
- built stuff!
- enrolled in Power Query Academy
- built more stuff!
- made time to study Power Query Academy
- online participation (more building!)
Free Resources
Getting started in Power Query is easy with so many free resources:
- I started watching YouTube videos (Mike Girvin, Oz du Soleil, etc)
- Getting started guide from Microsoft
- Power Query M function reference
Built Stuff!
I started reshaping and normalizing data in Power Query. It was difficult at first as the landscape was foreign but it’s getting easier. Some of my solutions are awkward but I keep learning.
I solved this messy challenge with formulas. Then I thought “Can I solve this in Power Query?“.
I combined formulas and Power Query to solve it. Next, with tips from others I solved it only using Power Query! Bill Szysz complicated my challenge and shared a solution. I learned more! Thanks Bill!
How Can I…?
When I get stuck in Power Query my questions are essential to my learning process!
- what’s the equivalent of the Mid function in M language?
- how can I append queries? (combine data with headers)
- how do I change ‘Load to’ options for a query?
- how can I search within a cell?
Power Query Academy
I decided to get serious about learning. I enrolled in Power Query Academy. At this point I’m 42% finished and I’ve learned so much! You’ll have to dedicate many hours to the course but this is the step in your journey that will take you to the highest level!
Update: Feb 2019 I’m now 82% finished and still learning!
Online Participation
Ken Pul’s has had 2 Power Query challenges so far. I’ve participated in both. He shares the different approaches so everyone learns!
Challenge Yourself
I saw a Starbucks drink menu in PDF format. Could I normalize it using Power Query? Besides my curiosity and determination some Starbucks’s coffee was helpful 🙂
I downloaded this Beverage Nutritional Info PDF from www.starbucks.ca/menu/nutrition-info
It definitely wasn’t easy but I eventually figured out how to do it and learned a lot during the process.
“R” is for resilient.
Power Query thinking!
When I immerse myself in the Power Query Academy course AND also build things in Power Query I’ve started to be able to “Think in Power Query“. It’s taken time and it’s only just beginning but it’s a major breakthrough! My journey is definitely off the ground at this point but I’ve got to keep going!
About Me
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.
You can watch my Excel videos, follow me on Twitter and read my blog.
Many have asked me about Excel training. Here are some courses I recommend.
Hi Kevin,
I came across your blog and I would like to let you know that I really admire you. Recently, I learned a bit about Power Query and it helped me to get things done quick and I fell in love with it right away. However, I’m not a data analyst nor a smart person that understand coding. I thought about the Power Query Academy but afraid that my Excel Level is not there and it would waste my money. So, my question is how do you like the Power Academy? If you have questions, who do you ask? Or, you just figure it out yourself?
Well, thanks for sharing all your learning experience.
Hi Yoke,
First of all thanks for visiting my blog! I’m glad you’ve found it helpful. I try to cover a variety of topics.
As a long term Excel user Power Query really is amazing! Many things that used to take hours now take minutes!
Power Query Academy is amazing but it does require serious study. I would suggest signing up for the free trial. There is also a support forum for the paid course.
There are other courses (some free) that aren’t as comprehensive if you want to learn the basics but don’t have time to really dive in.
Keep Excel’in Yoke!
Cheers,
Kevin
Hi Kev,
I found your blog accidentally. I am in my second year of subscription with Power Query Academy and I’m a happy customer. Ken is fantastic and his friend too. I’m an Excel instructor and I also suggest finding videos as a started then practice and progress toward more challenging stuff. Then go with a subscription if they choose. I made a quick demo of PowerPivot in Excel recently (using the data model) and they were pretty impressed that Excel can behave like a database!
Thanks for your blog.