Power Query: import and clean data without formulas
Record your data clean-up once and re-run it with Refresh: combine files, fix text and types, remove duplicates, merge tables and group results.
In this lesson you will
- Explain Get, Transform, Load and Refresh
- Use the common transform steps
- Append and merge queries
- Follow good habits
About 17 minutes
Stop repeating the same clean-up every month
Every month Rukhsar receives sales files from three branches. Each time she opens them, copies the rows into one sheet, trims spaces, fixes capitals, removes repeats and looks up the manager’s name. It takes two hours and she makes a different small mistake every time. Power Query records those steps once. Next month she drops in the new files, clicks Refresh, and the same clean-up runs in seconds.
Power Query is built into Excel 2016 and later for Windows (under Data, Get Data), and in recent Excel for Mac and Microsoft 365. Google Sheets does not have it. It could not be run in the environment used to prepare this lesson, so the steps below are described, and the same transformation is shown in Python so you can see what each step does.
The idea in one picture
- Get the data from a file, a folder, a web page or a database.
- Transform it in the Query Editor: each action becomes a recorded step.
- Load the result into a sheet or the data model.
- Refresh later: the steps run again on the new data.
Getting data
- Data, Get Data, From File, From Text/CSV or From Workbook for a single file.
- From Folder combines every file in a folder into one table. Drop in next month’s file and refresh.
- From Web reads a table from a web page.
- Excel tables and ranges in the same workbook can be queried too (Data, From Table/Range).
The steps you will use most
| Step | What it does |
|---|---|
| Use First Row as Headers | Makes row 1 the column names |
| Change Type | Sets text, number or date types |
| Trim, Clean, Capitalize Each Word | Fixes spacing and capitals (Transform tab) |
| Remove Duplicates, Remove Blank Rows | Tidies repeats and gaps |
| Split Column, Merge Columns | Splits or joins text by a delimiter |
| Append Queries | Stacks tables with the same columns |
| Merge Queries | Joins tables like a lookup (a join) |
| Group By | Summarises, like a PivotTable |
The same steps in code
The program below does the same job on two small month files of sales. It appends the rows, trims and capitalises the region, converts types, removes a repeated row, merges with a table of managers and prices, and groups by region. The data is invented for the example. It is Python, not Power Query, but each commented line matches a Query Editor step. The output is real:
import csv, io
jan = """date,region,product,units
2026-01-05, north ,Pen,10
2026-01-12,SOUTH,Notebook,5
2026-01-12,SOUTH,Notebook,5
2026-01-20,North,Notebook,8
"""
feb = """date,region,product,units
2026-02-03,East,Pen,20
2026-02-14,south,Pen,15
2026-02-25,West,Notebook,6
"""
managers = {"North": "Asha", "South": "Ben", "East": "Chen", "West": "Dina"}
price = {"Pen": 20, "Notebook": 60}
def read(text):
return list(csv.DictReader(io.StringIO(text)))
rows = read(jan) + read(feb) # Append queries
print("1. appended rows:", len(rows))
for r in rows: # Clean text
r["region"] = r["region"].strip().title()
r["units"] = int(r["units"]) # Change type
seen, clean = set(), []
for r in rows: # Remove duplicates
key = tuple(r.values())
if key not in seen:
seen.add(key); clean.append(r)
print("2. after removing duplicates:", len(clean))
for r in clean: # Merge with lookup tables, add a column
r["manager"] = managers[r["region"]]
r["revenue"] = r["units"] * price[r["product"]]
totals = {} # Group by
for r in clean:
totals[(r["region"], r["manager"])] = totals.get((r["region"], r["manager"]), 0) + r["revenue"]
print("3. revenue by region and manager:")
for (reg, man), t in sorted(totals.items()):
print(f" {reg:6s} {man:5s} {t}")
1. appended rows: 7
2. after removing duplicates: 6
3. revenue by region and manager:
East Chen 400
North Asha 680
South Ben 600
West Dina 360
Count what happened. Seven rows were appended, the duplicated South row was removed leaving six, and the cleaned rows were grouped. Notice that ‘ north ‘ and ‘North’ became the same region, and ‘south’ and ‘SOUTH’ joined too. Without the clean-up the four regions would have appeared as six separate groups, because ‘ north ‘, ‘North’, ‘SOUTH’ and ‘south’ all look different to a computer.
What the recorded steps look like
Power Query writes its steps in a language called M. You rarely need to edit it, but it helps to know it is there. A typical query reads like the pattern below. It is shown for explanation and was not run:
let
Source = Csv.Document(File.Contents("jan.csv")),
Promoted = Table.PromoteHeaders(Source),
Trimmed = Table.TransformColumns(Promoted, {{"region", Text.Trim}}),
Typed = Table.TransformColumnTypes(Trimmed, {{"units", Int64.Type}}),
NoDups = Table.Distinct(Typed)
in
NoDups
Good habits
- Name each query clearly and load raw copies as ‘connection only’ so they do not clutter the sheets.
- Never edit the loaded table by hand. Change the query instead, because a refresh replaces the sheet’s contents.
- Keep source files in a stable folder; moving them breaks the link.
- Check row counts at each stage as the code above does, so a missing file does not go unnoticed.
- Data from other people may contain private details. Handle it as carefully as the original file.
When not to use it
For a one-off tidy of a small list, formulas or Flash Fill are quicker. Power Query pays off when the job repeats, when the data is large, or when you combine many files. It is also the reason professionals can handle messy exports without writing a single formula.
Practice
Make two small CSV files with the same columns, including a repeated row and some messy capitals or spaces. In Excel, use Data, Get Data, From Folder, combine them, apply Trim and Capitalize Each Word, remove duplicates and load to a sheet. Then add a third file to the folder and press Refresh. Check that the row count goes up by the right amount.
If Refresh shows an error, the new file probably has a different column name or a changed type. Open the query and look at the step that turns red: the error message names the step and the column.
Editing the loaded table by hand. The next refresh overwrites your edits, and you will not know why they vanished. Change the query steps instead.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does Refresh do in Power Query?
- Deletes the query
- Changes colours
- Re-runs the recorded steps on new data
- Saves a copy
That is the time saver.
Which query action stacks tables with the same columns?
- Append Queries
- Merge Queries
- Group By
- Split Column
Merge is like a lookup.
Why not edit the loaded table by hand?
- Excel forbids it
- A refresh replaces it
- It deletes the file
- It sorts the data
Change the query steps instead.
When is Power Query most useful?
- For typing one number
- For printing
- For changing fonts
- When a clean-up repeats or combines many files
One-off tidies are quicker with formulas.
Which Excel feature gives you the ‘From Folder’ option?
- Home, Font
- Data, Get Data
- Insert, Chart
- View, Zoom
It combines every file in a folder.
Summary
- Power Query records steps and re-runs them on Refresh.
- Append stacks tables; Merge joins them like a lookup.
- Never edit the loaded table by hand.
- It suits repeating, large or multi-file jobs.
Questions
Is Power Query available in every Excel?
It is in Excel 2016 and later for Windows and in Microsoft 365, with some differences on Mac. Check your version under Data.
Is Power Query the same as VBA?
No. Power Query is a point-and-click data tool. VBA is a programming language for macros.
Can it connect to a database?
Yes, with the right permissions. Ask the database owner for access, and keep passwords out of shared files.
