Power Query in Excel: a hands-on tutorial
Power Query gets data into Excel and shapes it on the way: filter, clean, combine. It records every step, so next month's export takes one click on Refresh instead of an afternoon of copy and paste.
In short: click inside your data, then Data > From Table/Range. The Power Query Editor opens. Every command you use there is listed under Applied Steps on the right. Home > Close & Load sends the result back to Excel as a new table.
Try it now
Below is a simulated Power Query Editor. The source table sits on the sheet at the top. Underneath you get the query preview, the Applied Steps list and, at the bottom, the goal. Click a column header in the preview to select that column, then pick a command.
Task The Orders table is loaded into the Power Query Editor. Keep only the orders from Berlin and remove the Order no column.
| A | B | C | |
|---|---|---|---|
| 1 | Order no | City | Amount |
| 2 | 1001 | Berlin | 240 |
| 3 | 1002 | Hamburg | 180 |
| 4 | 1003 | Berlin | 95 |
| 5 | 1004 | Leipzig | 310 |
| 6 | 1005 | Bremen | 120 |
| 7 | 1006 | Berlin | 400 |
| 8 | 1007 | Hamburg | 75 |
See the bar marked fx? After each step it shows the formula Power Query writes behind the scenes, in a language called M. You can use Power Query for years without typing M, but reading it once is worth it: Table.SelectRows(Source, each [City] = "Berlin") simply means "take the rows of Source where City equals Berlin".
Why use Power Query instead of formulas?
Formulas calculate in cells next to your data. Power Query works one stage earlier: it changes the data itself before it reaches the sheet. That gives you three things formulas cannot:
- Repeatable: the steps are saved with the workbook. When a new export arrives, swap the source and hit Refresh. Every step runs again.
- Transparent: Applied Steps shows each operation in order. A wrong step can be deleted without starting over.
- Non-destructive: the source stays untouched. The result is loaded as a separate table on its own sheet.
For a one-off fix, a formula is often quicker. Once you repeat the same cleanup every week or month, Power Query pays for itself.
How to open the Power Query Editor
- Click inside your list. Format it as a table first (Ctrl+T) so the query picks up new rows automatically.
- On the Data tab, in the Get & Transform Data group, choose From Table/Range.
- Shape the data in the editor. Most commands live on the Home and Transform tabs.
- Click Close & Load. Excel adds a new sheet with the result.
Get Data works the same way for CSV files, other workbooks or a whole folder of files. Once the data is in the editor, the workflow is identical.
Another exercise: headers and data types
When you import a CSV file, Power Query does not always detect the headers, and numbers may arrive as text. That is the situation here.
Task A CSV file was loaded, but Power Query did not detect the headers (Column1 to Column3) and Qty and Price are text. Turn the first row into headers and change the data type of Qty and Price to Whole Number.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Qty | Price |
| 2 | Paper | 12 | 45 |
| 3 | Toner | 3 | 120 |
| 4 | Pens | 25 | 8 |
| 5 | Binders | 6 | 64 |
| 6 | Staplers | 10 | 15 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
Always check data types after loading. A number stored as text is ignored by SUM later. In the real editor, the icon on the left of each column header shows the type, and Data Type on the Home tab changes it.
Common beginner problems
Expression.Error: the column was not found
A later step refers to a column that an earlier step renamed or removed. Click through the steps in Applied Steps until you find the culprit, then delete or edit it.
The result does not change
Power Query only picks up changes in the source on refresh. Use Data > Refresh All, or right-click the result table and choose Refresh.
My edits in the result table disappeared
The loaded table belongs to the query. Formula columns you add to the right survive a refresh, but typing over loaded values does not.
Lessons in this bonus level
- Data cleaning: trim spaces, fix capitalization, split columns, replace values.
- Remove duplicates: by one or more columns, and why case matters.
- VLOOKUP in Power Query: Merge Queries instead of lookup formulas.
- Unpivot: turn crosstabs into lists you can analyze.
- Append and Group By: stack tables and summarize them.
This level is a bonus and does not count toward the final test, but the exercises still earn points.
Power Query in German Excel
| English | German |
|---|---|
| Get & Transform Data | Daten abrufen & transformieren |
| From Table/Range | Aus Tabelle/Bereich |
| Applied Steps | Angewendete Schritte |
| Use First Row as Headers | Erste Zeile als Überschriften verwenden |
| Data Type: Whole Number, Decimal Number, Text | Datentyp: Ganze Zahl, Dezimalzahl, Text |
| Close & Load | Schließen & Laden |
M functions keep their English names in every language, for example Table.SelectRows or Table.PromoteHeaders. Only the step names are translated, so a German colleague's query shows "Gefilterte Zeilen" where yours says "Filtered Rows".
Which Excel versions have Power Query?
According to Microsoft, Power Query is built into every standalone Windows version from Excel 2016 onward, under the name Get & Transform. Microsoft 365 always has the full feature set, while perpetual versions such as Excel 2019 or 2021 have a large subset. Excel 2010 and 2013 used a free add-in, which Microsoft has since officially deprecated.
On a Mac, Power Query requires Microsoft 365; Excel 2016 and 2019 for Mac do not have it. You start it from Data > Get Data (Power Query), and the feature set is smaller than on Windows. In Excel for the web, all Microsoft 365 subscribers can view and refresh queries, while editing depends on the plan.
FAQ
Do I need to code to use Power Query?
No. Every step in these lessons is a ribbon command. Power Query writes the M code for you; reading it helps, typing it is optional.
What is the difference between Power Query and Power Pivot?
Power Query loads and cleans data. Power Pivot builds a data model with relationships and measures on top of it. They often work as a pair, with Power Query going first.
Does Power Query change my original data?
No. The source stays as it is; the result is a separate table in the workbook.
How do I get the result into a PivotTable?
Load the query as a table and build a PivotTable from it. After refreshing the query, refresh the PivotTable as well.