Introduction
Many people spend hours every month cleaning the same reports: deleting the same header rows, splitting the same columns, fixing the same dates, copying several files into one sheet. Power Query, on the Data tab under Get & Transform, does that work once and remembers it. Next month, you replace the source file and click Refresh. It is built into Excel for Windows since Excel 2016 and is available in Microsoft 365 for Mac, with fewer data sources on Mac.
How it works
Get data. Data, Get Data offers many sources: From Text/CSV, From Workbook, From Folder (to combine every file in a folder), From Table/Range, and databases and web pages on Windows. Choose one and click Transform Data to open the Power Query Editor.
Record steps. Every action in the editor is recorded in the Applied Steps list on the right:
- Remove columns you do not need (select them, Remove Columns) and remove top rows or blank rows.
- Change Type: set each column to Whole Number, Decimal, Date or Text. Power Query guesses types; check them, because a wrong type is the most common cause of errors.
- Split Column by delimiter, Trim and Clean text, Replace Values, Fill Down to fill blanks below a heading.
- Filter rows with the drop-downs on the column headings, just like AutoFilter.
- Add Column, Custom Column for calculations.
Each step builds on the one before. You can click a step to see the data at that point, rename steps, or delete one. Behind the scenes, Power Query writes the steps in its own formula language, called M, shown in the Advanced Editor; you do not need to write M to use Power Query.
Reshape. Unpivot Columns turns a wide table, such as one column per month, into a long tidy table with Month and Amount columns, which is what PivotTables and charts need. Append stacks tables with the same columns, such as twelve monthly files. Merge joins two queries on matching columns, like a lookup, with join kinds such as left outer (all rows from the first table) and inner (only matches).
Load and refresh. Home, Close & Load sends the result to a new sheet as a Table, or Close & Load To lets you create only a connection, a PivotTable or add it to the data model. When the source changes, Data, Refresh All replays every step on the new data.
Example
Every month a system exports a CSV of sales with two lines of report title at the top, dates as text and one column per region:
| Month | North | South |
|---|---|---|
| Jan-2026 | 1200 | 900 |
| Feb-2026 | 1500 | 800 |
Line by line
- Data, Get Data, From Text/CSV, choose the file, then Transform Data.
- Remove Top Rows: 2, then Use First Row as Headers.
- Set the Month column's type to Date, and North and South to Whole Number.
- Select the Month column, then Transform, Unpivot Other Columns; rename the new columns Region and Sales.
- Close & Load to a new sheet.
Expected result
The sheet gets a Table with three columns, Month, Region and Sales, and four rows: January North 1200, January South 900, February North 1500 and February South 800. Next month, after saving the new export over the old file, Refresh All rebuilds the table with the new rows and no manual work.
In the real world
Power Query is how analysts combine dozens of branch files, clean exports from accounting and sales systems, and prepare data for PivotTables and Power BI, which uses the same Power Query engine. Monthly reporting that took a day often becomes one refresh.
Common mistakes
- Wrong data types left as Power Query guessed, giving errors or text that will not sum.
- Steps that depend on fixed column names that change in next month's file.
- Editing the loaded table by hand, which is lost on the next refresh.
- Moving or renaming the source file, which breaks the query until the source path is updated.
Best practices
- Rename steps and queries clearly so others can follow the recipe.
- Keep source files in a fixed folder and use From Folder for repeated monthly files.
- Unpivot wide data into a tidy long table before building PivotTables.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Refresh all queries and PivotTables | Ctrl+Alt+F5 | — |
| Refresh the selected query table | Alt+F5 | — |
| Get Data by key tips | Alt, A, P, N | — |
| Create a Table before From Table/Range | Ctrl+T | — |
Summary
Power Query imports data from files, folders, Tables and databases, records each cleaning step in Applied Steps, and replays them on every refresh. It removes rows and columns, fixes types, splits and replaces text, unpivots wide tables, appends tables with the same columns and merges tables on keys, then loads the result as a Table, a connection or a PivotTable.
Quick check
Key takeaways
- Power Query records cleaning steps once and replays them on every refresh.
- Append stacks tables; Merge joins them on keys; Unpivot turns wide tables into tidy long ones.
- Close & Load sends the result to a sheet, a PivotTable or the data model; Refresh repeats everything.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.