Power QueryExercises

Import CSV files: delimiters, encodings and headers

Turn a delimited text file into a typed table you can trust.

~10 min read · beginner · Module 2: Importing and inspecting data

By Tahir Nas · Examples run in our simulator · Published · Updated

No prerequisites: you can start here.

A CSV file is just text. Power Query has to be told how to cut it into columns, and almost every CSV import follows the same three steps: read the text into columns, promote the header row, and set types.

Input file StockFile

SKU,Description,OnHand,UnitCost
H-01,"Hinge, brass",40,1.25
H-02,Bolt,250,0.10
let
    Source = Csv.Document(StockFile, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(Promoted, {{"OnHand", Int64.Type}, {"UnitCost", type number}}, "en-US")
in
    Typed

Result

SKUDescriptionOnHandUnitCost
H-01Hinge, brass401.25
H-02Bolt2500.1

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The quoted field "Hinge, brass" stays in one column because QuoteStyle.Csv respects quotes.

Step 1: Csv.Document options

  • Delimiter separates fields. Many European systems use ; because the comma is their decimal separator. A tab is written "#(tab)".
  • Encoding 65001 is UTF-8. A wrong encoding shows accented letters as garbage such as é.
  • QuoteStyle.Csv lets quoted fields contain the delimiter or line breaks. QuoteStyle.None treats quotes as ordinary characters.

If everything lands in a single column called Column1, the delimiter is wrong. That's what happens when a semicolon file is read with the default comma:

Input file EuStock

SKU;OnHand
H-01;40
H-02;250
Csv.Document(EuStock)

Result

Column1
SKU;OnHand
H-01;40
H-02;250

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Fix the Source step (Delimiter = ";") rather than splitting Column1 afterwards. A later split can't tell a delimiter inside quotes from a real one.

Step 2: promote headers

Table.PromoteHeaders turns the first row into column names. Before that, every value is text in columns named Column1, Column2 and so on. If the file starts with a title or a run date, remove those rows first (next lesson).

Step 3: set types straight away

Everything read from a CSV is text, even 40. Set types right after promoting headers, with a culture when numbers or dates depend on it. Later steps then compare and calculate with real numbers and dates instead of text that happens to contain digits.

Where to find this in Excel

Excel for Windows
Data > Get Data > Text/CSV
Excel for Mac
Data > Get Data (Power Query) > Text/CSV
What you see
A preview lets you set File Origin, Delimiter and Data Type Detection before you choose Load or Transform Data. Excel for Windows also offers Text/CSV directly on the Data tab.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Where to get data (Microsoft Learn); Text/CSV connector (Microsoft Learn); Import and shape data in Excel for Mac (Power Query) (Microsoft Support).

Your first Power Query in Excel, from table to refresh

Common mistakes

  • Splitting Column1 by a delimiter instead of fixing the delimiter in the Source step.
  • Leaving numbers as text, so "100" < "20" sorts and compares as text.
  • Accepting an automatic Changed Type step that turned an ID column into a number and dropped its leading zeros.

Excel, Power BI and the simulator

  • Excel: Data → From Text/CSV. Power BI Desktop: Get data → Text/CSV. Both show a preview dialog where you pick the delimiter and file origin (encoding) before editing.
  • The dialog's Data Type Detection option decides whether an automatic Changed Type step is added; you can turn it off and type columns yourself.
  • The simulator reads CSV fixtures with Csv.Document and supports the common encodings (UTF-8, Windows-1252, ASCII, UTF-16, Latin-1); other encodings raise UnsupportedFeature.
Functions in this lesson:Csv.DocumentTable.PromoteHeadersTable.TransformColumnTypes

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.

Practise: Import a semicolon-separated stock file (hardware)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.