Loading Data from CSV and Text Files in Power Query: Options, Delimiters, and Encoding Settings

Introduction

Picture this: your operations team just exported a week's worth of sales transactions from their point-of-sale system. The file lands in your inbox as a .txt file. You open it in Notepad and see a wall of data separated by pipe characters (|). There's a header row, but some of the product names have commas in them — which explains why they didn't use a standard CSV format. Your boss needs this loaded into a report by end of day.

This is an everyday scenario for anyone who works with data, and it's exactly the kind of problem Power Query was built to solve. CSV and text files are the universal language of data exchange — practically every system on the planet can export one — but they come in dozens of variations. Delimiters differ, character encodings clash, header rows appear or disappear, and date formats shift depending on who built the export. Knowing how to handle all these variations confidently is a foundational skill you'll use constantly.

By the end of this lesson, you'll be able to connect to any CSV or delimited text file, configure every important import option correctly, diagnose encoding problems, and write clean M code that you understand rather than just accepting what the wizard generates.

What you'll learn:

• How Power Query reads CSV and text files, and what happens under the hood
• How to set delimiters, including custom and multi-character delimiters
• What character encoding means and how to pick the right setting
• How to handle header rows, data type detection, and locale-specific formatting
• How to edit the generated M code to control your connection programmatically

---

Prerequisites

This lesson assumes you have Excel (2016 or later) or Power BI Desktop installed. If you've never opened Power Query before, the article Getting Started with Power Query: Master Connect, Transform, Load for Real-World Data will get you oriented in about 20 minutes. You don't need to know any M code yet — we'll walk through it together.

---

What CSV and Text Files Actually Are

Before we click anything, it's worth understanding what we're dealing with. A CSV file (Comma-Separated Values) is a plain text file where each row represents one record and each field within that row is separated by a delimiter — traditionally a comma. A .txt file is exactly the same thing structurally; the only difference is the file extension.

Here's a simple example of what a CSV file looks like when you open it in a text editor:

OrderID,CustomerName,Product,Amount,OrderDate
1001,Acme Corp,Industrial Pump,4500.00,2024-01-15
1002,Beta Industries,Safety Valve,320.50,2024-01-16
1003,Acme Corp,"Widget, Deluxe Edition",89.99,2024-01-16

Notice row 1003: the product name contains a comma, so it's wrapped in double quotes. This is standard CSV quoting behavior — Power Query handles it automatically. Now imagine the same data with a pipe delimiter:

OrderID|CustomerName|Product|Amount|OrderDate
1001|Acme Corp|Industrial Pump|4500.00|2024-01-15
1002|Beta Industries|Safety Valve|320.50|2024-01-16
1003|Acme Corp|Widget, Deluxe Edition|89.99|2024-01-16

With a pipe delimiter, that product name doesn't need quotes because commas are no longer special. This is why pipe-delimited files are common in systems where commas appear naturally in the data.

---

Connecting to a CSV File

In Excel

Go to the Data tab on the ribbon. Click Get Data, then hover over From File, then click From Text/CSV. A file browser opens — navigate to your file and click Import.

Power Query will immediately show you a preview dialog. This is not the full editor yet — it's a quick-look screen that makes some guesses about your file's structure. We'll examine every option on this screen.

In Power BI Desktop

Go to the Home tab on the ribbon. Click Get Data, then select Text/CSV from the list (or type "text" in the search box). Browse to your file and click Open. You'll see the same preview dialog.

What the Preview Dialog Shows You

The preview dialog has several settings at the top:

• File Origin — the character encoding setting
• Delimiter — what character separates your fields
• Data Type Detection — how aggressively Power Query should guess column types

Below those settings is a preview of roughly the first 200 rows of your file. Changes to the settings above instantly refresh the preview so you can see the effect before committing.

---

Understanding and Setting Delimiters

The delimiter dropdown is the first thing to check after connecting. Power Query will make a guess, and it's often right — but not always.

The built-in delimiter options are:

• Comma (the default for .csv files)
• Tab
• Semicolon
• Space
• Colon
• Custom — this is the one you'll use most often for non-standard files

Choosing a Custom Delimiter

If your file uses a pipe |, tilde ~, or any other character as a delimiter, select Custom from the dropdown. A text box appears next to it. Type your delimiter character there.

For the pipe-delimited sales file from our opening scenario, you'd type | in that box. The preview table will immediately re-render with the data split into proper columns.

Tab-Delimited Files

Tab-delimited files deserve special mention because they're extremely common in data exports from databases, ERP systems, and scientific software. They're often saved with a .tsv extension (Tab-Separated Values) or just .txt. Power Query should detect these correctly, but if it doesn't, choose Tab from the delimiter dropdown.

Multi-Character Delimiters

Occasionally you'll encounter files from legacy systems that use multi-character separators like || (double pipe) or ~|~. The Custom option handles these — just type the full multi-character sequence into the custom delimiter box.

---

Character Encoding: The Most Misunderstood Setting

Character encoding is the system that maps numbers to characters. Your computer stores every character as a number — encoding is the rulebook that says "number 65 means the letter A." The problem is there are many different rulebooks, and when you use the wrong one to read a file, characters get scrambled.

This is called mojibake — a Japanese term for garbled text that happens when encoding mismatches occur. If you open a file and see characters like é instead of é, or “ instead of ", you have an encoding mismatch.

The Three Encodings You'll Encounter

UTF-8 is the modern standard. It can represent every character in every human language using 1–4 bytes per character. The vast majority of files exported from modern systems will be UTF-8. When in doubt, try this first.

UTF-8 with BOM (Byte Order Mark) is UTF-8 with a small signature at the beginning of the file that explicitly declares the encoding. Some Windows applications add this automatically. If your file opens and shows a strange  at the start of the first field, you have a UTF-8 with BOM file being read as plain UTF-8. Select the BOM variant from the dropdown.

Windows-1252 (also called ANSI or Latin-1) is a legacy encoding used by older Windows applications. It's common in files exported from older ERP systems, legacy databases, and government data portals. If your file contains accented characters (like those in French, Spanish, or German names) and they're showing as garbage, Windows-1252 is a strong candidate.

The File Origin dropdown in Power Query lists encodings by their IANA name or Windows code page number. Some key ones to recognize:

What you see in the dropdown	What it means
UTF-8	Modern universal encoding
65001	Also UTF-8 (the Windows code page number)
1252	Windows-1252 (Western European)
1200	UTF-16 Little Endian
ISO-8859-1	Latin-1, nearly identical to Windows-1252

---

Handling Header Rows

By default, Power Query assumes the first row of your file contains column headers and automatically promotes it. You can see this in the preview — column names appear in the header row of the preview table rather than "Column1," "Column2," etc.

If the preview shows "Column1," "Column2" as the headers with your actual headers sitting in the first data row, click the Use First Row as Headers button (it looks like a table with an up arrow, found in the Transform tab of the full editor). Alternatively, in the preview dialog, check whether the Data Type Detection setting is set to something that's interfering — but usually a simple "Use First Row as Headers" step in the editor fixes it.

Some system exports include metadata rows before the real header. You might see something like:

Report Generated: 2024-01-17
Extracted by: SalesSystem v3.2
 
OrderID,CustomerName,Product,Amount,OrderDate
1001,Acme Corp,Industrial Pump,4500.00,2024-01-15

In this case, you need to remove the top rows first. In the full Power Query editor, go to Home → Remove Rows → Remove Top Rows and enter the number of rows to skip before your real data starts. After doing that, use Use First Row as Headers.

---

Data Type Detection: A Blessing and a Trap

The Data Type Detection dropdown has three options:

• Based on first 200 rows — Power Query samples the first 200 rows and guesses types
• Based on entire dataset — Power Query scans the whole file before guessing (slower but more accurate)
• Do not detect data types — everything loads as text; you assign types manually

For small files, "based on entire dataset" is fine. For large files (hundreds of thousands of rows), scanning the entire file just to detect types slows down the import unnecessarily.

The dangerous option to watch out for is letting Power Query detect types automatically when your data is inconsistent. Consider a "PostalCode" column containing values like 02134 (a Boston ZIP code with a leading zero). If Power Query decides it's a whole number, that leading zero disappears and you get 2134 — which is now wrong. For any column where leading zeros matter or where values might be mixed (numbers and text codes together), choose Do not detect data types and manually assign the type as Text after loading.

---

Locale and Regional Settings: When Numbers and Dates Don't Parse Correctly

This is a subtler problem that catches people off guard. Power Query uses your computer's regional settings as a default when interpreting numbers and dates. If a file was exported in Europe where the decimal separator is a comma (so 4.500,00 means four thousand five hundred), and your machine is set to US regional settings, those numbers will either fail to parse or parse incorrectly.

You handle this by right-clicking the column in the Power Query editor, choosing Change Type → Using Locale, and selecting the locale that matches where the data originated.

Similarly, dates formatted as 16/01/2024 (day/month/year format used in the UK) will be misread as an error or as January 16th by a machine expecting 01/16/2024 (month/day/year). Using locale when changing types tells Power Query exactly what format to expect.

---

Reading the Generated M Code

When you click Transform Data (or Load then view the query), Power Query has already written M code for you. Click View → Advanced Editor in the Power Query editor to see it.

For a typical CSV file, the generated code looks like this:

let
    Source = Csv.Document(
        File.Contents("C:\Data\sales_export.csv"),
        [
            Delimiter = ",",
            Columns = 5,
            Encoding = 65001,
            QuoteStyle = QuoteStyle.Csv
        ]
    ),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {
            {"OrderID", Int64.Type},
            {"CustomerName", type text},
            {"Product", type text},
            {"Amount", type number},
            {"OrderDate", type date}
        }
    )
in
    #"Changed Type"

Let's read through this:

• Csv.Document() is the core function that reads the file. It takes the file contents and a record of options.
• Delimiter = "," — your chosen delimiter, as a text string
• Encoding = 65001 — the numeric code page for UTF-8
• QuoteStyle = QuoteStyle.Csv — tells the parser to handle double-quoted fields correctly
• Table.PromoteHeaders() — the step that moves row 1 into the column header position
• Table.TransformColumnTypes() — the auto-detected type assignments

For a pipe-delimited file read as Windows-1252, it would look like:

Source = Csv.Document(
    File.Contents("C:\Data\sales_export.txt"),
    [
        Delimiter = "|",
        Encoding = 1252,
        QuoteStyle = QuoteStyle.Csv
    ]
)

Understanding this code means you can modify it directly instead of repeating the wizard. This becomes important when you're building reusable queries or working with files whose paths or settings change. For a deeper dive into the M language that powers all of this, see Understanding the M Formula Language: Syntax, Data Types, and Expression Basics.

---

Working with Fixed-Width Text Files

Not all text files use delimiters. Some legacy systems produce fixed-width files, where each column occupies a specific number of characters regardless of the actual value. A customer ID might always occupy characters 1–8, the name might occupy characters 9–38, and so on.

Power Query's standard CSV connector won't parse these correctly — you'll get one giant text column. For these files, you'll want the full Power Query editor's Split Column by Position functionality. This is a more specialized topic that gets thorough treatment in the article on Splitting and Processing Fixed-Width and Delimited Text Files in Power Query: Parsing Legacy Flat File Formats.

---

Hands-On Exercise

Let's put everything together with a realistic exercise. Create a text file on your desktop called inventory_export.txt and paste in the following content exactly:

Exported from WarehouseSystem v2.1
Date: 2024-01-17

SKU|ProductName|StockQty|UnitCost|LastReceived
W-001|Steel Bolt, M6 Grade 8|4500|0,85|17/01/2024
W-002|Hex Nut M6|12000|0,12|15/01/2024
W-003|Washer 6mm|8750|0,08|17/01/2024
W-004|Anchor Bolt 10mm|320|2,45|10/01/2024

This file has three intentional challenges: metadata rows at the top, a pipe delimiter, and European number/date formatting (commas as decimal separators, day/month/year dates).

Step 1 — Connect: In Excel or Power BI, use Get Data → From File → From Text/CSV. Browse to inventory_export.txt.

Step 2 — Set the delimiter: In the preview dialog, change the delimiter dropdown to Custom and type |. You should see the data snap into five columns. Notice the first few rows still show the metadata.

Step 3 — Load to editor: Click Transform Data to open the full Power Query editor.

Step 4 — Remove metadata rows: Go to Home → Remove Rows → Remove Top Rows. Type 3 (we have two metadata lines plus one blank line before the headers). Click OK.

Step 5 — Promote headers: Go to Home → Use First Row as Headers. The column names (SKU, ProductName, etc.) should appear as headers.

Step 6 — Fix types with locale: Right-click the UnitCost column header. Choose Change Type → Using Locale. Set the data type to Decimal Number and the locale to Dutch (Netherlands) or any European locale that uses commas as decimal separators. Click OK.

Repeat for the LastReceived column: right-click → Change Type → Using Locale → Date → locale set to English (United Kingdom) (which uses day/month/year). Click OK.

Step 7 — Verify: Your table should now show five clean columns with correct types. UnitCost should show 0.85, 0.12, 0.08, and 2.45 as proper decimal numbers. LastReceived should show proper dates.

Step 8 — Close and Load: Click Home → Close & Load. Your data lands in a clean table.

---

Common Mistakes & Troubleshooting

Problem: All data appears in a single column Your delimiter is wrong. Power Query guessed comma but your file uses tabs or pipes. Open the file in a text editor, identify the separator character, and update the delimiter setting.

Problem: Garbled characters like é or “ Classic encoding mismatch. The file is probably UTF-8 but being read as Windows-1252, or vice versa. Try switching the File Origin setting between UTF-8 and 1252 until the preview looks correct.

Problem: Dates showing as errors or wrong values Your file's date format doesn't match your machine's regional settings. Right-click the date column, choose Change Type → Using Locale, and pick the locale that matches the date format in the source file.

Problem: Leading zeros disappearing from ZIP codes or ID numbers Power Query detected the column as a whole number. Change the type to Text to preserve leading zeros.

Problem: The file path is hardcoded and breaks when moved This is a maintainability issue rather than an error. Consider using a parameter for the file path so you can update it in one place. The article on Parameterized Queries and Dynamic Data Sources in Power Query covers exactly how to do this.

Problem: Quoted fields aren't being handled correctly If your CSV uses non-standard quoting (single quotes instead of double quotes, or no quoting at all), the default QuoteStyle.Csv won't work right. In the Advanced Editor, change QuoteStyle = QuoteStyle.Csv to QuoteStyle = QuoteStyle.None and handle the cleanup manually.

Problem: Refreshing the query fails with a permissions or path error The file either moved or Power Query lost its credential for the file location. In Excel, go to Data → Queries & Connections, right-click the query, and choose Edit. Update the file path in the M code. In Power BI, check the file path in the data source settings under File → Options → Data Source Settings.

---

When You're Loading Many Files at Once

If you need to load dozens of CSV files from the same folder — say, one monthly export per file — the approach changes. Instead of connecting to files one by one, you connect to the folder and let Power Query iterate over all the files, combining them into one table automatically. This technique is covered in depth in Combining Multiple Files from a Folder. The settings covered in this lesson (delimiter, encoding, header handling) still apply — they're configured inside the helper query that processes each individual file.

After loading and combining your data, you'll often need to do further cleaning — removing duplicates, handling blank rows, or resolving type conflicts between files. The article on Cleaning Messy Data with Power Query: Nulls, Errors, and Type Conversions is the natural next stop for that work.

---

Summary & Next Steps

Loading a CSV or text file sounds simple, but the details matter enormously. Here's what you're now equipped to handle:

• Delimiters: Standard and custom, including multi-character separators. You know how to spot the wrong delimiter in the preview and fix it.
• Encoding: You understand what UTF-8, Windows-1252, and other encodings mean, why mismatches cause garbled text, and how to fix them using the File Origin setting.
• Headers and metadata: You can skip leading metadata rows, promote the right row as headers, and work with files that don't have headers at all.
• Data types and locale: You know the risk of automatic type detection, when to use locale-aware type changes, and how to protect columns like ZIP codes that look like numbers but shouldn't be treated as such.
• M code: You can read the generated code and understand what each option does, which means you can modify it confidently rather than re-running the wizard every time something changes.

For your next step, try combining multiple CSV files from a folder using the technique in Combining Multiple Files from a Folder — it builds directly on what you learned here. If you're working with more complex source data like Excel workbooks with named ranges, Connecting to Excel Workbooks and Named Ranges in Power Query: A Practical Foundation Guide covers the equivalent settings for that connector.

The more varied source files you work with, the more intuitive these settings become. After a few real projects, you'll be opening the Advanced Editor and tweaking the M directly rather than clicking through the wizard — and that's when you'll really start to feel the power of the tool.