You've just been handed a file. It came from the mainframe, or the ERP system, or the ancient AS/400 that nobody wants to talk about. It's a .txt file, and when you open it in Notepad, it looks like someone typed a novel with no spaces between words — or alternatively, like someone was deeply committed to the pipe character as an artistic medium. This is the reality of legacy flat file formats, and if you work with any system that predates the modern data stack, you will encounter them constantly.
Power Query handles these situations elegantly once you understand the underlying mechanics — but the default "just import it" wizard will fail you in creative ways when your file doesn't conform to simple CSV conventions. Fixed-width files with no delimiters, multi-character delimiters, files with headers buried on row three, files where some fields are quoted and some aren't — these all require deliberate parsing strategies rather than point-and-click hope.
By the end of this lesson, you'll be able to confidently import, parse, and reshape both fixed-width and delimited flat files in Power Query, including the ones that behave badly. You'll understand why Power Query's parsing functions work the way they do, which means you'll be able to adapt them to files you've never seen before.
What you'll learn:
You should already be comfortable with the Power Query interface — loading data, applying basic transformations, and understanding that the M formula language is behind every step. You don't need to be an M expert, but you should know how to open the Advanced Editor and not panic when you see a let...in block. Familiarity with Table.TransformColumns, Text.Split, and basic list operations will help.
Before writing a single formula, you need to correctly diagnose your file. This sounds obvious, but it's the step most people skip — and then they spend an hour debugging a problem that would have been obvious in thirty seconds.
Open the file in a plain text editor (Notepad, Notepad++, VS Code) before loading it into Power Query. Look for:
LF (\n); Windows files use CRLF (\r\n); old mainframe exports sometimes use just CR (\r). Power Query usually handles this, but sometimes it doesn't.Once you know what you're dealing with, you can pick the right strategy.
Power Query's default text file import is designed for well-behaved CSVs. For anything else, you want to load the file as raw binary and parse it yourself. This gives you full control.
In Power Query, go to Get Data > From File > From Text/CSV, select your file, and then — before clicking Load or Transform — look at the preview. If Power Query has split your fixed-width file into one column (good, that's actually what you want at this stage), or has done something clearly wrong with a delimited file, proceed into the editor and then immediately open the Advanced Editor.
For a truly raw approach, use File.Contents combined with Lines.FromBinary:
let
RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
AsText = Lines.FromBinary(
RawBinary,
QuoteStyle.None,
false,
1252 // Windows-1252 encoding for legacy mainframe files
)
in
AsText
Lines.FromBinary returns a list where each element is one line of the file as a text string. This is your starting point for manual parsing. The parameters are:
QuoteStyle.None — don't try to interpret quote charactersfalse — don't treat the first row as a header1252 — the code page for Windows-1252 encoding (use 65001 for UTF-8, 20127 for ASCII)To turn this list into a table with one row per line:
let
RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
AsTable = Table.FromList(LineList, Splitter.SplitByNothing(), {"RawLine"}),
// Remove header rows and trailer records
DataOnly = Table.Skip(AsTable, 2), // skip the first 2 rows (metadata/header)
RemoveTrailer = Table.RemoveLastN(DataOnly, 1) // remove the final summary record
in
RemoveTrailer
You now have a single-column table called RawLine where each row contains one complete record as a text string. This is your foundation for everything that follows.
Tip:
Table.RemoveLastNis invaluable for files with trailer records. Many mainframe exports end with a totals row or a record count line that would corrupt your analysis if you included it. Always check the last few rows of your file before building the rest of the query.
Fixed-width files are the format that most intimidates Power Query beginners, because there's no obvious separator. The trick is that you always have a specification — either in documentation, in the file header, or obtained by measuring the fields manually.
Here's a realistic example. You've received a payroll flat file from an HR system. The layout specification says:
| Field | Start Position | Length |
|---|---|---|
| Employee ID | 1 | 8 |
| Last Name | 9 | 20 |
| First Name | 29 | 15 |
| Department Code | 44 | 4 |
| Pay Rate | 48 | 9 |
| Pay Type | 57 | 1 |
| Hours Worked | 58 | 6 |
A raw line looks like this (with the understanding that spaces are meaningful padding):
10042891HENDERSON MARCUS FIN 002500.00S040.00
Power Query's Text.Middle function is your primary tool here. It takes a text string, a starting position (zero-indexed, so subtract 1 from the spec), and a length:
Text.Middle("10042891HENDERSON MARCUS FIN 002500.00S040.00", 0, 8)
// Returns: "10042891"
Text.Middle("10042891HENDERSON MARCUS FIN 002500.00S040.00", 8, 20)
// Returns: "HENDERSON "
To apply this across your entire table, use Table.AddColumn for each field:
let
RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
AsTable = Table.FromList(LineList, Splitter.SplitByNothing(), {"RawLine"}),
DataOnly = Table.Skip(AsTable, 2),
RemoveTrailer = Table.RemoveLastN(DataOnly, 1),
// Parse each field by position
AddEmployeeID = Table.AddColumn(RemoveTrailer, "EmployeeID",
each Text.Trim(Text.Middle([RawLine], 0, 8)), type text),
AddLastName = Table.AddColumn(AddEmployeeID, "LastName",
each Text.Trim(Text.Middle([RawLine], 8, 20)), type text),
AddFirstName = Table.AddColumn(AddLastName, "FirstName",
each Text.Trim(Text.Middle([RawLine], 28, 15)), type text),
AddDeptCode = Table.AddColumn(AddFirstName, "DepartmentCode",
each Text.Trim(Text.Middle([RawLine], 43, 4)), type text),
AddPayRate = Table.AddColumn(AddDeptCode, "PayRate",
each Number.From(Text.Trim(Text.Middle([RawLine], 47, 9))), type number),
AddPayType = Table.AddColumn(AddPayRate, "PayType",
each Text.Trim(Text.Middle([RawLine], 56, 1)), type text),
AddHours = Table.AddColumn(AddPayType, "HoursWorked",
each Number.From(Text.Trim(Text.Middle([RawLine], 57, 6))), type number),
// Drop the raw line column now that we've extracted everything
CleanTable = Table.RemoveColumns(AddHours, {"RawLine"})
in
CleanTable
Warning: Positions in the spec are usually 1-indexed (field starts at character 1), but
Text.Middleis 0-indexed (first character is position 0). Always subtract 1 from the spec's start position. This is the single most common mistake when parsing fixed-width files.
Notice that Text.Trim wraps every extraction. Fixed-width fields are padded with spaces to fill their full width. Without trimming, "HENDERSON " stays as-is and will cause problems in any lookup or comparison later.
Some mainframe systems store numbers in packed formats. An "implied decimal" is a common one: the number 002500.00 might actually be stored as 000250000 with the last two digits being the decimal places. The file spec will tell you — look for notes like "9(7)V99" (COBOL notation meaning 7 whole digits, implied decimal point, 2 decimal places).
// If PayRate is stored as "000250000" meaning $2500.00
AddPayRate = Table.AddColumn(AddDeptCode, "PayRate",
each Number.From(Text.Trim(Text.Middle([RawLine], 47, 9))) / 100,
type number)
For signed packed decimal fields (where the last character encodes the sign), you'll need a more sophisticated approach:
// Some systems use "{" to mean positive zero, "}" to mean negative zero,
// letters A-I for positive 1-9, letters J-R for negative 1-9
// This is rare but real — handle it with a lookup if you encounter it
Now let's tackle the other category: delimited files that aren't simple CSVs.
Pipe (|) delimiters are extremely common in enterprise data exports. Power Query's Csv.Document function accepts a custom delimiter:
let
RawBinary = File.Contents("C:\Data\customer_extract_2024.txt"),
ParsedTable = Csv.Document(
RawBinary,
[
Delimiter = "|",
Columns = 12,
Encoding = 1252,
QuoteStyle = QuoteStyle.None
]
),
PromoteHeaders = Table.PromoteHeaders(ParsedTable, [PromoteAllScalars = true])
in
PromoteHeaders
The Columns parameter tells Power Query how many columns to expect. If you omit it, Power Query will infer it from the first row — which works if your file is consistent, but can fail if some rows have extra pipe characters in data fields.
Tip: If your data fields can contain the delimiter character, you need quoting. The
QuoteStyle = QuoteStyle.Csvoption tells Power Query to respect double-quote wrapping.QuoteStyle.Noneignores all quoting, which is what you want for files that genuinely contain no quotes.
Some legacy systems delimit fields with multiple characters — ||, ~|, or even something like <|>. Power Query's Csv.Document only handles single-character delimiters. For multi-character delimiters, you need to split manually.
Here's the approach using Text.Split on each row:
let
RawBinary = File.Contents("C:\Data\inventory_feed.txt"),
LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
AsTable = Table.FromList(LineList, Splitter.SplitByNothing(), {"RawLine"}),
SkipHeader = Table.Skip(AsTable, 1),
// Split each line on the multi-character delimiter "~|"
SplitLines = Table.TransformColumns(
SkipHeader,
{"RawLine", each Text.Split(_, "~|"), type list}
),
// Expand the list into columns
ExpandColumns = Table.ExpandListColumn(SplitLines, "RawLine"),
// At this point each row has repeated—we need to pivot this differently
// Better approach: use List.Zip to build a structured table
// Alternative: build table directly from lists
ParsedRows = List.Transform(
List.Skip(LineList, 1),
each Text.Split(_, "~|")
),
ColumnNames = Text.Split(List.First(LineList), "~|"),
FinalTable = Table.FromRows(ParsedRows, ColumnNames)
in
FinalTable
Let me show that pattern more cleanly, because List.Transform with Table.FromRows is the right approach for multi-character delimiters:
let
RawBinary = File.Contents("C:\Data\inventory_feed.txt"),
LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
// First line is the header
HeaderLine = List.First(LineList),
ColumnNames = Text.Split(HeaderLine, "~|"),
// All remaining lines are data
DataLines = List.Skip(LineList, 1),
// Split each data line into a list of field values
ParsedRows = List.Transform(DataLines, each Text.Split(_, "~|")),
// Build the table
ResultTable = Table.FromRows(ParsedRows, ColumnNames)
in
ResultTable
This pattern — split each line into a list, then use Table.FromRows with a list of lists — is extremely versatile. It works for any delimiter, any number of characters.
Real-world legacy files sometimes have rows with missing trailing fields — the ERP system just didn't write the delimiter if there was no value. This causes Table.FromRows to fail because the lists have different lengths.
Fix it by padding each row to the expected length:
let
RawBinary = File.Contents("C:\Data\inventory_feed.txt"),
LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
HeaderLine = List.First(LineList),
ColumnNames = Text.Split(HeaderLine, "~|"),
ExpectedColumns = List.Count(ColumnNames),
DataLines = List.Skip(LineList, 1),
// Split and pad each row to exactly ExpectedColumns elements
ParsedRows = List.Transform(
DataLines,
each
let
Fields = Text.Split(_, "~|"),
CurrentCount = List.Count(Fields),
PaddedFields = if CurrentCount < ExpectedColumns
then Fields & List.Repeat({""}, ExpectedColumns - CurrentCount)
else List.FirstN(Fields, ExpectedColumns)
in
PaddedFields
),
ResultTable = Table.FromRows(ParsedRows, ColumnNames)
in
ResultTable
The padding logic adds empty strings to short rows and truncates rows that are unexpectedly long. This makes your query resilient to the minor inconsistencies that are so common in legacy exports.
You now have two main strategies. Here's how to choose:
Use Text.Middle (position-based splitting) when:
Use Text.Split or Csv.Document (delimiter-based splitting) when:
Sometimes you have a hybrid: a file where each line is a fixed-width record, but within that record, one field contains a delimited sub-string. In that case, extract the fixed-width fields first, then apply Text.Split to the specific column that contains the delimited value.
After you've split your fields out, everything is text. Now you need to make it useful.
let
// ... previous steps ...
TypedTable = Table.TransformColumnTypes(CleanTable, {
{"EmployeeID", type text},
{"LastName", type text},
{"FirstName", type text},
{"DepartmentCode", type text},
{"PayRate", type number},
{"PayType", type text},
{"HoursWorked", type number}
})
in
TypedTable
Warning: Don't use the automatic type detection step that Power Query offers to insert. It samples rows and guesses — it will guess wrong on numeric codes that look like numbers (employee IDs, zip codes, department codes) and will make them integers, silently dropping leading zeros. Always type columns manually in flat file imports.
Leading zeros on ID fields: Fixed-width ID fields like 00042891 should stay as text. Make sure you type them as type text before any other operation touches them.
Date formats: Mainframe dates are often in YYYYMMDD or MMDDYYYY format with no separator. Parse them with Date.FromText using the format parameter:
// Convert "20241115" to a proper date
Table.AddColumn(TypedTable, "PayDate",
each Date.FromText(Text.Trim([RawDate]), [Format = "yyyyMMdd"]),
type date)
Negative numbers: Some legacy systems represent negative numbers by putting the minus sign at the right end (12345-) or by using a special character for the last digit (an old EBCDIC convention). Handle it:
// Handle trailing-sign negative numbers like "012345-"
Table.AddColumn(TypedTable, "Amount",
each
let
Raw = Text.Trim([RawAmount]),
HasTrailingMinus = Text.End(Raw, 1) = "-",
NumericString = if HasTrailingMinus
then "-" & Text.Start(Raw, Text.Length(Raw) - 1)
else Raw
in
Number.From(NumericString),
type number)
Empty strings vs. null: After trimming, fields with only spaces become empty strings "". Decide whether you want them as null or "" — for most purposes, null is more useful:
Table.ReplaceValue(TypedTable, "", null, Replacer.ReplaceValue, {"LastName", "FirstName"})
If you process the same file format regularly, you don't want to hard-code every Text.Middle call. Build a function that accepts a field specification as a table and applies it dynamically.
// Create a query called "ParseFixedWidth" as a function
(RawLines as list, FieldSpec as table) as table =>
let
// FieldSpec should have columns: FieldName, StartPos (1-indexed), Length
// Convert FieldSpec to a list of records for easier iteration
SpecRecords = Table.ToRecords(FieldSpec),
// Build a table from the raw lines
RawTable = Table.FromList(RawLines, Splitter.SplitByNothing(), {"RawLine"}),
// Dynamically add a column for each field in the spec
ParsedTable = List.Accumulate(
SpecRecords,
RawTable,
(CurrentTable, SpecRow) =>
Table.AddColumn(
CurrentTable,
SpecRow[FieldName],
each Text.Trim(
Text.Middle([RawLine], SpecRow[StartPos] - 1, SpecRow[Length])
),
type text
)
),
// Remove the raw line column
Result = Table.RemoveColumns(ParsedTable, {"RawLine"})
in
Result
Now create a second query that defines your field specification as a table:
// Query: PayrollFieldSpec
let
SpecTable = Table.FromRows(
{
{"EmployeeID", 1, 8},
{"LastName", 9, 20},
{"FirstName", 29, 15},
{"DepartmentCode",44, 4},
{"PayRate", 48, 9},
{"PayType", 57, 1},
{"HoursWorked", 58, 6}
},
{"FieldName", "StartPos", "Length"}
)
in
SpecTable
And call it from your main query:
// Query: PayrollData
let
RawBinary = File.Contents("C:\Data\payroll_export_2024.txt"),
LineList = Lines.FromBinary(RawBinary, QuoteStyle.None, false, 1252),
DataLines = List.Skip(List.RemoveLastN(LineList, 1), 2),
Result = ParseFixedWidth(DataLines, PayrollFieldSpec)
in
Result
This architecture means that when the field spec changes — and it will change — you update one table, not a dozen Table.AddColumn calls.
Here's a real scenario to work through. You'll parse a fixed-width bank transaction export.
Setup: Create a text file called bank_transactions.txt with the following content (use a fixed-width font to verify alignment):
BANK EXPORT FILE - FIRST NATIONAL BANK - ACCOUNT 789456123
GENERATED 20241115
ACCTNUM TRANDATE TRANTYPE AMT DESCRIPTION STATUS
10045678 20241101 CR 000125050DIRECT DEPOSIT PAYROLL C
10045678 20241103 DB 000045000AMAZON.COM C
10045678 20241105 DB 000008750NETFLIX C
10045678 20241108 CR 000010000TRANSFER FROM SAVINGS C
10045678 20241110 DB 000156789RENT PAYMENT C
99 RECORDS IN EXPORT 99 TOTAL RECORDS
The field specification:
| Field | Start | Length | Notes |
|---|---|---|---|
| AccountNumber | 1 | 8 | |
| TransactionDate | 10 | 8 | YYYYMMDD |
| TransactionType | 19 | 2 | CR or DB |
| Amount | 22 | 9 | Implied 2 decimal places |
| Description | 31 | 29 | |
| Status | 61 | 1 | C=Cleared, P=Pending |
Your tasks:
Lines.FromBinary with Windows-1252 encoding.Text.Middle with the correct zero-indexed positions.TransactionDate from YYYYMMDD text to a proper date type using Date.FromText.Amount from the 9-character packed string to a proper number by dividing by 100.SignedAmount that makes CR amounts positive and DB amounts negative.AccountNumber as text (to preserve leading zeros), everything else as appropriate.Expected result for the first data row:
Bonus challenge: Refactor your solution to use the List.Accumulate pattern shown in the reusable parser section above.
The single most common error. The spec says a field starts at position 9 — you put 9 in Text.Middle. But Text.Middle is zero-indexed, so position 9 means the 10th character. Subtract 1 from every spec position.
How to debug: Add a step that just returns Text.Middle([RawLine], 0, 50) on the first row and count characters manually. Use a monospace font in your text editor.
Power Query's auto-type detection will silently convert "00042891" to the number 42891 and you'll never notice until someone complains that employee IDs are wrong. Always manually specify types for every column, immediately.
If your file contains any non-ASCII characters (accented letters, currency symbols, special characters) and you use the wrong encoding, those characters will be garbled. The symptoms: fields look right except for one or two characters that appear as ? or random symbols.
Fix: Use code page 1252 for Western European Windows files, 65001 for UTF-8, 850 for DOS code page, 37 for EBCDIC (rare but exists). Notepad++ shows the encoding in the status bar.
Lines.FromBinary often returns a final empty string if the file ends with a newline character. This causes Text.Middle to return empty strings for all fields, which then fail type conversion. Always check for and remove empty rows:
RemoveEmpty = Table.SelectRows(RawTable, each [RawLine] <> "" and [RawLine] <> null)
A file that looks pipe-delimited might actually use \t (tab) — and tabs look like spaces in many text editors. In Notepad++, turn on View > Show Symbol > Show All Characters to see tabs, spaces, and line endings explicitly. In VS Code, the status bar shows the file encoding and you can toggle whitespace display.
If a description field says "Red|Blue Widgets" in a pipe-delimited file (and the file doesn't quote it), Text.Split will give you an extra column. The right fix is to get proper quoting from whoever generates the file. The workaround is to use Csv.Document with QuoteStyle.Csv if the file uses quoting, or to limit the split count using Splitter.SplitTextByDelimiter with a max column count.
If you skip headers and trailers but get more or fewer rows than expected, check whether the file has Windows line endings (\r\n) and whether Lines.FromBinary is handling them correctly. On some systems, you might see \r appearing as the last character in each line. Check with:
Text.End(List.First(DataLines), 1) = Character.FromNumber(13) // returns true if CR present
Strip it with Text.TrimEnd([RawLine], Character.FromNumber(13)) in a Table.TransformColumns step.
For files under 50,000 rows, performance is rarely a concern with the approaches shown here. For larger files, keep these in mind:
List.Accumulate for dynamic column generation is elegant but can be slower than explicit Table.AddColumn chains for very wide files with many columns. If you have 50+ columns and millions of rows, benchmark both approaches.
Lines.FromBinary vs. Csv.Document — Csv.Document is internally optimized and will outperform the manual Lines.FromBinary + Text.Middle approach for delimited files. Use Csv.Document whenever the file format allows it.
Avoid row-by-row operations in M — Table.AddColumn is vectorized and efficient. Functions that iterate row by row in M (like custom functions called per row) are slower. Keep your column expressions simple.
Load to data model, not to Excel table — for flat files you process regularly, loading to the Power Pivot data model rather than to a worksheet table removes the 1M row Excel limit and generally improves refresh performance.
You now have a complete toolkit for handling the flat files that modern import wizards can't touch. To summarize what you've built:
Lines.FromBinary gives you raw control over how a text file is read, including encoding and line handling.Text.Middle is the core tool for fixed-width parsing — zero-indexed, always trim the result.Table.FromRows with List.Transform handles multi-character and non-standard delimiters elegantly.List.Accumulate lets you build dynamic, spec-driven parsers that adapt to layout changes without rewriting formulas.What to tackle next:
Folder.Contents combined with the parsers you've built here to process all files in a folder automatically.try...otherwise patterns to make your parsing resilient to individual bad records without failing the entire query.Table.NestedJoin.The files you've been handed are problems someone else created — but with these tools, they don't have to stay problems for long.
Learning Path: Power Query Essentials