AdvancedLesson 25 of 305 min read

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.

Good to know

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

  1. Get the data from a file, a folder, a web page or a database.
  2. Transform it in the Query Editor: each action becomes a recorded step.
  3. Load the result into a sheet or the data model.
  4. 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.

Practice answer

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.

Common mistake

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?

  1. Deletes the query
  2. Changes colours
  3. Re-runs the recorded steps on new data
  4. Saves a copy

That is the time saver.

Which query action stacks tables with the same columns?

  1. Append Queries
  2. Merge Queries
  3. Group By
  4. Split Column

Merge is like a lookup.

Why not edit the loaded table by hand?

  1. Excel forbids it
  2. A refresh replaces it
  3. It deletes the file
  4. It sorts the data

Change the query steps instead.

When is Power Query most useful?

  1. For typing one number
  2. For printing
  3. For changing fonts
  4. 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?

  1. Home, Font
  2. Data, Get Data
  3. Insert, Chart
  4. 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.

Next lesson

Next: building dashboards.

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.

Progress is saved in this browser.