How to Import CSV into Power Query
Importing a CSV through Power Query — rather than a plain File → Open — gives you a refreshable, type-safe connection. This is the right way to bring CSV data into Excel for any serious analysis.
Why import via Power Query?
Opening a CSV directly (File → Open) makes it a one-time paste. There's no connection back to the source file. When the source changes, you re-open, re-format, and re-clean.
Power Query treats the CSV as a live source. Refresh the query, and the latest file contents flow through your saved transformation steps. Type conversions, filters, and column renames all persist.
Step 1: Open the Get Data menu
Step 2: Select the CSV
Browse to the file and click Import. Power Query analyzes the first few thousand rows and shows a preview.
Step 3: Review the preview dialog
The preview shows three important guesses Power Query has made:
- File Origin (encoding) — usually UTF-8 or Windows-1252. Wrong choice shows garbled characters.
- Delimiter — comma, semicolon, tab, or custom.
- Data Type Detection — how many rows to scan for type inference. Default is 200, but you can pick "Based on entire dataset" for reliability.
If names show "Ren" instead of "René", the encoding is wrong. Change File Origin to a different encoding (UTF-8 → Windows-1252 or vice versa) until characters look correct.
Step 4: Load or Transform
Two buttons at the bottom:
- Load — imports directly to a new table with Power Query's guesses.
- Transform Data — opens the Power Query Editor for cleaning first.
Almost always click Transform Data. Even for clean CSVs, explicit type-setting prevents surprises.
Step 5: Set data types explicitly
In the editor, look at each column's type icon (left of the column name). The most common issues:
- Dates guessed as text — click the icon → Date
- Numbers guessed as text — click the icon → Whole Number or Decimal Number
- ID columns (like "00042") guessed as numbers — click the icon → Text (to preserve leading zeros)
Step 6: Handle common CSV quirks
Junk header rows
If the CSV starts with title/metadata rows:
Then Home → Use First Row as Headers.
Trailing blank rows
Home → Remove Rows → Remove Blank Rows.
Currency and thousand separators
"$1,234.56" imported as text needs cleaning. Right-click column → Replace Values → find "$", replace with nothing. Then find ",", replace with nothing. Set the type to Decimal Number.
European decimals ("1.234,56")
Home → Data Type → Using Locale → Decimal Number → select German (or appropriate locale). Power Query interprets the decimals correctly.
Step 7: Close and Load
The CSV becomes a table in a new worksheet, with a query connection visible in the Queries & Connections pane.
Load options
Close & Load To… opens a dialog with alternatives:
- Table (default)
- PivotTable (skip the table, go straight to a PivotTable)
- PivotChart
- Only Create Connection — no visible output, but the query is available for use by other queries or added to the Data Model
Refreshing when the CSV changes
Save an updated CSV to the same path with the same name. Then:
Power Query re-reads the file, applies every step you saved, and updates the table. Rows added, changed, or removed all flow through.
Common pitfalls
The absolute path (C:\Users\...) is baked into the query. If you email the workbook or move it, the CSV path breaks. Either use relative paths (via a workbook cell + parameter) or place the CSV in a synced folder (OneDrive/SharePoint) that has stable paths.
If row 500 has "N/A" in a numeric column, Power Query fails at refresh because the type doesn't match. Set the type-detection option to "Based on entire dataset", or explicitly set types and add error-handling steps.
For portable projects, store the CSV in a subfolder next to the .xlsx. Use a query parameter for the folder path so paths adjust when the project moves.
CSV imports without the type gotchas
Encoding, delimiter, type detection, junk rows — CSV imports have a lot of ways to go wrong. Excel Wizard imports the CSV, guesses the right settings, and cleans common issues (junk headers, mixed types) before you even open the query editor.
Install Excel Wizard →