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!)
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
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!
Ken Pul’s has had 2 Power Query challenges so far. I’ve participated in both. He shares the different approaches so everyone learns!
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!
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.