Getting started with Power Query: import and clean data automatically
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.