The Power Query Editor is the window where you clean and reshape data before it goes into Excel. You remove columns, split text, fix data types and combine tables there. Every step is recorded, so the same clean-up runs again with one click when the data changes.
Table of Contents
How to open the Power Query Editor
There are three ways in, depending on whether you’re starting fresh, bringing in new data, or changing a query you already have.
- Open it empty. Go to Data → Get Data → Launch Power Query Editor, or press Alt + F12.
- Load data into it. In the Get & Transform Data group on the Data tab, choose a source such as From Table/Range, From Text/CSV or From Web. The editor opens with that data loaded.
- Edit an existing query. Go to Data → Queries & Connections and double-click the query in the pane on the right.
The Power Query Editor window
The editor has seven parts, numbered in the screenshot below. Once you know what each one does, every Power Query tutorial becomes easier to follow.
| # | Part | What it does |
|---|---|---|
| 1 | Ribbon | All the commands, organised into the File, Home, Transform, Add Column and View tabs. |
| 2 | Queries pane | Lists every query in the workbook. Right-click a query to rename, duplicate or delete it. |
| 3 | Applied Steps | Every change you make, in order. Click a step to see the data at that point, or rename, move and delete steps. |
| 4 | Preview grid | A preview of your data at the selected step. Right-click a column header for the most useful commands. |
| 5 | Formula bar | Shows the M code behind the selected step. You can edit it directly. |
| 6 | Details preview | Shows the full contents of the cell you click, which helps with long text and nested tables. |
| 7 | Status bar | Shows how many columns and rows are loaded, and when the preview was last downloaded. |
You can’t type into the preview grid. It looks like a worksheet, but every change has to be made with a command, so Power Query can record it as a step and repeat it next time.
The ribbon tabs at a glance
| Tab | Use it for | Most-used commands |
|---|---|---|
| File | Saving and loading your work | Close & Load, Close & Load To, Discard & Close |
| Home | The everyday essentials | Remove Columns, Remove Rows, Split Column, Data Type, Merge and Append Queries |
| Transform | Changing the existing columns | Unpivot, Fill Down, Format, Extract, Group By |
| Add Column | Creating new columns | Custom Column, Conditional Column, Column From Examples, Index Column |
| View | Showing panes and checking data quality | Formula Bar, Column Quality, Advanced Editor, Query Dependencies |
Some commands appear on more than one tab. The most important case is the Transform and Add Column tabs, explained below.
File tab
The File tab is where your work leaves the editor.
| Command | What it does |
|---|---|
| Close & Load | Saves the query and loads the result into a new worksheet as a table. |
| Close & Load To… | Saves the query and opens the Import Data dialog, so you choose where the result goes. |
| Discard & Close | Closes the editor without saving the changes from this session. |
| Options & Settings | Query Options for default behaviour, and Data Source Settings for permissions and credentials. |
In the Import Data dialog, you can load the result as a Table, a PivotTable Report, a PivotChart, or Only Create Connection. Tick Add this data to the Data Model to use it in Power Pivot and relationships between tables.
Choose Only Create Connection for helper queries you’ll merge or append later. That keeps them out of your worksheets.
Home tab
The Home tab collects the commands you’ll use in almost every query. The numbers in the screenshot match the table.
| # | Group | What you can do |
|---|---|---|
| 1 | Close | Close & Load, and Close & Load To, the same as on the File tab. |
| 2 | Query | Refresh the preview, open Properties and the Advanced Editor, and manage queries: delete, duplicate or reference them. |
| 3 | Manage Columns | Choose Columns to keep, Remove Columns, or Go to Column in a wide table. |
| 4 | Reduce Rows | Keep Rows: top, bottom, a range, duplicates or errors. Remove Rows: top, bottom, alternate, duplicates, blank rows or errors. |
| 5 | Sort | Sort a column ascending or descending. |
| 6 | Transform | Split Column, Group By, Data Type, Use First Row as Headers, and Replace Values. |
| 7 | Combine | Merge Queries to join tables side by side, like a lookup. Append Queries to stack tables on top of each other. Combine Files for a whole folder. |
| 8 | Parameters | Create values a query can use, such as a year or a file path, so you can change one input instead of editing the query. |
| 9 | Data Sources | Data Source Settings, for changing a file path or updating credentials. |
| 10 | New Query | Start a new query from New Source, Recent Sources or Enter Data. |
Parameters in action: build a query that imports a web page for 2010, then replace 2010 in the address with a parameter. Change the parameter to 2024 and refresh, and the same query pulls 2024’s data. See the full walkthrough in Passing a parameter in Power Query.
Transform tab
Commands on the Transform tab change the selected column in place. The numbers in the screenshot match the table.
| # | Group | What you can do |
|---|---|---|
| 1 | Table | Work on the whole table: Group By, Use First Row as Headers, Transpose, Reverse Rows and Count Rows. |
| 2 | Any Column | Change the data type, Detect Data Type, Rename, Replace Values, Fill Down or Up, Pivot, Unpivot, Move and Convert to List. |
| 3 | Text Column | Split and Merge columns, Format (UPPER, lower, Trim, Clean, add prefix or suffix), Extract (first or last characters, text between delimiters) and Parse JSON or XML. |
| 4 | Number Column | Statistics, Standard maths, Scientific, Trigonometry, Rounding and Information (Is Even, Is Odd, Sign). Most of these are greyed out for text columns. |
| 5 | Date & Time Column | Extract the year, month, quarter, week or day, calculate age, and work with times and durations. |
| 6 | Structured Column | Expand, Aggregate or Extract Values from columns that contain tables or lists, such as after a merge. |
Unpivot Columns is the command people come back for most. It turns a wide, cross-tab layout, such as one column per month, into a tall list that PivotTables and charts can use.
Transform tab vs Add Column tab
Many commands, such as Format, Extract and the date options, appear on both tabs. The difference is what happens to your original column:
| Transform tab | Add Column tab | |
|---|---|---|
| Original column | Changed | Kept as it is |
| Result | Replaces the original values | Appears in a new column |
| Example: Extract Year from a date | The date column becomes years | A new Year column is added next to the dates |
Use Add Column when you still need the original values, for example to keep the full date and add a Year column for grouping.
Add Column tab
Every command on this tab creates a new column.
| # | Group | What you can do |
|---|---|---|
| 1 | General | Column From Examples (type a few results and Power Query works out the rule, like Flash Fill), Custom Column with your own formula, Conditional Column for if-then rules, Index Column, Duplicate Column and Invoke Custom Function. |
| 2 | From Text | The same text commands as the Transform tab, applied to a new column. |
| 3 | From Number | The same number commands as the Transform tab, applied to a new column. |
| 4 | From Date & Time | The same date and time commands as the Transform tab, applied to a new column. |
View tab
The View tab controls what you see, and has two tools worth turning on straight away.
| # | Group | What you can do |
|---|---|---|
| 1 | Layout | Show or hide the Formula Bar and the Query Settings pane. |
| 2 | Data Preview | Column Quality shows the share of valid, error and empty values. Column Distribution shows distinct and unique counts. Column Profile shows detailed statistics. Monospaced and Show Whitespace help you spot alignment problems and stray spaces. |
| 3 | Columns | Go to Column, for jumping around wide tables. |
| 4 | Parameters | Always Allow, which lets you use parameters in dialog boxes. |
| 5 | Advanced | Advanced Editor, which shows the complete M code of the query. |
| 6 | Dependencies | Query Dependencies, a diagram of how your queries and sources connect. |
Turn on the Formula Bar and Column Quality first. The formula bar shows exactly what each step does, and Column Quality flags errors and blanks before they reach your worksheet.
By default, the column statistics are based on the first 1,000 rows. Click the text at the bottom of the window, Column profiling based on top 1000 rows, to switch to the entire data set.
Query Dependencies draws a map of your queries. It’s the quickest way to see what feeds what before you delete or rename anything.
Working with Applied Steps
- There’s no Undo button. To reverse a change, delete its step with the X next to it in Applied Steps.
- Rename steps with descriptive names, such as “Removed blank rows”, so the query explains itself months later.
- Click an earlier step to see the data as it was at that point. If you add a new step there, Power Query asks you to confirm before inserting it.
- Click the gear icon next to a step to reopen its settings and change them without starting over.
Frequently asked questions
How do I open the Power Query Editor in Excel?
Go to Data → Get Data → Launch Power Query Editor, or press Alt + F12. To edit an existing query, open Data → Queries & Connections and double-click it.
Why can’t I type in the Power Query Editor?
The preview grid is read-only by design. Power Query records every change as a step so it can repeat it on refresh, so changes have to be made with commands, not by typing into cells.
How do I get back to the editor after closing it?
Click any cell in the loaded table and choose Query → Edit, or double-click the query in the Queries & Connections pane.
Where is the formula bar in Power Query?
It’s hidden by default in some versions. Go to View → Formula Bar to turn it on.
Is Power Query available in my version of Excel?
It’s built into Excel 2016, 2019, 2021, 2024 and Microsoft 365 for Windows, under Data → Get & Transform Data. Excel 2010 and 2013 need the free Power Query add-in. Excel for Mac supports Power Query too, with fewer data sources.