Power Query in Excel: Import and Clean Data Automatically | Free Excel Course: From Beginner to Advanced

What you'll learn

Import data from CSV files, other workbooks and entire folders.
Clean data (spaces, data types, extra columns) without formulas and without redoing the work every month.
Merge tables and unpivot reports so you can analyze them.
Refresh your whole report with a single click.
Avoid the classic failures: inner double spaces, renamed columns and broken file paths.

📥 Practice this lesson

Load the Customers to Clean sheet into Power Query (Data → From Table/Range) and turn the names into "Ashley Johnson" format without typing a single formula on the sheet.

practice-excel.xlsx · Customers to Clean sheet

If every week you download a report from your company's system, delete columns, fix formats, paste the data into your file and rebuild the summary, Power Query will give you hours back. It's built into Excel for Windows (since Excel 2016, under Data → Get & Transform Data) and Excel for Mac in Microsoft 365 also includes it, with fewer data sources. Power Query records every cleanup step and repeats it automatically when new data arrives.

3phases: get the data, transform it and load it

1 clickon Refresh All repeats the whole cleanup on new data

2016the first Windows version with Power Query built in

Get the data
Clean it in the editor
Unpivot reports
Merge and append
Load and refresh

The key idea: steps that repeat themselves

When you clean data by hand or with formulas, the work is gone next month: you have to do it again. In Power Query every action (removing a column, changing a type, filtering rows) is saved in the Applied Steps list. When you refresh, Power Query re-reads the source and replays every step in order.

Step 1: get the data

Everything starts at Data → Get Data (or the Get & Transform Data group):

Source	Where	Typical use
A table or range in this workbook	From Table/Range	Cleaning data you already have in Excel
A text or CSV file	From File → From Text/CSV	Exports from your ERP, bank, Shopify or QuickBooks
Another Excel workbook	From File → From Workbook	Reports from other departments
A folder	From File → From Folder	Combining every monthly file into one table
Web, databases, SharePoint	From Other Sources	Company data or data published online

Once you pick a source you'll see a preview. Click Transform Data (not "Load") to open the Power Query Editor.

Step 2: clean the data in the editor

The editor looks like Excel, but every click is a recorded step. The most useful transformations:

Problem	Power Query fix
The headers are in the second row	Home → Remove Rows → Remove Top Rows, then Use First Row as Headers
Columns you don't need	Select the ones you do want → right-click → Remove Other Columns (new columns in the source won't break your report)
Numbers or dates read as text	Click the icon on the column header → choose the type. For day-first dates from a supplier abroad: Change Type → Using Locale
Extra spaces	Transform → Format → Trim removes leading and trailing spaces. Careful: unlike Excel's `TRIM`, it doesn't collapse double spaces in the middle (see the worked example)
Inconsistent capitalization	Format → Capitalize Each Word
A code and a name in one cell ("TX-01 Dallas")	Split Column → By Delimiter
Blank or total rows	Filter them out from the header arrow, or Remove Rows → Remove Blank Rows
Empty cells that should repeat the value above	Transform → Fill → Down
Misspelled values ("Wst")	Right-click → Replace Values

Made a mistake?

There's no Ctrl + Z like in Excel: delete the step with the ✕ in the Applied Steps list. Click any earlier step to see what the data looked like at that point.

1. Why is "Remove Other Columns" better than "Remove Columns"?

Because it's faster.
Because if the source file adds new columns, your query keeps working the same way.
Because it isn't saved as a step.

By stating which columns to keep, any new column in the source is dropped automatically.

Unpivot: turn a report back into data

Many reports arrive in "report format," with one month per column:

Product	Jan	Feb	Mar
Coffee	120	135	150
Tea	80	75	90

In that shape you can't filter by month or build a comfortable PivotTable. Select the Product column → right-click → Unpivot Other Columns. You get a list with the columns Product, Attribute (the month) and Value: six rows, the right format for analysis (lesson 1's rules).

2. A report has one column for each month of the year. Which transformation turns it into a list you can analyze?

Split Column
Fill Down
Unpivot Columns

Unpivot turns the month columns into rows: one row per product per month.

Merge and append queries

Append QueriesOne table under another

Result: More rows, same columns.
Requirement: The tables must have the same columns (same names).
Example: January sales + February sales = sales for both months.

Stack

Merge QueriesA VLOOKUP for the whole table

Result: Same rows, more columns.
Requirement: A shared column (the product SKU, the customer ID).
Example: Add each sale's category from the catalog. Use Left Outer to keep every sale even if a product is missing from the catalog.

Enrich

The star trick: combine every file in a folder

OnceOne folder for the reportsSave every monthly report there, all with the same structure.
OnceConnect the folderGet Data → From File → From Folder → pick the folder → Combine & Transform Data.
OnceCleanApply your cleanup in the editor: it's saved as steps.
Every month · 10 secondsRefreshDrop the new file in the folder and click Refresh All. The new month is added automatically.

Group By

Transform → Group By summarizes data inside Power Query: for example, total amount and number of orders per customer. It's useful when the source table is huge and you only need the summary.

Step 3: load and refresh

Home → Close & Load To… lets you choose:

Table on a new sheet (the most common).
Only Create Connection: the query exists but isn't displayed. Useful for intermediate queries.
Add this data to the Data Model: for millions of rows and related tables, the foundation of Power Pivot and Power BI.

To refresh: Data → Refresh All (Ctrl + Alt + F5). If you have PivotTables built on the result, the same button refreshes them too.

Careful when you move files

If a query reads a file or folder on your computer and you move it, or email the workbook to someone else, the path no longer exists. Update it in Data → Get Data → Data Source Settings. For teams, keep sources in a shared location (OneDrive, SharePoint or a network drive).

Worked example: clean up the customer list

The Customers to Clean sheet has names like " ashley JOHNSON " or " maria GARCIA". If you only apply Trim, the first becomes "ashley JOHNSON": no spaces at the ends, but the double space in the middle survives. To make it perfect:

Select the range → Data → From Table/Range → confirm "My table has headers."
Add Column → Custom Column, named Customer, with this formula, which splits the text at each space, drops the empty pieces and joins it back with single spaces:
```
Text.Combine(List.Select(Text.Split([#"Customer (original)"], " "), each _ <> ""), " ")
```
With the new column selected: Transform → Format → Capitalize Each Word.
Select Customer and the email column → Remove Other Columns; apply Trim to the email (its extra spaces are only at the ends).
Close & Load.

Result: "Ashley Johnson," "Maria Garcia," "Chris Anderson"… Paste 500 new customers into the original sheet tomorrow, click Refresh, and they come out clean too. For the bigger picture of data cleaning (duplicates, outliers, missing values), see the data cleaning lesson in our free data analytics course.

Case study: the Monday report at a Chicago-area hardware chain

Lauren, staff accountantHardware chain with 12 stores · Chicago area, Illinois

Every Monday she received 12 CSV files, one per store. She copied them one by one, deleted columns, fixed dates that arrived as text and rebuilt the summary. She set up a From Folder query with the cleanup recorded and a PivotTable on top. Now she drops the new files in the folder, clicks Refresh All and reviews the result.

Minutes every Monday · before

Combine120

Clean45

Review15

Minutes every Monday · after

Combine0

Clean0

Review10

From 180 to 10 minutes a week: about 11 hours freed up every month (170 × 4 = 680 minutes).

Common Power Query mistakes

Clicking Load in the preview and cleaning up afterward on the sheet.

Click Transform Data: the cleanup has to be recorded in the query.

Trusting Trim to leave names perfect.

Trim only handles the ends. For inner double spaces, use the custom column from the example.

Ignoring the "The column … of the table wasn't found" error after the system renames a header.

Open the failing step (often the automatic Changed Type) and update the column name.

Finding that the PivotTable doesn't show new data after Refresh All.

In the query's properties, turn off Enable background refresh so the PivotTable waits for the query.

A quick look at the M language

Every step you take with the mouse is written in a language called M. You can see it under Home → Advanced Editor. You don't need to learn it to use Power Query, but reading it helps you understand and tweak your queries. A step looks like this:

#"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", type number}})

3. A new month's report lands in your folder. What do you do to include it in your report?

Click "Refresh All."
Repeat the cleanup and copy the data over.
Create a new query.

The folder query reads every file and replays the steps when you refresh.

4. You want to add each product's category to your sales table from another table. What do you use?

Append Queries
Merge Queries
Group By

Merge joins tables on a shared column (like a VLOOKUP). Append stacks them one under the other.

Practice exercise: automate a monthly report

Create three CSV files (January, February and March) with the columns Date, Product, Quantity, Price. On purpose, add extra spaces and type some products in lowercase.
Save them in one folder and combine them with From Folder.
Clean: trim spaces, capitalize each word, set the data types and add a custom column [Quantity] * [Price] (Add Column → Custom Column).
Load the result and create a PivotTable of sales by month and product.
Create a fourth CSV (April), drop it in the folder and click Refresh All.

See solution

After refreshing, April should appear in the PivotTable without you touching anything else. If a product shows up twice ("Coffee" and "coffee "), you didn't trim or standardize the capitalization before loading. Go back to the query, add the missing step and refresh again.

Summary

Power Query records every cleanup step and replays it when you refresh.
Remove Other Columns, change types, trim, split and fill down solve most problems.
Unpivot turns reports into analyzable data; Merge joins tables; Append stacks them.
With "From Folder," a new file is included with one click.
Trim doesn't remove inner double spaces: always check the result before loading.

Next lesson: Excel Macros: What They Are, Examples and How to Create Them with VBA. You'll automate repetitive tasks with macros and take your first steps in VBA.