Getting started with Power Query: import and clean data automatically
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.
Excel Power Query Course: Power Query Tutorial for Beginners
Simon Sez IT
Open on YouTube (opens in a new tab)
Power Query is Excel’s built-in tool for pulling in data and reshaping it — from a file, a database, or the web — through a sequence of steps that stay attached to the result. Change the source data, click refresh, and every step reruns automatically.
Step 1: Get data
Go to Data → Get Data, then pick a source: From File (CSV, another workbook), From Table/Range (a table already in this workbook), or From Web. For a first attempt, From Table/Range on any data in your current sheet is the easiest way in.
Step 2: The Power Query Editor
This opens a separate window with your data in a grid and an Applied Steps list on the right. This list is the whole point of Power Query: every transformation you make — a filter, a rename, a split — gets recorded as a named step, in order, and can be edited or removed later without starting over.
Step 3: Common transforms
A few operations cover most cleanup work:
- Remove Duplicates — right-click a column header, or select the whole table and use the Home tab
- Remove Blank Rows — Home → Remove Rows → Remove Blank Rows
- Split Column — Home → Split Column → By Delimiter, useful for pulling
apart something like
"Smith, John"into two columns - Change Type — click the icon in a column header to fix a column that imported as text but should be a number or date
- Filter Rows — the dropdown arrow on any column header, same as a worksheet AutoFilter
Each of these becomes its own line in Applied Steps.
Step 4: Close & Load
When the data looks right, Home → Close & Load sends it to a new worksheet as a table (or to the Data Model, if you’re feeding a PivotTable across multiple sources).
The actual benefit: refresh
The reason to use Power Query over manual cleanup is what happens next time. Paste in a new export of the same report, right-click the table, choose Refresh, and every step — the filter, the split, the dedupe — reruns on the new data automatically. A manual cleanup has to be redone from scratch every time; a Power Query cleanup doesn’t.
When you don’t need it
For a one-off CSV that you’ll never touch again, opening Power Query is often more setup than the job needs. The CSV cleaner tool on this site does trim/dedupe/blank-row-removal instantly in your browser for exactly that case — reach for Power Query once you’re repeating the same cleanup on a recurring basis.
Go deeper with this skill
Create a repeatable cleanup and refresh process instead of doing the same manual edits every month. For this article, the goal is to practice: How to pull data into Excel with Power Query, apply repeatable cleaning steps, and load the result — without touching a single formula.
Practice workbook setup
Use a raw export with extra columns, inconsistent text, blanks, or repeated monthly files.
Practice workflow
- Import the data into Power Query and rename every meaningful step.
- Remove columns only after confirming they are not needed downstream.
- Set data types deliberately for dates, numbers, IDs, and text.
- Load the cleaned result to a table and refresh it after changing the source.
Quality checks
- Applied steps are named clearly enough to review later.
- Data types are correct before loading the result.
- Refresh works without manual cleanup in the worksheet.
Common mistakes
- Editing the loaded table instead of the query steps.
- Removing columns before checking whether reports depend on them.
- Ignoring errors caused by new values or changed source headers.
Next actions
- Create a small refresh checklist and keep it beside the report.
- Try combining two files or appending a new month to test repeatability.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.
