Power Query in Excel: Import, Transform, and Refresh Data Automatically

Power Query is Excel's built-in ETL (Extract, Transform, Load) tool. It replaces the tedious work of manually cleaning and reshaping data every time a file updates. You build the transformation steps once, and every future refresh happens in seconds.

What Is Power Query and When Should You Use It?

Power Query sits between your source data and your Excel sheet. You point it at a file, database, or folder; it imports the data; you apply cleaning and shaping steps; then it loads the result into Excel.

Use Power Query when you:

Receive a CSV or Excel export from another system on a regular schedule
Need to combine data from multiple files or sheets
Have to repeat the same cleaning steps (remove blank rows, rename columns, split a column) every time you get new data
Want a refreshable report that updates without manual intervention

Don't use Power Query for:

One-off quick lookups (VLOOKUP or XLOOKUP is faster)
Data you never update
Calculations that depend on the current time (NOW(), TODAY()) — Power Query runs on refresh, not continuously

Import a CSV File

Data tab → Get Data → From File → From Text/CSV
Navigate to the file → click Import
A preview window opens showing how Excel has parsed the file

- Check the File Origin (encoding) — usually UTF-8 is correct - Check the Delimiter — comma, tab, semicolon, etc.

If the preview looks correct, click Transform Data to open the Power Query Editor

(Click Load to skip the editor and load directly — only do this if no cleaning is needed)

Import an Excel File

Data → Get Data → From File → From Workbook
Select the file → Import
A Navigator panel shows all sheets and named Tables in that file
Select the sheet or Table you want → Transform Data

The Power Query Editor

The editor is a separate window with its own ribbon. Key areas:

Left panel: Queries list (all queries in this workbook)
Center: Data preview
Right panel: Applied Steps — every transformation you make is recorded here as a step
Formula bar: Shows the M code for the selected step

The Applied Steps panel is what makes Power Query powerful. You can delete, reorder, or edit any step. If you need to redo the whole thing, just delete steps from the bottom up.

Common Transformations

Filter Rows

Click the dropdown arrow in any column header → uncheck values to hide them, or use Number Filters / Text Filters / Date Filters for conditional logic.

Sort

Click a column header dropdown → Sort Ascending or Sort Descending.

Rename Columns

Double-click any column header in the preview to rename it inline.

Remove Columns

Select a column (click its header) → right-click → Remove Columns. Hold Ctrl to select multiple columns.

Remove Blank Rows

Home tab in the editor → Remove Rows → Remove Blank Rows

Split a Column

Right-click a column → Split Column → By Delimiter or By Number of Characters. Useful for splitting "FirstName LastName" or "City, State" fields.

Change Data Type

Click the type icon to the left of each column header (ABC = text, 123 = number, calendar = date). Always set data types explicitly — Power Query sometimes guesses wrong.

Filter and Keep Top N Rows

Home → Keep Rows → Keep Top Rows → enter a number. Useful for testing with a small sample.

Load Results to Your Sheet

When your transformations are done:

Home tab (in the Power Query Editor) → Close & Load

- Close & Load: Creates a new sheet and loads the data as a Table - Close & Load To: Opens a dialog — choose Existing Worksheet, specify a cell, or load as a Connection Only (no output table, useful if you're combining this query into another)

The loaded data appears as an Excel Table with a distinct style. You can build pivot tables and formulas on top of it normally.

Refresh Data

When your source file updates, bring the changes into Excel:

Right-click anywhere in the loaded Table → Refresh
Or: Data tab → Refresh All (refreshes all queries and pivot tables in the workbook)
Keyboard shortcut: Ctrl+Alt+F5

Power Query reruns all your Applied Steps against the updated source data. If the source file structure is the same (same columns), the refresh is seamless. Auto-refresh on file open: Data → Queries & Connections → right-click your query → Properties → check Refresh data when opening the file.

Frequently Asked Questions

Does Power Query work on Mac?

Yes, Power Query is available in Excel for Mac (Microsoft 365 and Excel 2019+). Some connectors (like SharePoint and SQL Server) may require additional configuration, but CSV and Excel file imports work the same as Windows.

Can Power Query combine multiple CSV files from a folder?

Yes — this is one of its best features. Data → Get Data → From File → From Folder. Select the folder containing your CSVs. Power Query imports all files, stacks them vertically, and runs your cleaning steps on the combined result. Every new file added to the folder is included on the next refresh.

What happens if the source file is moved or renamed?

Power Query stores the file path. If the file moves, the refresh will fail with a "file not found" error. Fix it: Data → Queries & Connections → right-click the query → Edit → in Applied Steps, click the first step (Source) → update the file path in the formula bar.

Is Power Query the same as Power BI?

They share the same transformation engine (M language), but they're different products. Power Query in Excel loads data into your workbook. Power BI Desktop is a separate analytics application. A query you build in Excel can often be copy-pasted into Power BI and vice versa.