It can be argued that Power Query (also known as “Get & Transform”) is the single greatest feature in Excel. Power Query can be used to help solve almost every Excel problem you encounter.
What is Power Query in Excel
Power Query is a tool in Excel that helps you save time by making it easy to work with data.
It lets you import, clean, and organize data from different places, like other Excel files, websites, or databases.
You don’t need to be an expert. Power Query guides you through each step, so you can get your data ready for analysis without any hassle.
Here’s how Power Query can make your work easier:
- Get Data from Anywhere: Import data from CSV files, Excel sheets, online databases, or even web pages.
- Clean Up Messy Data: Remove duplicates, fill in missing details, or change text formats. No more manual clean-up!
- Transform Data Your Way: Split columns, change data types, merge tables, and much more. You decide how your data should look.
- Automate Repetitive Tasks: Set up your data steps once, and Power Query will handle it every time you need an update. It saves you from doing the same tasks over and over again.
Whether you’re managing sales reports or tracking project data, Power Query helps you simplify your workflow. You can focus on what matters—analyzing data and making smart decisions.
How to Add Power Query to Excel
Power Query is a built-in tool in most versions of Excel, and it helps you automate, clean, and transform data with ease. Depending on your Excel version, you may already have it installed. Let’s go through the simple steps to find or add Power Query to your Excel.
For Excel 2016, Excel 2019, and Microsoft 365
- Open Excel: Launch Excel on your computer.
- Go to the Data Tab: At the top menu, click on Data.
- Look for Get & Transform Data: Under the Get & Transform Data section, you’ll find all the Power Query options, like From Table/Range and From Text/CSV. You’re all set to start using Power Query!
For Excel 2010 and Excel 2013
In older versions like Excel 2010 and 2013, you need to install Power Query as an add-in. Here’s how:
- Download the Power Query Add-In:
- Visit the official Microsoft Power Query download page and click Download.
- Follow the on-screen instructions to install it.
- Open Excel: Once the installation is complete, launch Excel.
- Enable the Power Query Tab:
- Click File > Options.
- In the Excel Options window, click Add-Ins.
- At the bottom, find Manage and choose COM Add-ins. Click Go.
- Check the box next to Microsoft Power Query for Excel and click OK. Now, you’ll see a Power Query tab on the top menu.
Featured Course
Master Excel Power Query – Beginner to Pro
How to Use Power Query in Excel
Power Query can simplify your data tasks and save you time. Here’s how you can use it to clean and combine data quickly, even if you’re not an Excel expert. Let’s walk through a real example.
Example: Cleaning and Merging Sales Data
Imagine you have a large sales report with over 200,000 rows. You need to create two reports:
- Sales by Group-Customer Name
- Sales by Customer Category
The problem? The Customer Name and Category information aren’t in the main sales file.
- Instead, you have a Customer ID mixed in with other data (like the Invoice Number and Transaction ID).
- Plus, the customer details are stored in a separate text file.
Normally, you might use lookup formulas (like VLOOKUP) to bring over the information, but this can make your Excel file bigger and slower.
Instead, let’s see how Power Query can handle this fast and efficiently.
Step 1: Connect to the Sales Data (Excel File)
- Open Excel and go to the Data tab.
- Click Get Data -> From File -> From Workbook.
- Select your sales report file and click Transform Data. This will open it in the Power Query Editor.
Step 2: Extract Customer ID from Mixed Data
Your Invoice-Customer-TransactionID column has multiple pieces of information.
Let’s use the Power Query Editor in Excel to extract just the Customer ID:
- Select the column that has the mixed data.
- Go to Transform -> Extract -> Text Between Delimiters.
- Enter “space dash space” as the delimiter to pull out the Customer ID.
- Change the result to a whole number so it’s easier to use.
Step 3: Calculate Total Sales
Now that you have clean Customer IDs, let’s calculate the Total Sales:
- Select the Quantity and Unit Price columns.
- Go to Add Column -> Standard -> Multiply.
This will create a new column with the Total Sales for each entry.
Step 4: Connect to the Customer Details (Text File)
To add customer names and categories, connect to the text file that holds this information:
- Go back to the Data tab, click Get Data -> From File -> From Text/CSV.
- Select the text file with the customer details and click Transform Data.
Step 5: Extract Customer Name from Text File
The Customer Name in the text file needs a little cleaning:
- Select the CustomerName column.
- Go to Transform -> Extract -> Text Before Delimiter.
- Use “space open parentheses” as the delimiter to cleanly extract the names.
Step 6: Merge Data from Both Files
Now, let’s combine your sales data and customer details:
- Go to Get Data -> Combine Queries -> Merge.
- Select both data sets (the sales report and customer details file).
- Choose Customer ID as the common column to link them together.
Step 7: Load and Analyze Your Data in Excel
- After merging, click Close & Load to bring the data back into Excel.
- Create a Pivot Table to summarize sales by Group-Customer Name or Customer Category:
- Add fields like Customer Name, Category, and Total Sales.
- Sort the data by sales to see top performers at a glance.
Featured Course
Excel Pivot Tables & Dashboards
Why Use Power Query Instead of Traditional Excel Methods?
Without Power Query, you’d need to manually use lookup formulas and constantly update the data. This increases file size, slows down Excel, and requires more time.
With Power Query, you can automate the entire process. Just click Refresh, and your data updates instantly.
Plus, your final file size will be much smaller. This will make it faster and easier to share.
Ready to Master Power Query? Join Our Course!
Our Excel Power Query Course will teach you how to automate tasks and simplify your workflow. Designed for beginners and pros, this course helps you:
- Automate Repetitive Tasks: Save hours by letting Power Query do the hard work for you.
- Easily Clean and Organize Data: Handle messy data with just a few clicks.
- Combine Data from Multiple Sources: Create powerful reports without complex formulas.
Taught by Excel expert Leila Gharani, the course features hands-on lessons and practical examples you can use right away. Thousands of professionals have transformed their work—now it’s your turn!
What You’ll Get:
- Step-by-Step Guidance: Easy-to-follow lessons, no technical background needed.
- Real-World Applications: Learn the skills that employers value most.
- Lifetime Access: Revisit the course anytime and stay updated.
Join Now and boost your productivity! Enroll today and see how much time you can save.
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
