The party would already be in full swing; I was going to be late. Not fashionably late, just late.
When I finally arrived, rather than the usual “What time do you call this?“, I received the “Hey; you made it, that’s amazing. It’s great you could come. Let me show you around“. Nobody looked down at me for being late. They were just pleased that I was there and excited to tell me about everything that was going to happen.
Sounds like a nice party, right? You’re starting to wish you were there too, aren’t you? The good news is… you’ve been invited. Actually, you’ve just arrived.
The party I’m referring to is the group of people who already know and love Power Query (or Get & Transform as it’s known in Excel 2016 and later). I can’t remember the first time I heard about Power Query, but I do remember it was in the same breath as Power Pivot. Then I heard it was an additional add-in I had to install. My first thought was “Sounds like too much effort; it’ll probably be rubbish anyway. I don’t really need it. I’ve got VBA; I can do everything I need in VBA. It’ll just be a waste of time“. The irony being that had I looked into PowerQuery at that moment it would have saved me so much time.
Time passed. I noticed that more and more Excel experts were talking about it. Ken Puls, Mynda Treacy, Oz du Soliel were all talking about how amazing Power Query is. I dug in my heels. I thought to myself “I don’t work in a world of dirty data, I don’t spend hours cleaning and fixing data problems. Plus ‘data transformation’ sounds like something I would need a computer science degree for“.
Excel 2016 was released and Power Query was integrated directly into main Excel application (though it was now known by the name of Get and Transform). I had no excuse, there was no download or installation required. I opened up the Power Query interface. My instant reaction was “Too hard, don’t understand it, see it was a waste of time, I knew it would be.”
Twelve months pass, I happened to see some free webinars and YouTube videos, no harm in checking them out – BOOM!!!! (That was the sound of my mind being blown). My brain starts thinking again; it’s the first rational Power Query thought I’ve had: “PowerQuery isn’t just for dirty data, it’s for all data! Data transformation can be as simple as transposing a table. This could save me hours of time and a lot of annoying VBA code. Why had nobody told me about this before … oh wait … they did!”
This is how I finally arrived at the Power Query party. Since then, everybody has been willing to share all their knowledge about Power Query. I immersed myself in Power Query and Power Pivot and now I rely on these tools to save time every day.
Unfortunately, if you ask most Excel users if they use Power Query they will look at you blankly, they have no idea what it is. I want to take my part in changing that, so over the next few posts we will be focusing on using Power Query.
If you’re reading this, then chances are you’ve just arrived at the Power Query party too. All I can say to you is: “Hey, you made it, that’s amazing. It’s great you could come. Let me show you around”. 🙂
What does Power Query do?
Power Query is an ETL tool (“a what?” I hear you say). ETL stands for Extract, Transform and Load. Let us look at each of those words individually to get a better understanding.
- Extract – Data can be extracted from a variety of sources; databases, CSV files, text files, Excel workbooks, specific cells on the same worksheet, websites and even some PDFs. Basically, if there is data stored somewhere in a structured or semi-structured format, Power Query can get to it and pull it out.
- Transform – Once the data has been extracted in the previous step, it can be cleaned up (i.e., remove spaces, split columns, change date formats, fill blanks, find and replace etc) and reshaped (i.e., unpivot, remove columns etc). When data is extracted from different sources it is unlikely to be consistent, the transform process is used to make it ready for use.
- Load – Once the data has been extracted and transformed, it needs to be put somewhere so that you can use it. From an Excel perspective, it can be pushed into a worksheet, a data model, or another query.
To summarize, Power Query takes data from different sources and turns it into something which can be used.
As a tool, this is pretty useful already. But here is the best part. Once the ETL process has been created, it can be run over and over again with a single click. Which can save hours of work every week.
Some examples of what Power Query can do
Does it all sound a bit abstract and confusing so far? Let me share some examples to give you a real taste of what is possible.
Let’s say that every day you receive a CSV file with an updated price list. Normally you would open the CSV, copy the relevant columns into your spreadsheet then use a lot of LEFT and RIGHT formulas to split some text fields, finally, you use a PivotTable to present the information in the right format.
You could do all of this with Power Query. Simply save the CSV file in the correct location, then update everything with a single click.
Or how about this scenario, each month you receive 30 Excel workbooks from different divisions of the business, all the workbooks are in the same format. You open each spreadsheet and copy the data into a master spreadsheet to consolidate into a master report.
With Power Query you could consolidate all the data at once with a single refresh. You don’t need to open up each worksheet, you don’t need to copy and paste. Just save all the files in the same folder, then update the consolidation with one click.
Your business has just acquired a new company. As is often the case, their software system is different to the rest of the company. Management currently has no intention of changing IT systems. Your job is to create a summarized sales dashboard for the whole company each week. Sounds like it could be a lot of work.
With Power Query you may be able to get data directly from each system and with the use of a mapping table, update the dashboard with a single refresh.
What is the difference between Power Query and Get & Transform
The average user doesn’t use the word “Query” particularly often to describe the tasks they undertake. Put the word “Power” in front and nothing is clearer. Maybe this is why Microsoft changed the name to Get & Transform in Excel 2016 and placed it in the Data ribbon. I am sure they were trying to give the user more clarity. I’m sure if you asked most people to guess what the tool did, just based on the name, many will get some of the right answer. While it may officially be called Get and Transform in Excel 2016 and later, the name Power Query appears to have stuck with the Excel community.
How difficult is Power Query to learn?
If you are you thinking that you need to be a programmer? Or at least an Excel expert? You would be wrong. Power Query has an easy to use interface which is designed for everyday users.
There is a bit of a learning curve, but most of that is learning what each button does.
If you want to go deep into Power Query, there is a programming language called “M” which you could learn. But you can harness 99.9% of the power without needing it at all.
How to install Power Query?
Due to the development of Power Query in the Excel application in recent years means there are several key questions:
- Do you need to download Power Query?
- Which version of Power Query should you download?
- Is your version of Excel compatible with Power Query?
Thankfully, Jon Acampora has a very detailed post which will give you all the information you need to install Power Query.
Time to get started
In a survey of Power Query users, it revealed an average time-saving of 22%! That’s huge! More than one workday per week of annoying repetitive work removed for each user of Power Query.
With that statistic in mind, let’s get started with learning Power Query so that you can start saving time yourself.
Power Query Series Contents
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: