Power Query in Excel for Beginners: Import and Clean Data
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016 (Get & Transform)
If you download the same report every week or month and spend an hour tidying it before you can use it, Power Query is for you. It is a tool built into Excel that imports data, cleans it and loads it into a sheet. The clever part is that it records every step you take. Next month, you drop in the new file, click Refresh, and all the cleaning happens again in seconds. You do not need to write any code; everything is done with buttons and menus.
Where to find Power Query
In current versions of Excel, Power Query lives on the Data tab in the Get & Transform Data group. The buttons there, such as Get Data, From Text/CSV and From Table/Range, all open Power Query. In Excel 2010 and 2013 it was a separate free add-in with its own tab.
Import a file
Suppose your accounting system exports a CSV file of sales every month. Go to Data > From Text/CSV, choose the file and click Import. A preview appears showing the first rows. Power Query has already guessed the separator and the data type of each column. Rather than clicking Load straight away, click Transform Data to open the Power Query Editor, where the cleaning happens.
You can also start from data that is already in your workbook: click inside a list and choose Data > From Table/Range. Excel turns the list into an Excel Table if it is not one already.
A tour of the Power Query Editor
- The data preview in the middle shows your data as it looks after the current steps.
- The column headers have an icon showing each column's data type (ABC for text, 123 for whole numbers, a calendar for dates) and a filter arrow.
- The Applied Steps list on the right records everything you do. Click any step to see the data at that point, click the X beside a step to delete it, or click the gear icon to change its settings.
- The ribbon holds the commands, mainly on the Home, Transform and Add Column tabs.
Nothing you do in the editor changes the original file. Power Query only reads it.
Common cleaning steps
- Remove columns you do not need: select the columns you want to keep with Ctrl + click, then choose Home > Remove Columns > Remove Other Columns. Keeping columns rather than removing them means new unwanted columns in future files are ignored.
- Remove top rows: many reports start with a title and a blank line. Use Home > Remove Rows > Remove Top Rows, then Use First Row as Headers.
- Fix data types: click the type icon in a header and choose Date, Decimal Number or Text. For dates in a different regional format, choose Using Locale and pick the country the data came from.
- Trim and clean text: right-click a column and choose Transform > Trim to remove extra spaces, the same job the TRIM function does in a sheet. See text functions.
- Split a column: Home > Split Column > By Delimiter splits "DXB-2026-0451" at the hyphens.
- Replace values: right-click a column and choose Replace Values to change "N/A" to null or "Dubia" to "Dubai".
- Filter rows: use the header filter arrow to remove blank rows, totals or cancelled orders.
- Remove duplicates: select the key column and choose Home > Remove Rows > Remove Duplicates.
- Add a calculated column: Add Column > Custom Column lets you write simple calculations such as
[Amount] * 0.05for VAT.
Unpivot: turn a wide report into a proper list
Reports often arrive with one column per month: Product, Jan, Feb, Mar and so on. That layout is easy to read but hard to analyse. Select the Product column, right-click and choose Unpivot Other Columns. Power Query turns the data into three columns, Product, Attribute (the month) and Value, with one row for each product and month. Rename the columns, and the data is ready for a pivot table or SUMIFS. This one step can replace hours of copying and pasting.
Load the result into Excel
When the preview looks right, choose Home > Close & Load. The clean data appears on a new sheet as a table, and the Queries & Connections pane opens on the right showing your query and its row count. Use Close & Load To if you would rather load straight into a pivot table or only create a connection.
Refresh with next month's data
Save next month's export with the same file name in the same folder, replacing the old one. In Excel, choose Data > Refresh All (Ctrl + Alt + F5). Power Query reads the new file and repeats every applied step. If the file name or location changes, open the query, click the gear beside the Source step and browse to the new file.
Combine many files from a folder
If each branch sends its own file with the same layout, put them all in one folder. Choose Data > Get Data > From File > From Folder, select the folder and click Combine & Transform Data. Power Query stacks every file into one table and adds a column showing which file each row came from. Add a new file to the folder, refresh, and it is included automatically.
Merge queries: a lookup without formulas
Home > Merge Queries joins two tables on a shared column, much like XLOOKUP. For example, merge a sales query with a product list on Product Code, then expand the new column to bring in the product name and category. For large data sets this is faster and more reliable than thousands of lookup formulas.
Tips for success
- Rename steps in Applied Steps (right-click > Rename) so you remember what each does.
- Set data types near the end of your steps, after splitting and cleaning.
- Keep source files in a stable location, such as a shared folder, rather than Downloads.
- Do not type extra data into the loaded table; it will be overwritten on refresh. Add notes on a separate sheet.
Summary
- Power Query imports and cleans data, recording each step.
- Use Transform Data to open the editor before loading.
- Remove columns and rows, fix types, trim, split, filter and unpivot with menus.
- Refresh All repeats every step on new data.
- From Folder combines many files; Merge Queries replaces lookup formulas.
Back to: all Excel tutorials.