Excel Power Query Guide — From CSV Import to Automated Reports

Published: 2026-04-14

If you work with data in Excel, you've likely faced a repetitive cycle: import a CSV file each month, clean the data, consolidate numbers, and deliver the same report. Power Query breaks that cycle. It records your CSV import and data transformation steps into a reusable query—so next month, a single click refreshes everything.

This guide covers Power Query from the ground up: importing CSVs (single files or entire folders), transforming messy data, and recognizing when to reach for other tools.

Note: Screenshots and steps in this article are based on Excel for Microsoft 365. Your interface may differ depending on version.

What Power Query Does — Automate the Manual Grind

Traditional CSV Import vs. Power Query

The standard approach to bringing CSV data into Excel involves opening the file directly, copying and pasting, or using the "Text Import Wizard." Every time a fresh CSV arrives, you repeat the steps. It works, but wastes time on tasks that haven't changed.

Power Query flips the model. You build a query once—which documents your import logic, cleaning steps, and transformations—and then run it again whenever new data appears. Press refresh, and Excel executes your entire workflow in seconds.

Here's how the two approaches compare:

Aspect	Manual Approach	Power Query
CSV loading	Copy/paste or wizard each time	Load once, refresh automatically
Transformations	Hand-edited columns, filters, etc.	Recorded steps, repeatable
Multi-file workflows	Process files one at a time	Batch-process entire folders
Fixing mistakes	Undo and redo earlier steps	Edit any query step, re-run
Maintenance	Documentation by memory	Visible, editable step list

Three Things Power Query Handles Best (Import, Transform, Merge)

Power Query excels at three critical tasks in the data pipeline.

Task	Capability
Importing data at scale	Reads CSV, text, JSON, and XML formats. Pulls data from APIs, databases, and online sources. Can import dozens of CSVs from a folder and merge them into a single table.
Standardizing and reshaping	Removes unwanted columns, filters rows by criteria, splits and merges text fields, changes data types, adds calculated fields, and pivots data from tall-and-thin to wide-and-short (or vice versa).
Joining tables	Combines multiple data sources using inner joins, left outer joins, and full outer joins—without writing VLOOKUP formulas or complex INDEX/MATCH arrays.

Importing CSV Files with Power Query

Loading a Single CSV — The Basic Steps

Start with a single CSV file. This foundation applies to every Power Query workflow. The process unfolds in six straightforward steps:

Step	Action
1. Open Data tab	Find "Data" in the top ribbon menu
2. Select import option	Get Data > From File > From Text/CSV
3. Choose file	Navigate to and select the CSV file you want to import
4. Review and load	Auto-detect delimiter and encoding, then click Load
5. Data loads into worksheet	CSV appears as a new table; query is automatically saved
6. Edit later if needed	Right-click query in Queries panel and select "Edit"

Batch-Importing Every CSV in a Folder

In many workflows, CSV files arrive monthly in a single folder—often with changing filenames. Power Query can import all of them at once and combine them into one table.

Step	Action
1. Select folder import	Data > Get Data > From File > From Folder
2. Specify folder path	Paste path or browse to select the folder
3. Combine & load	Power Query scans, identifies CSV files, and stacks them automatically
4. Clean up columns	Remove "Source.Name" or "FileName" columns if needed

The payoff: add a new CSV to the folder, and the next refresh automatically incorporates it.

Specifying Encoding and Delimiters Manually

Some CSV files don't follow standard conventions. Power Query may misidentify the encoding (UTF-8 vs. ASCII vs. Windows-1252) or delimiter (comma vs. tab vs. semicolon). When auto-detection fails, specify these manually.

Step	Action
1. Open editor	Right-click your query and select Edit
2. Find the Source step	Look at the formula bar; you'll see `Csv.Document(Source, [Delimiter=","])`
3. Modify formula	Click fx or "Advanced Editor" to edit the function directly
4. Update parameters	Change delimiter and add encoding: `Csv.Document(Source, [Delimiter=";", Encoding=65001])`
5. Save changes	Review preview and click Done

Encoding codes matter: 65001 = UTF-8, 1252 = Windows Western European. Wrong encoding can corrupt data.

Transforming Imported Data — Five Essential Operations

Removing and Reordering Columns

Every CSV import brings unwanted columns—metadata you don't need, duplicate fields, or fields in the wrong order. Power Query handles this in one step, and that step repeats on every refresh.

Action	Steps
Delete column	Right-click column > Select Remove (vanishes from preview)
Reorder columns	Click and drag headers left/right, or right-click and choose "Move"
Auto-repeat	These steps are recorded in your query and repeat on every refresh

Filtering Rows by Condition

You may need only rows that match certain criteria—a specific region, orders above a threshold, or non-null dates. Filters act as persistent rules on your query.

Step	Action
1. Open filter	Click the dropdown arrow in the column header
2. Set condition	Uncheck unwanted values or click "Filter by Value" (e.g., "greater than 1000", "between dates X and Y")
3. Apply	Click OK (only matching rows remain)

Stack multiple filters across different columns to narrow results further.

Splitting, Merging, and Changing Column Types

Raw CSV data often arrives in awkward formats. Power Query reshapes it without formulas.

Split: Turn "2024-01-15" into three columns (year, month, day)

Step	Action
1. Open split menu	Right-click date column > Split Column > By Delimiter
2. Choose delimiter	Select hyphen "-" as the separator
3. Confirm	Click OK (creates three columns: 2024, 01, 15)

Merge: Combine "John" and "Doe" into "John Doe"

Step	Action
1. Select columns	Hold Ctrl and click both headers (John, Doe)
2. Merge	Right-click > Merge Columns
3. Choose separator	Select space (result: single "John Doe" column)

Change type: Convert "12345" (text) to a number

Step	Action
1. Select column	Click the column header
2. Change type	Home tab > Data Type > Integer, Decimal, or target type

Pivot and Unpivot

Sometimes data arrives in "tall and thin" format (many rows, few columns) when you need it "short and wide" (few rows, many columns)—or vice versa.

Pivot: Show daily sales by product across columns

Step	Action
1. Select column	Choose column with row labels (e.g., Product)
2. Pivot	Right-click > Pivot Column
3. Configure	Choose column for headers and column for values (reshapes instantly)

Unpivot: Turn months-as-columns into a rows-and-values format

Step	Action
1. Select columns	Choose all month columns to unpivot
2. Unpivot	Right-click > Unpivot Columns
3. Result	Creates "Attribute" column (month names) and "Value" column (sales)

These operations are especially powerful in monthly workflows where you need to restructure the same shape repeatedly.

Adding Custom Calculated Columns

You can create new columns using formulas. Power Query supports arithmetic, text functions, conditionals, and date operations.

Step	Action
1. Open menu	Click Add Column > Custom Column (Home tab)
2. Name column	Enter column name (e.g., "Tax-Included Price")
3. Write formula	Enter formula (e.g., `[Price] * 1.1`)
4. Create	Click OK (new column appears in data)

Power Query uses the M language, not Excel worksheet functions. Available M functions include DateTime.LocalNow(), if...then...else, Text.Length(), Text.Combine(), and dozens more. Reference existing columns with square brackets [ColumnName].

Where Power Query Hits Its Limits

Power Query is flexible, but not limitless. Recognize these ceilings so you don't waste time fighting the tool.

Limit	Issue	Solution
Multi-million-row datasets	Memory constraints and slowdown beyond ~1M rows	SQL DB, Python, Spark, or LeapRows
Complex branching & loops	Can't handle intricate if/then/else logic with recursion	Python, R, or SQL
External data export	Can't auto-export to databases, APIs, or cloud storage	Power Automate, Azure Data Factory, or custom code

Turn Monthly Busywork into a Single Refresh Click

Recapping: Power Query is an Excel feature that automates the CSV-to-report pipeline.

Key capabilities:

Load one CSV or a hundred from a folder
Transform using deletions, filters, splits, merges, pivots, and calculated fields
Refresh in one click whenever your source data updates

For any business process involving regular CSV imports—sales reports, inventory checks, financial consolidations—Power Query cuts weeks of manual work down to button presses. Begin with a single CSV import, master the transform steps, and expand from there. The pattern scales.