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

Nothing you do in the editor changes the original file. Power Query only reads it.

Common cleaning steps

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

Summary

Back to: all Excel tutorials.