Power Query is Excel’s built-in tool for importing and cleaning data. You connect to a source, tidy the data with a few clicks, and load the result into Excel. Every step is recorded, so next time the data changes, one click on Refresh repeats the whole clean-up.
What is Power Query?
Power Query is an ETL tool, which stands for Extract, Transform and Load:
| Step | What happens | Examples |
|---|---|---|
| Extract | Connect to your data | Excel tables, CSV and text files, a folder of files, web pages, databases, SharePoint |
| Transform | Clean and reshape it | Remove blank rows, trim spaces, split columns, change data types, unpivot, merge tables |
| Load | Put the result in Excel | A table, a PivotTable, or the Data Model for Power Pivot |
It’s the same engine used in Power BI, so everything you learn in Excel carries over.
A simple example: clean messy data
Here’s a typical export: extra spaces, inconsistent capitals, a blank row and amounts stored as text.
In Power Query, the clean-up takes five steps, each just a click:
- Click inside the data and choose Data → From Table/Range. The Power Query Editor opens.
- Home → Remove Rows → Remove Blank Rows.
- Select the Customer and Region columns, then Transform → Format → Trim.
- With them still selected, Transform → Format → Capitalize Each Word.
- Click the ABC icon on the Amount header and choose Whole Number, then Home → Close & Load.
The result is a clean table on a new sheet. Now the useful part: next month, paste the new export over the old source data and click Data → Refresh All. All five steps run again, and the clean table updates. No formulas, no macros, no repeating the work.
Reshape data: unpivot
Reports often arrive with one column per month. PivotTables and charts need the opposite: one row per record. Power Query’s Unpivot Columns turns one into the other:
Select the Region column, then Transform → Unpivot Other Columns. Three columns of months become Month and Sales columns: 9 rows ready for a PivotTable. Doing this by hand means copying and pasting every block.
Where to find Power Query
In Excel 2016 and later, Power Query is in the Get & Transform Data group on the Data tab:
| Command | Use it to |
|---|---|
| Get Data | Connect to any source: files, folders, databases, online services |
| From Text/CSV | Import a CSV or text file |
| From Web | Import a table from a web page |
| From Table/Range | Load data from the current workbook |
| Recent Sources | Reconnect to a source you’ve used before |
| Queries & Connections | See and edit the queries in the workbook |
The Power Query Editor
All the cleaning happens in the Power Query Editor. Each change you make is listed under Applied Steps on the right, and you can click any step to see the data at that point.
For a full tour of every pane and tab, see the Power Query Editor guide.
What you can do with Power Query
| Task | Where | Tutorial |
|---|---|---|
| Combine sheets of a workbook | Get Data → From Workbook | Combine multiple worksheets |
| Merge two tables, like a lookup | Home → Merge Queries | Joins in Power Query |
| Swap rows and columns | Transform → Transpose | Transpose data |
| Add serial numbers | Add Column → Index Column | Add an index column |
| Make a query dynamic | Home → Manage Parameters | Passing a parameter |
| Remove duplicates | Home → Remove Rows | See the editor guide |
| Group and summarise | Home → Group By | See the editor guide |
| Split text into columns | Home → Split Column | See the editor guide |
More tutorials: all blog posts on Power Query.
Power Query, formulas or macros?
| Power Query | Formulas | VBA macros | |
|---|---|---|---|
| Best for | Importing and cleaning data that arrives regularly | Live calculations inside the sheet | Automating anything, including formatting and other apps |
| Coding needed | No | Formula knowledge | Yes |
| Updates | When you click Refresh | Instantly | When you run the macro |
| Large data | Handles millions of rows | Slows down on big ranges | Depends on the code |
| Learn more | Power Query Editor | Excel functions | Excel macros |
They work well together. For example, Power Query cleans the data, and formulas such as SUMIFS or XLOOKUP report on the clean table. In Excel 365, VSTACK and GROUPBY can do some of Power Query’s jobs with a formula.
Which Excel versions have Power Query?
| Version | Power Query |
|---|---|
| Microsoft 365, Excel 2024, 2021, 2019, 2016 (Windows) | Built in, under Data → Get & Transform Data |
| Microsoft 365 for Mac | Built in, with fewer data sources than Windows |
| Excel for the web | Can refresh queries and has a growing set of Power Query features |
| Excel 2010 and 2013 | Separate free add-in, no longer updated by Microsoft |
Frequently asked questions
Is Power Query hard to learn?
No. Most tasks are menu commands, and you can do a great deal without writing any code. Behind the scenes, each step is written in a language called M, which you can edit later if you want to go further.
Does Power Query change my original data?
No. It reads the source and builds a separate output. The original file or table stays exactly as it was.
How do I update a query when the data changes?
Click Data → Refresh All, or press Ctrl + Alt + F5. To refresh automatically, right-click the query in Queries & Connections, choose Properties, and tick Refresh data when opening the file.
Is Power Query the same as Power Pivot?
No. Power Query gets and cleans data. Power Pivot builds data models and relationships between large tables. They’re often used together: Power Query loads the data into the Data Model, and Power Pivot analyses it.
What’s the difference between Power Query and Get & Transform?
Nothing. Get & Transform is the name of Power Query’s group on Excel’s Data tab.
Watch and learn
Video: 12 Methods to Clean Data using Power Query · All Power Query videos