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…? 🙂
Power Query works in a very similar way!
- 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.
Power Query is like a machine because once you have your query setup, the process can be repeated with the click of a button (refresh) every time your data changes.
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, Salesforce.com, 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, Salesforce.com, 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.
After completing your steps, you can output the data to a Table in your Excel workbook by clicking the Close & Load button.
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.
Power Query can do this with the click of a few buttons, and prepare your data for use in a pivot table.
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.
Power Query makes it very fast and easy to merge two tables together with just a few clicks. It basically uses SQL joins, so you can even do more advanced merges like inner, outer, left, right, full, and anti joins.
Create Custom Functions
Power Query was designed so that you do NOT have to know how to code to use it. It is very easy to use because you can just click buttons and apply filters like you normally would in Excel.
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.)
You can think of Excel as the factory building that houses all these tools. The exciting part is that there is a very bright future for Excel! All these advances in technology will help us make sense of our data in new ways, save us time, and impress our bosses. 🙂
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.
The webinar is running at multiple days and times. Please click the link below to get registered and save your seat.
Click Here to Register for the Free Webinar
What Do You Think?
Are you using Power Query yet? If so, please leave a comment below letting me know how you use it.
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! 🙂
I’d like to see a video on automating the combination of disparate data into a single data set. For example, in this video you used the “from folder” function to combine multiple files with the same data structure. However, many times I have to pull in different types of data from various sources and then do VLOOKUP’s etc to link these different data sets into usable information. For example, I may have one file with order data, another file with customer information, etc and I want to automate the combination of these different data sets into a single data set. It’s unclear to me how to automate this type of data consolidation task in Power Query. Would I need to manually load the different types of data into multiple worksheet tabs in a single workbook and then use Power Query to automate the process of building the relationships among the different worksheet tabs? It’s the automation of the retrieval and association of this disparate data into a combined data set that I’m particularly interested in learning how to do. Hope that makes sense.
Great question, Bill!
For this process you essentially want to create a relationship between the Orders and Customer tables.
There are many different ways to create this relationship in Excel. As you mentioned, VLOOKUP is one way to do it with formulas.
In Power Query we can use the Merge feature to join the tables and bring the information from the Customer table into the Orders table, or vice-versa.
For this scenario you do NOT need to manually load the different tables into the workbook. They can be from different sources. For example, you could connect to a CSV file or a database for the orders data. Then a different database or Excel file for the customer data. Possibilities are endless.
Once both queries are created, you can then do the Merge in one of the queries. Or create a new query that merges the two tables.
I will add this process to our list for future posts and videos. We also have training on this in our Combine Data with Power Query Course, which is part of our Elevate Excel Training Program, if you want to get started right away.
Another option is to create a join in Power Pivot, then create a pivot table that can display the results from both tables. In this case there is no table that contains the results of the “lookup/join”. That is done in memory and output to the pivot table. I cover that in our Elevate Excel program as well.
There’s always a million ways to solve a problem in Excel, which is what I love about it.
I hope that helps. Thanks again and have a nice day! 🙂
Hi Jon, great video. I followed the process and created a folder with 4 spreadsheets that I would like to combine. They have quite a bit of data in each spreadsheet. Is there a limit? as it has only loaded the 1st spreadsheet from the folder. I know I must missing something, but your help would be appreciated.
Hi Lynne,
Great question! No, there is not a limit on how much data you can load. However, the Power Query Editor only shows a preview of the first thousand rows of data. When you load the data to the Excel Table or Data Model, all of the rows will be loaded.
If you are filtering the data and don’t see all of the items in the column in the filter drop-down list, you can press the Show More button in the bottom right corner of the menu to see all options. This can take a few seconds to load depending on how much data and unique items there are.
I hope that helps. Thanks again and have a nice day! 🙂
Hi Jon, have you ever considered offering CPE credits for your training webinars? This would make your training webinars more attractive to those seeking such credits.
Hi Stephen,
Thanks for the suggestion! We’ll look into this further.
Excellent information, John! The power in Power Query is endless!
Hi Jon,
I have Excel 2016, but my “Get External Data” looks different that what you are showing in the video. I don’t have the icon “Get Data”.
I don’t have “From File”
Instead, I have “From Access”, “From Web”, “From Text”, and “From Other Sources” which does not have “From PDF”.
And I want to choose “From PDF”. =(
What am I missing?
Thanks!
I do really enjoy the video. Great job.It is the most effective & comprehensive training that i have ever been involved.
What do I do when I click the refresh button on the load data, it gives an error message – “The key didn’t match any rows in the table”?
my labtop was using Microsoft Office 365 ProPlus. How come i still do not see the option for me to select “PDF” under Data-> Get Data -> From File
[…] Many identify Excel as a spreadsheet tool that is ideal for numbers, but in fact, it is commonly used to create text grids or lists as well. In the past, users complained about not being able to import table data from Adobe Portable Document Format (PDF) files into their Excel spreadsheets. All that changed on August 5, 2020, when Microsoft announced the launch of a new FROM PDF connector that could be connected to PDF files, enabling data to be transported into Excel spreadsheets easily. In some scenarios, you may wish to import a range of pages from one or more PDF documents. If so, you can simply specify your Start and End pages in the Power Query Editor’s M formula. […]
[…] you're new to Power Query, I recommend checking out this overview, and then using this tutorial to get it installed […]
Hi,
apologies if these are already answered – or too basic !!
1. Is merging two data sets possible using Power Query if the data sets already containing formulas – do they have to be flat files?
2. When merging the two data sets do they have to have the same column headings – if there are additional columns of data contained in only one data set – when the merge is complete will Power Query add these additional columns on the end of the resulting data set.
thank you for your help,
Murray Cowan
Thank you for this helpful introduction. I’ve set up my files but am having difficulties either merging or apprehending a folder that contains four XLS files. I’ve set up the first sheet in each file to have matching headers throughout. However, when I go to Combine & Transform Data, it only loads data for the first file. On this screen I select the one sheet I’d like, but cannot click a “Next” option to select the three other workbooks’ sheets. It only prompts an “Ok” which takes me to the Power Query Editor screen, excluding the three other needed sources. Any advice is appreciative – thanks again!
is power query available in excel 2019 for mac?
In Power Query, if we are using any link and now i wanna make 3 things in list to be variable which will download various kind of data. how can i do that. For example, i have 1000+ products sales report which is dated on a website, i have a list of 55 products and 2 dates for every product. i need to get data of sales for only those 55 product in those 2 defined dates only (dates vary product wise). how can i do this ?? Any suggestion!!
Hello, I’m conecting excel to a mysql database, and I can get all tables except for one that is very big and when it is loading the data into excel, it stops and does not complete the download. What can I do? Any help would be much appreciatte
PD this table get an error through power query and also through Data /Get external Data, I have MySql ODBC 8.0 Ansi driver
Hi Jon,
I’ve been watching your videos on YouTube about excel and power query. You made it easy to follow and learn. Thank you.
I have a shared workbook on OneDrive used by multiple user. Each user need to do some investigation and mark the item resolve when everything is said and done. The users have been saying it is difficult to make updates when someone else is editing and filtering the file. They are on top of each other. I put this file on power query and created 3 queries for each team. The challenge I have is how do I connect the 3 queries to the master file so that each of the team’s update is captured? How do I extract the Resolved items from the master file so that the master file only contains in progress and new cases. Can this be done in power query?
Unfortunately, I can’t give you sample data due to confidentiality concerns. I’m hoping you can provide brilliant insight on what might be the best tool to use for this type of situation.
Thanks Jon.
Hi Jon, yes it is very good. hee… Jon cause I my learning step which quite a bit slow. May I have the step 1 step 2.. how to get the report done. Cause i was thinking if i want to pull report out in a diff folder. I am sure this is a very good and its save a lots of space in my PC.
Can you share with me the step.
Thank you
Peggy
Thank you!!! I’ve spent the past several days trying to figure out how I was going to get my MS Forms continuous data exports from being a manual copy/paste. I was having a hard time finding a help video using my version of Excel and files. I’m honestly so happy right now I think I could cry.
thank you man u are great
Hi Jon, my database has 5,000 records. Power Query brings in a sample of 1,000 records. I do the transformations. Everything is perfect.
How do I get Power Query to do it’s magic on all 5,000 records?
I’m just missing something.
thanks alot of information
Glad to see that Power Query works so nicely for all of you. In my case, it loads data to the querying file, but then does not allow me to save the source data file. Telling me that there is a share violation and that if I want to save, I must save as another file. Even if the querying file has been long deleted! Nobody here has this problem??
I am running Windows 10 Pro, Excel coming from a Microsoft 365 subscription. Everything is up to date. Google searches show that I have to have the folder I am working in indexed, have Use Sharing Windows selected, and not be running a third party anti virus program. I am good with all of that. Can anybody help?
I still not get why I need learn power query when all the listed functions can be done with MS Access/VBA/DAO. Without a standard of data EX between entities, how each part can exchange data?
Hi Jon
I found your article so helpful.
Thanks
I have a Kronos timekeeping report that does not export in a clean format. I use Power Query so that I can just export and refresh. I realize that I’ve only scratched the surface. I can’t wait to dive deeper and use it more!!
where excel cources
and this is nice website
Sadly files are not working!
I’d like to know if it is possible to have excel vba read in the source pdf file, find a row, edit row, and save to excel xlsm file?
Is there any way to select multiple cvs files in Excel 365 on a Mac? The folder option is not available. Is there any other way to do this?
I’m new to Power Query so am probably missing a basic point.
I want to extract the data from a table in each of several files located in different subfolders. The files have a common name characteristic so I can filter on them, but when I load from folder, I can’t do any filtering. I can’t combine files because I have non excel files in there. So I can get a list of filtered files but now I want to extract the data from within. If the files were on their own in a single folder, I can do it. I’m missing the “join”
Thanks
Great overview of Power Query! I love how you broke down its features and functionalities. It definitely makes data cleaning and transformation so much easier in Excel. Looking forward to trying out some of the tips you mentioned!
Great overview of Power Query! I love how you broke down its features and provided practical examples. It really helps demystify the tool for beginners like me. Looking forward to trying out some of these techniques in my own Excel projects!