Bottom line: Learn how this awesome feature of Excel and Power BI called Power Query will help you automate the process of importing, transforming, and cleansing your data to save a TON of time with your job.
Skill level: Beginner
Video Tutorial
Download the Sample Files
The CSV files I use in the video are available for download below. You will need to extract the files out of the zip file.
Introduction to Power Query
In this tutorial I provide an introductory explanation of Power Query. You will learn why this is my new favorite Excel tool for working with data, and how it can help automate processes and save you time!
The Power Query Data Machine
I was watching a TV show on how things are made, and they were explaining how a depositor machine worked in a pastry factory.
The basics of a depositor machine are:
You add ingredients to it.
Change some settings.
And it magically creates pastries (cookies, donuts, biscuits) that are ready for baking.
Once the dials are set, the process can be repeated over-and-over again to make perfect pastries every time. Getting hungry…? 🙂
You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
Press buttons in the Power Query Editor window to transform your data.
Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting.
If you have used macros to transform your data, you can think of this as a much easier alternative to VBA that does NOT require coding.
Common Data Tasks Made Easy
Do you work with data that has been exported from a system of record? This could be a general ledger, accounting, ERP, CRM, chúng tôi or any reporting system that contains data.
If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.
These data transformations could include tasks like:
Remove columns, rows, blanks
Convert data types – text, numbers, dates
Split or merge columns
Sort & filter columns
Add calculated columns
Aggregate or summarize data
Find & replace text
Unpivot data to use for pivot tables
Do any of these tasks sound familiar? If so, then they probably also sound boring, repetitive, and time consuming. 🙂 Believe me, I’ve spent the better part of my career doing these tasks and trying to figure out faster ways to get them done.
Fortunately, Power Query has buttons that automate all these tasks!
Overview of the Power Query Ribbon
Starting in Excel 2016 for Windows, Power Query has been fully integrated into Excel. It is now on the Data tab of the Ribbon in the Get & Transform group.
In Excel 2010 and 2013 for Windows, Power Query is a free add-in. Once installed, the Power Query tab will be visible in the Excel Ribbon.
You use the buttons in the Data or Power Query tab to get your source data. Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, chúng tôi Dynamics CRM, Facebook, Wikipedia, websites, and more.
Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data.
The buttons in the Power Query Editor Window allow you to transform your data.
Think about some of those tasks you do repeatedly as you browse the buttons in the image above. Each time you press a button your actions (steps) are recorded, and you can quickly re-apply the steps when you receive new data by refreshing the query.
You can also modify existing queries and refresh your output tables with the changes or updated data.
Data Transformation Examples
Here are a few examples of what Power Query can do with your data.
Unpivot Data for Pivot Tables
My favorite feature of Power Query is it’s ability to Unpivot data. This is a technique used to get your data ready for the source of a pivot table. This is also referred to as normalizing your data to get it in a tabular format.
The data might start out looking something like the following.
And you want the end result to look like this.
Here is an article and video on exactly How to Unpivot Your Data with Power Query.
Checkout my article on how to structure your source data for a pivot table if you are unfamiliar with why your data needs to look like this for a pivot table.
Append (Combine) Tables with Power Query
The Append feature of Power Query allows you to combine multiple tables (stack them vertically) to create one large table. It can do this with multiple tables in one file, or it can pull in data from a bunch of different files/sources.
Let’s say you have a folder that contains CSV or Excel files with report data for each month. Throw all those files in the Power Query machine, and it will spit out one nice table that you can then use to create pivot tables and charts.
If the data in those reports also needs to be transformed (remove rows, split columns, unpivot, etc.), then Power Query can handle that in the same process.
Once it is setup, all you have to do is hit the refresh button every month when a new file is added to the folder and the rows will be added to your output table.
How awesome is that! 🙂
Merge Tables – A VLOOKUP Alternative
Power Query has the ability to merge or join tables. This can be used as an alternative to VLOOKUP or INDEX/MATCH formulas.
Let’s say you have this data table of sales records, and you are using a VLOOKUP to bring in information about the product based on the name of the product sold. Your product group information is located in another table on a different sheet or workbook.
Using VLOOKUP formulas is great, but it can often mean adding thousands of formulas to your workbook. Which increases the file size and calculation time.
Create Custom Functions
However, Power Query can be programmed to create custom functions. This gives you seemingly unlimited potential to transform your data in just about any way possible.
It is based on the M language, and most of the functions are very similar to writing a formula in Excel. This also makes it more user friendly and easier to learn the code.
This new language and set of functions means there is a lot to learn, but I consider that the fun and challenging part. Plus, employers of the future will definitely be looking for employees with Power Query skills.
Power Query Records Your Steps & Automates Processes
Power Query not only makes all these tasks easier, but it also records your steps so you do NOT have to do them over-and-over again. It will save you a lot of time if you are preparing the same data every day, week, or month.
It also does a pretty good job of handling errors. If the structure of your source data changes, Power Query will tell you what step it broke at and allow you to fix it. This makes maintenance easy and you don’t have to completely redo your process when something changes.
You can use Power Query to get your data ready for use in pivot tables, charts, and dashboard reports. This is a critical step in the process of summarizing and analyzing data.
The Power Query Machine & Power BI
Well, it can’t exactly make cookies, but Power Query is a pretty awesome tool! It will save you a ton of time when transforming your data.
Power Query is just one piece of the suite of Power BI (Business Intelligence) products from Microsoft.
If we go back to the analogy of baking cookies in a factory, you can think of Power Query as the first step in the assembly line. Once the cookies are formed, we then need to bake them (Pivot Tables, PowerPivot) and then package them for presentation (Power View, Power Map, Charts, Dashboards, etc.)
How Do I Get Power Query?
The other nice part is that Power Query is now built into Excel starting with Excel 2016 for Windows. If you are on Excel 2010 or 2013 then Power Query is a free add-in.
I have a dedicated page that will help you determine if you have the right version of Excel to get Power Query. It also provides complete installation instructions and the download link.
Complete Guide to Installing Power Query
To give you an idea of the importance of this tool, Power Query was fully integrated into Excel in Excel 2016 for Windows, and is on the Data tab of the Ribbon.
It is also known as Get & Transform, although the term Power Query is most common.
Additional Resources
This article has provided an overview of the basics of Power Query that should help you understand some of the major features. Power Query has a ton of features and there is definitely a lot to learn.
I will be sharing more how-to articles and videos in the coming weeks. Here are a few resources that will help you get started.
How to Unpivot Your Data with Power Query + Video Tutorial
Free Training Webinar on the Power Tools
Right now I’m running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It’s called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
If not, do you think it would be useful for you? Are you doing any of the tasks I mentioned manually right now?
I will be creating more how-to articles and videos on Power Query in the future, so I’m interested to know what you want to learn.
Thank you! 🙂