Well, it depends!

Depends on what? It depends on what you do and how you do it in Excel. 😉

True/False moments

1. Excel is the core software I use for work
2. I use Excel for data analysis
3. I need to deal with data from multiple sources (or from multiple Excel workbooks)
4. I spent hours cleansing/transforming data before I could start analyzing it
5. I do repetitive cleansing/transformation tasks on a regular basis
6. I consolidate data by “copy and paste” data from multiple files
7. I do most of the data work with VBA

If you answered TRUE to more than two questions, then you should really consider setting Power Query as your learning goal in 2022. Believe it or not, you don’t need one year to learn it before you can use the power of it. Although mastering Power Query could be a long journey, most of us are not required to be at the expert level. (I am not an expert of Power Query either.) Just the basics of Power Query are good enough to change your (work) life.

What does Power Query do?

Power Query is easy to learn and apply. We could get the data we need from various sources. Clean and shape the data into the way that we need. Load the result to worksheet directly; or more commonly, load the result to memory for further analysis in Pivot Table. Most actions can be done through the user interface of Power Query Editor. I often joke with my colleagues:

See, I solve your problems with clicks, not even a single formula

And the best part is that all the cleansing / transformation steps are recorded. It is a real lifesaver as we are no longer required to repeat the tedious tasks when a new set of data comes in. A click of Refresh would do the job auto–magically.

In short, Power Query is the tool in both Excel and Power BI that empowers users to deploy a process of

1. getting the data
2. transforming it (i.e. cleansing and reshaping it, as well as combining data from multiple sources)
3. load the result directly to a worksheet or more commonly to memory (connection / data model) for further analysis via Pivot Table

To technical people, this process is commonly known as ETL (Extract, Transform, Load). By the way, I am not a technical person at all. I am just an Excel nerd. 😎

Well, if you are new to Power Query, you may still be in doubt. What are the benefits of using Power Query? How powerful could it be?

Sometimes it’s hard to explain without seeing it…… I have a Playlist called Power Query Challenge on my YouTube channel where you could see how I solved various Excel challenges using Power Query. Feel free to have a look (some videos with Cantonese version).

Honestly, I made these videos on an ad-hoc basis. I encountered a problem. I solved it in Excel using Power Query. And then I shared how I did it on YouTube. If you are newbies to Power Query, it could be really hard to follow. There was never a proper introduction of Power Query on my YouTube channel.

Although I also wrote multiple blogposts related to Power Query, again they were not written in a systematic way for training purposes. Those are blogpost topics which I wrote my mind.

And recently I was surprised by a tweet made by @wynhopkins

I knew Power Query is not popular. Nevertheless, I didn’t expect it to be that less known.

As an Excel nerd who feels and grows from the Power of Power Query, I really want to contribute more to bring Power Query into life for all Excel users. That’s why I am planning to devote more time to making bite-size videos on Power Query in 2022.

The plan is to talk about Power Query from the very beginning, followed by key commands found on the ribbons of Power Query Editor. Real life problems with Power Query solution will be placed to strengthen understanding and application. Bilingual videos will be posted on my YouTube channel and shared in my blog. It will be video driven as it’s better to see it in action rather than reading it, I think. 😉

If you want to learn more about Power Query, please follow my blog and subscribe to my YouTube channel. Stay tuned!